sql: add support for GROUPING SETS, CUBE, and ROLLUP

[yuzefovich]

According to Postgres docs:

More complex grouping operations are possible using the concept of *grouping
sets*. The data selected by the `FROM` and `WHERE` clauses is grouped separately
by each specified grouping set, aggregates computed for each group just as for
simple `GROUP BY` clauses, and then the results returned.

We (maybe) should add support for GROUPING SETS, CUBE, and ROLLUP.

I have no idea how hard that would be, but 7 out 99 queries of TPC-DS benchmark use rollup.

Jira issue: CRDB-5096

[yuzefovich]

cc @awoods187

[batara666]

we need this

[lacasaprivata2]

is there a timeline on this feature? if not, i'm going to install spark and/or pandas for in-memory post-processing....

[yuzefovich]

cc @vy-ton

[vy-ton]

@vitruvvius We don't have a timeline for this feature yet.

If you could share a sample query/use case that would help future prioritization?

[batara666]

@vy-ton i could use CUBE for saving []float64, usually i use CUBE for saving my machine learning encoding data

[lacasaprivata2]

rollups are very common when computing analytics for customers - ie, pivot tables

[rytaft]

All of these seem like they can be implemented with transformations in the optimizer. Check out these pages: SQL GROUPING SETS - Generate Multiple Grouping Sets, SQL ROLLUP, SQL CUBE

The example in the GROUPING SETS page shows that this query:

SELECT
    warehouse,
    product, 
    SUM (quantity) qty
FROM
    inventory
GROUP BY
    GROUPING SETS(
        (warehouse,product),
        (warehouse),
        (product),
        ()
    );

can be rewritten as:

SELECT
    warehouse,
    product, 
    SUM (quantity) qty
FROM
    inventory
GROUP BY
    warehouse,
    product
UNION ALL
SELECT
    warehouse, 
    null,
    SUM (quantity) qty
FROM
    inventory
GROUP BY
    warehouse
UNION ALL
SELECT
    null,
    product, 
    SUM (quantity) qty
FROM
    inventory
GROUP BY
    product
UNION ALL
SELECT
    null,
    null,
    SUM(quantity) qty
FROM
    inventory;

I think we can perform similar transformations for the others as well.

[kenliu-crl]

manually reviewed and brought up to date

[github-actions]

We have marked this issue as stale because it has been inactive for
18 months. If this issue is still relevant, removing the stale label
or adding a comment will keep it active. Otherwise, we'll close it in
10 days to keep the issue queue tidy. Thank you for your contribution
to CockroachDB!