grouping sets 高級聚合本質是多重`group by`進行`union all`操作。 ```sql SELECT a,b,SUM(c) FROM tab1 GROUP BY a,b GROUPING SETS((a,b)，a，b,()); -- 等價于 SELECT a,b,SUM(c) FROM tab1 GROUP BY a,b union SELECT a,null,SUM(c) FROM tab1 GROUP BY a,null union SELECT null,b,SUM(c) FROM tab1 GROUP BY null,b union SELECT null,null,SUM(c) FROM tab1; ``` :-: 表 GROUPING SETS 及等價語句 | GROUPING SETS 語句 | 等價于 GROUP BY 語句 | | --- | --- | | SELECT a, b, SUM(c) FROM tab1 <br/>GROUP BY a, b <br/>GROUPING SETS ((a, b), a, b, ( )); | SELECT a, b, SUM( c ) FROM tab1 GROUP BY a, b <br/> UNION <br/> SELECT a, null, SUM( c ) FROM tab1 GROUP BY a, null <br/> UNION <br/> SELECT null, b, SUM( c ) FROM tab1 GROUP BY null, b <br/> UNION <br/> SELECT null, null, SUM( c ) FROM tab1; | | SELECT a, b, SUM( c ) FROM tab1 <br/> GROUP BY a, b GROUPING SETS ( (a,b), a); | SELECT a, b, SUM( c ) FROM tab1 GROUP BY a, b <br/> UNION <br/> SELECT a, null, SUM( c ) FROM tab1 GROUP BY a; | | SELECT a, b, SUM(c) FROM tab1 GROUP BY a, b GROUPING SETS ( (a,b) ); | SELECT a, b, SUM(c) FROM tab1 GROUP BY a, b; | | SELECT a,b, SUM(c) FROM tab1 GROUP BY a, b GROUPING SETS (a,b); | SELECT a, null, SUM( c ) FROM tab1 GROUP BY a <br/> UNION <br/> SELECT null, b, SUM( c ) FROM tab1 GROUP BY b; | **`GROUP BY WITH CUBE|ROLLUP`** >`CUBE`：對分組列進行所有可能組合的聚合，是 group by 字段的所有組合 `ROLLUP`：計算維度層次級別上的聚合 :-: 表 ROLLUP/CUBE 及等價語句 | ROLLUP/CUBE 語句 | 等價于 GROUP BY 語句 | | --- | --- | | SELECT <br/> a, b, SUM( c ) <br/> FROM tab1 <br/> GROUP BY a, b, c WITH CUBE; | SELECT <br/> a, b, SUM( c ) <br/> FROM tab1 <br/> GROUP BY a, b, c <br/> GROUPING SETS ( (a, b, c), (a, b), (b, c), (a, c), (a), (b), (c), ( )); | | SELECT <br/> a, b, SUM( c ) <br/> FROM tab1 <br/> GROUP BY a, b, c WITH ROLLUP; | SELECT <br/> a, b, SUM( c ) <br/> FROM tab1 <br/> GROUP BY a, b, c GROUPING SETS ( (a, b, c), (a, b), (a), ( )); |