### Description

Only "search hits" based queries (no aggregations) provide a consistent view on the data when paginating through the result set. With point-in-time, it is possible to support transactional semantics for aggregation queries as well.

This issue is somewhat blocked by https://github.com/elastic/elasticsearch/issues/75528 because a PIT can not immediately be closed if the aggregation result set contains less than `fetch_size` records. This could cause some increased resource utilization due to open PITs. Especially if the client does not close the SQL cursor.
x-pack/plugin/sql/src/main/java/org/elasticsearch/xpack/sql/execution/search/Querier.java
final QueryContainer query;
        final SearchRequest request;
        final BitSet mask;
        BaseAggActionListener(
            ActionListener<Page> listener,
            Client client,
            SqlConfiguration cfg,
            List<Attribute> output,
            QueryContainer query,
            SearchRequest request
        ) {
            super(listener, client, cfg, output);
            this.query = query;
            this.request = request;
            this.mask = query.columnMask(output);
        }
        protected List<BucketExtractor> initBucketExtractors(SearchResponse response) {
            
            List<QueryContainer.FieldInfo> refs = query.fields();
            List<BucketExtractor> exts = new ArrayList<>(refs.size());
            ConstantExtractor totalCount = new ConstantExtractor(response.getHits().getTotalHits().value);
            for (QueryContainer.FieldInfo ref : refs) {
                exts.add(createExtractor(ref.extraction(), totalCount));
            }
            return exts;
        }
        private BucketExtractor createExtractor(FieldExtraction ref, BucketExtractor totalCount) {
            if (ref instanceof GroupByRef r) {