ROLLUP

功能描述

ROLLUP是GROUP BY的扩展，用于生成多级聚合行、超聚合行和总计行。ROLLUP按照从右到左的顺序递减分组粒度，实现层次化的聚合统计。

语法格式

SELECT projectItem [, ...] FROM table_reference
   GROUP BY ROLLUP ( expression [, ...] ) 

或使用WITH语法：

SELECT projectItem [, ...] FROM table_reference
   GROUP BY expression [, ...] WITH ROLLUP;

参数说明

ROLLUP等价转换

GROUP BY ROLLUP(a, b, c) 等价于以下四个分组查询的UNION ALL：

• (a, b, c) 分组小计：

  SELECT a, b, c, sum(expression) FROM table GROUP BY a, b, c;

• (a, b) 分组小计：

  SELECT a, b, NULL, sum(expression) FROM table GROUP BY a, b;

• (a) 分组小计：

  SELECT a, NULL, NULL, sum(expression) FROM table GROUP BY a;

• () 总计：

  SELECT NULL, NULL, NULL, sum(expression) FROM table;

注意事项

• 所要分组的表必须是已经存在的表，否则会出错。
• ROLLUP的分组粒度从右到左递减，最右侧的列最先被去除。
• 可以配合GROUPING()函数判断某列是否参与了当前行的分组。

示例

根据group_id与job生成多级聚合：

SELECT group_id, job, SUM(salary) FROM group_test
   GROUP BY ROLLUP (group_id, job); 

等价于WITH语法：

SELECT group_id, job, SUM(salary) FROM group_test 
  GROUP BY group_id, job WITH ROLLUP; 

配合GROUPING()函数标识分组级别：

SELECT group_id, job, SUM(salary),
   GROUPING(group_id) AS g1, GROUPING(job) AS g2 
   FROM group_test
   GROUP BY ROLLUP (group_id, job);