Repository navigation
support GROUP BY modifiers #4250
Description
Request Description
MySQL and Oracle support ROLLUP modifier, SQL Server also supports CUBE and GROUPING SETS
References:
- MySQL: GROUP BY Modifiers
- Oracle Database:
- SQL Server: SELECT - GROUP BY
An example in MySQL:
drop table if exists t;
create table t(a bigint, b bigint, c bigint);
insert into t values(1, 2, 3), (2, 2, 3), (3, 2, 3);MySQL > select a, min(b) from t group by a with rollup;
+------+--------+
| a | min(b) |
+------+--------+
| 1 | 2 |
| 2 | 2 |
| 3 | 2 |
| NULL | 2 |
+------+--------+
4 rows in set (0.01 sec)Category
Feature
Value
- Value Point: 2
It's hard to tell the value for now. Quoted from https://docs.oracle.com/cd/F49540_01/DOC/server.815/a68003/rollup_c.htm:
One of the key concepts in decision support systems is "multi-dimensional analysis": examining the enterprise from all necessary combinations of dimensions. We use the term "dimension" to mean any category used in specifying questions. Among the most commonly specified dimensions are time, geography, product, department, and distribution channel, but the potential dimensions are as endless as the varieties of enterprise activity. The events or entities associated with a particular set of dimension values are usually referred to as "facts." The facts may be sales in units or local currency, profits, customer counts, production volumes, or anything else worth tracking.
I think the value of this request is to improve the usability and performance in Business Intelligence(BI) scenarios. No need to run several aggregate queries to get another level aggregate result. I'm curious about whether are some BI tools rely on this feature.
Workload Estimation
Workload contains coding, documenting, and testing.
1 Point for 1 Person/Work Day
- 60 Points, hard to say, most modifications are on the aggregate operator in execution engine.
Metadata
Metadata
Assignees
Labels
Type
Projects
- Status
Activity
Lloyd-longfei commentedon Mar 8, 2018
My also encountered the same problem with TIDB, and when writing SQL, the following mistakes were made:
This problem may directly affect the running of our online production environment, when can you increase the function, or tell us how to solve the problem. @hanfei1991
zz-jason commentedon Mar 8, 2018
@Lloyd-longfei Thanks for your feed back, I'm sorry to tell you that
group by ... with rollupis not on our roadmap now, but we'll support it as soon as possible.For now, a workaround is to write another sql to get the aggregation summary, see https://dev.mysql.com/doc/refman/5.7/en/group-by-modifiers.html for more detail.
morgo commentedon Dec 29, 2018
Confirming that this feature is still missing:
vkingnew commentedon Mar 28, 2019
this feature is still not support as tidb 2.1.7,and which version to support ROLLUP and CUBE
bhzhu203 commentedon May 6, 2021
TiDB-v5.0.0 still does not support the "ROLLUP" fuction. Our product managers and staff managers like to write the BI statistics queries with the "ROLLUP" fuction , and hard to remove all of the "ROLLUP" from the losts of queries .
The replacement of the "ROLLUP" fuction costs a lot. The "ROLLUP" fuction exists in our BI queries over 40% . We still using the slow mysql instance because that. Hope TiDB will support "ROLLUP" . Thanks
rollup-sample.sql.gz
Enochack commentedon May 11, 2021
/pick
Enochack commentedon May 11, 2021
/assign
withRollup: The design doc for withRollup clause. (pingcap#4250)
Defined2014 commentedon Aug 20, 2024
We already supported
rollupright now, ref #42631 . So close this issue.