### 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
Bucket implicitGroup = buckets.get(0);
                List<BucketExtractor> extractors = initBucketExtractors(response);
                Object[] values = new Object[mask.cardinality()];
                int index = 0;
                for (int i = mask.nextSetBit(0); i >= 0; i = mask.nextSetBit(i + 1)) {
                    values[index++] = extractors.get(i).extract(implicitGroup);
                }
                delegate.onResponse(Page.last(Rows.singleton(schema, values)));
            } else if (buckets.isEmpty()) {
                delegate.onResponse(Page.last(Rows.empty(schema)));
            } else {
                throw new SqlIllegalArgumentException(
                    "Too many groups returned by the implicit group; expected 1, received {}",
                    buckets.size()
                );
            }
        }
    }
    /**
     * Dedicated listener for composite aggs/group-by results.
     */
    static class CompositeActionListener extends BaseAggActionListener {
        private final boolean isPivot;
        CompositeActionListener(
            ActionListener<Page> listener,
            Client client,
            SqlConfiguration cfg,
            List<Attribute> output,
            QueryContainer query,
            SearchRequest request