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 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, ());