# GROUPING SETS
#### 功能描述
GROUPING SETS是GROUP BY的扩展，用于在一次查询中实现多组不同粒度的分组统计，相当于将多个GROUP BY查询通过UNION ALL合并的结果。
#### 语法格式
```
SELECT projectItem [, ...] FROM table_reference
  GROUP BY { expression | GROUPING SETS ( grouping_set [, ...] ) } [, ...]
```
其中 grouping_set 为：
```
{ ( expression [, ...] ) | expression }
```
#### 参数说明
| 参数            | 说明                                                  |
|:---|:---|
| GROUPING SETS | 指定多个分组集合，每个分组集合定义一组用于GROUP BY的列。未参与分组的列在结果中以NULL填充。 |
| grouping_set  | 一个分组集合，用括号包围的列组合表示。单个列可以省略括号。                       |
   
#### GROUPING SETS等价转换
- GROUP BY a, b GROUPING SETS ((a,b)) 等价于：
  ```
  SELECT a, b, sum(expression) FROM table GROUP BY a, b;
  ```
  
- GROUP BY a, b GROUPING SETS (a, b) 等价于：
  ```
  SELECT a, NULL, sum(expression) FROM table GROUP BY a
  UNION ALL
  SELECT NULL, b, sum(expression) FROM table GROUP BY b;
  ```
  
- GROUP BY a, b GROUPING SETS ((a,b), a) 等价于：
  ```
  SELECT a, b, sum(expression) FROM table GROUP BY a, b
  UNION ALL
  SELECT a, NULL, sum(expression) FROM table GROUP BY a;
  ```
  
- GROUP BY a, b GROUPING SETS ((a,b), a, b, ()) 等价于：
  ```
  SELECT a, b, sum(expression) FROM table GROUP BY a, b
  UNION ALL
  SELECT a, NULL, sum(expression) FROM table GROUP BY a
  UNION ALL
  SELECT NULL, b, sum(expression) FROM table GROUP BY b
  UNION ALL
  SELECT NULL, NULL, sum(expression) FROM table;
  ```
  
 
#### 注意事项
- 所要分组的表必须是已经存在的表，否则会出错。
- 未参与分组的列在结果中以NULL填充。
- 可以配合GROUPING()函数判断某列是否参与了当前行的分组。
 
#### 示例
根据group_id与job两个字段生成交叉表格行：
```
SELECT group_id, job, SUM(salary) FROM group_test
   GROUP BY group_id, job
   GROUPING SETS (group_id, job);
```
多分组集合包含总计：
```
SELECT group_id, job, SUM(salary), GROUPING(group_id), GROUPING(job)
   FROM group_test
   GROUP BY group_id, job
   GROUPING SETS ((group_id, job), group_id, job, ());
```