数据湖探索 DLI-GROUPING SETS:关键字

时间：2023-11-03 17:00:59

数据湖探索 DLI 分组

关键字

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

将转换为以下四条查询：

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;