文档首页/ 数据湖探索 DLI/ Spark SQL语法参考/ SELECT/ 分组/ GROUPING SETS
更新时间：2024-07-01 GMT+08:00
查看PDF
分享

GROUPING SETS

功能描述

GROUPING SETS生成交叉表格行，可以实现GROUP BY字段的交叉统计。

语法格式

1
2
3
 
SELECT attr_expr_list FROM table_reference
  GROUP BY col_name_list
  GROUPING SETS(col_name_list);

关键字

GROUPING SETS：为对GROUP BY的扩展，例如

  • SELECT a, b, sum(expression) FROM table GROUP BY a, b GROUPING SETS((a,b));
    将转换为以下一条查询： 
    1
2
    		 
    SELECT a, b, sum(expression) FROM table
  GROUP BY a, b;
  • SELECT a, b, sum(expression) FROM table GROUP BY a, b GROUPING SETS(a,b);

    将转换为以下两条查询：

    1
2
3
    		 
    SELECT a, NULL, sum(expression) FROM table GROUP BY a;
UNION
SELECT NULL, b, sum(expression) FROM table GROUP BY b;
  • SELECT a, b, sum(expression) FROM table GROUP BY a, b GROUPING SETS((a,b), a);
    将转换为以下两条查询： 
    1
2
3
    		 
    SELECT a, b, sum(expression) FROM table GROUP BY a, b;
UNION
SELECT a, NULL, sum(expression) FROM table GROUP BY a;
  • SELECT a, b, sum(expression) FROM table GROUP BY a, b GROUPING SETS((a,b), a, b, ());
    将转换为以下四条查询： 
    1
2
3
4
5
6
7
    		 
    SELECT a, b, sum(expression) FROM table GROUP BY a, b;
UNION
SELECT a, NULL, sum(expression) FROM table GROUP BY a, NULL;
UNION
SELECT NULL, b, sum(expression) FROM table GROUP BY NULL, b;
UNION
SELECT NULL, NULL, sum(expression) FROM table;

注意事项

  • 所要分组的表必须是已经存在的表，否则会出错。
  • 不同于ROLLUP，GROUPING SETS目前仅支持一种格式。

示例

根据group_id与job两个字段生成交叉表格行，返回每种聚合情况下的salary总和。

1
2
3
 
SELECT group_id, job, SUM(salary) FROM group_test
  GROUP BY group_id, job
  GROUPING SETS (group_id, job);
父主题： 分组

相关文档