[Feature Request]: GROUP BY WITH ROLLUP

[dengn]

Is there an existing issue for the same feature request?

• [X] I have checked the existing issues.

Is your feature request related to a problem?

No response

Describe the feature you'd like

The GROUP BY clause permits a WITH ROLLUP modifier that causes summary output to include extra rows that represent higher-level (that is, super-aggregate) summary operations. ROLLUP thus enables you to answer questions at multiple levels of analysis with a single query. For example, ROLLUP can be used to provide support for OLAP (Online Analytical Processing) operations.

CREATE TABLE sales ( year INT, country VARCHAR(20), product VARCHAR(32), profit INT );

mysql> SELECT year, SUM(profit) AS profit FROM sales GROUP BY year; +------+--------+ | year | profit | +------+--------+ | 2000 | 4525 | | 2001 | 3010 | +------+--------+

The output shows the total (aggregate) profit for each year. To also determine the total profit summed over all years, you must add up the individual values yourself or run an additional query. Or you can use ROLLUP, which provides both levels of analysis with a single query. Adding a WITH ROLLUP modifier to the GROUP BY clause causes the query to produce another (super-aggregate) row that shows the grand total over all year values:

mysql> SELECT year, SUM(profit) AS profit FROM sales GROUP BY year WITH ROLLUP; +------+--------+ | year | profit | +------+--------+ | 2000 | 4525 | | 2001 | 3010 | | NULL | 7535 | +------+--------+

Describe implementation you've considered

Follow MySQL behavior: https://dev.mysql.com/doc/refman/8.0/en/group-by-modifiers.html

Documentation, Adoption, Use Case, Migration Strategy

No response

Additional information

No response

[Cyberleu]

I have read the mysql 8.0 document, can I have a try? @dengn

[Cyberleu]

@YANGGMM Sorry, I didn't noticed that you have unassigned me. I have finished this issue, and now i'm merging conflicts.

[YANGGMM]

@YANGGMM Sorry, I didn't noticed that you have unassigned me. I have finished this issue, and now i'm merging conflicts.

ok, I have assigned it to you, thanks for your contribution!

[YANGGMM]

@Cyberleu do you have any interest in supporting GROUP BY WITH CUBE and GROUP BY WITH sets ?

[Cyberleu]

@Cyberleu do you have any interest in supporting GROUP BY WITH CUBE and GROUP BY WITH sets ? Yes,I'd like to try. And I think I will redesign 'with rollup' based on the 'grouping sets'.

[YANGGMM]

@Cyberleu do you have any interest in supporting GROUP BY WITH CUBE and GROUP BY WITH sets ? Yes,I'd like to try. And I think I will redesign 'with rollup' based on the 'grouping sets'.

ok, will you change the pr https://github.com/matrixorigin/matrixone/pull/18678, or open a new pr?

[Cyberleu]

@YANGGMM Hi,could you please help check my pr? Thx!

[YANGGMM]

@YANGGMM Hi,could you please help check my pr? Thx!

ok

[YANGGMM]

@YANGGMM Hi,could you please help check my pr? Thx!

but there are some conflicts and test error, @Cyberleu can you solve it first, I will check the pr later

[Cyberleu]

@YANGGMM Hi,could you please help check my pr? Thx!

but there are some conflicts and test error, @Cyberleu can you solve it first, I will check the pr later

I'm testing now, there is something wrong with the privious pr, i will modify it later.

[YANGGMM]

@YANGGMM Hi,could you please help check my pr? Thx!

but there are some conflicts and test error, @Cyberleu can you solve it first, I will check the pr later

I'm testing now, there is something wrong with the privious pr, i will modify it later.

ok, thanks

[Cyberleu]

@YANGGMM Test done.

[YANGGMM]

support group by with rollup group by with cube group by with sets grouping finished, plz test @aressu1985, plz add document about it @yangj1211

[Cyberleu]

support

• group by rollup(a,b,c)
• group by cube(a,b,c)
• group by grouping sets((a,b),(b,c),())
• grouping @aressu1985 @yangj1211

[allengaoo]

after discussed with @aressu1985, this one has entered testing phase, change milstone to 2.0.1

[Ariznawlll]

After testing, the function is basically unavailable, please @YANGGMM help to find the relevant responsible person to do the relevant repair and then transfer to me, Thanks