# GROUPING SETS 等效项

• Agg()：任何聚合函数

• (arg)：参数

## UNION ALL 的 GROUPING SETS 等效项

 ``````SELECT customer, year, SUM(sales) FROM T GROUP BY GROUPING SETS ((customer), (year))`````` ``````SELECT customer, NULL as year, SUM(sales) FROM T GROUP BY customer UNION ALL SELECT NULL as customer, year, SUM(sales) FROM T GROUP BY year``````

## 简单 GROUP BY 的 GROUPING SETS 等效项

 ``GROUP BY GROUPING SETS ( () )`` ``GROUP BY ()``

 ``GROUP BY GROUPING SETS ( (C1, C2, ..., Cn) )`` ``GROUP BY C1, C2, ..., Cn``

## GROUPING SETS ROLLUP 等效项

 ``GROUP BY ROLLUP (C1, C2, …, Cn-1, Cn)`` ``````GROUP BY GROUPING SETS ( (C1, C2, …, Cn-1, Cn) ,(C1, C2, ..., Cn-1) ... ,(C1, C2) ,(C1) ,() )``````

## GROUPING SETS CUBE 等效项

 ``GROUP BY CUBE (C1, C2, C3, ..., Cn-2, Cn-1, Cn)`` ``````GROUP BY GROUPING SETS ( (C1, C2, C3, ..., Cn-2, Cn-1, Cn) -- All dimensions are included. ,( , C2, C3, ..., Cn-2, Cn-1, Cn) -- n-1 dimensions are included. ,(C1, C3, ..., Cn-2, Cn-1, Cn) … ,(C1, C2, C3, ..., Cn-2, Cn-1,) ,(C3, ..., Cn-2, Cn-1, Cn) -- n-2 dimensions included ,(C1 ..., Cn-2, Cn-1, Cn) … ,(C1, C2) -- 2 dimensions are included. ,… ,(C1, Cn) ,… ,(Cn-1, Cn) ,… ,(C1) -- 1 dimension included ,(C2) ,… ,(Cn-1) ,(Cn) ,() ) -- Grand total, 0 dimension is included.``````

 ``GROUP BY CUBE (C1, C2, C3)`` ``````GROUP BY GROUPING SETS ( (C1, C2, C3) ,(C1, C2) ,(C1, C3) ,(C2, C3) ,(C1) ,(C2) ,(C3) ,() )``````

## 内部包含分组集的 ROLLUP 中的组合列

 ``ROLLUP(A, (C1, C2, ..., Cn) )`` ``ROLLUP( (A), (C1, C2, ..., Cn) )`` ``GROUPING SETS ( (A, C1, C2, ..., Cn), (A), () )``

## 内部包含分组集的 CUBE 中的组合列

 ``CUBE(A, (C1, C2, ..., Cn) )`` ``CUBE( (A), (C1, C2, ..., Cn) )`` ``GROUPING SETS ( (), (A), (C1, C2, ..., Cn), (A, C1, C2, ..., Cn) )``

## 包含 GROUPING SETS、ROLLUP 或 CUBE 的 GROUP BY

 ``GROUP BY A, CUBE (B, C)`` ``GROUP BY GROUPING SETS ( (A), (A, B), (A, C), (A, B, C ))``

 ``GROUP BY A, GROUPING SETS ( (B), (C) )`` ``GROUP BY GROUPING SETS ( (A, B), (A, C) )``

 ``GROUP BY ROLLUP (A, B), ROLLUP(C, D)`` ``````GROUP BY GROUPING SETS ( (),(C),(C,D),(A),(A,C),(A,C,D),(A,B),(A,B,C),(A,B,C,D) )``````

## 包含在 GROUPING SETS 列表中的 ROLLUP

 ``GROUP BY GROUPING SETS ( (A), ROLLUP (B, C) )`` ``GROUP BY GROUPING SETS ( (A), (B,C), (B), () )``

## 包含在分组集中的 ROLLUP

 ``GROUP BY GROUPING SETS(A, (B, ROLLUP(C, D)) )`` ``GROUP BY GROUPING SETS (A, B, (B,C), (B, C, D) () )``