介绍
一组数据DataSet,有多个维度A,B,我们需要对这组数据进行一下聚合:
- 全量数据的Min,Max,Avg
- 按维度A聚合,计算 Min,Max,Avg
- 按维度B聚合,计算 Min,Max,Avg
- 按维度AB聚合,计算 Min,Max,Avg
实现这个需求的直觉是根据不同的维度分别聚合计算出Min,Max,Avg结果,然后再用UNION ALL把所有结果连接起来:
-- 全量数据的Min,Max,Avg
SELECT
NULL,
NULL,
Min,
Max,
Avg
from
DataSet
UNION ALL
-- 按维度A聚合,计算 Min,Max,Avg
SELECT
A,
NUll,
Min,
Max,
Avg
from
DataSet
GROUP BY
A
UNION ALL
-- 按维度B聚合,计算 Min,Max,Avg
SELECT
NULL,
B,
Min,
Max,
Avg
from
DataSet
GROUP BY
B
UNION ALL
--按维度AB聚合,计算 Min,Max,Avg
SELECT
A,
B,
Min,
Max,
Avg
from
DataSet
GROUP BY
A,
B
这种方式能达到效果,但是实现起来比较繁琐,如果维度比较多,就要一长串的SQL代码。
那么有没有比较简单且优雅的实现方式呢?
使用 GROUPING SETS
替代
SELECT
A,
B,
Min,
Max,
Avg
from
DataSet
GROUP BY
A,
B
GROUPING SETS(
(A),
(B),
(A,B),
()
)
这个写法也太简洁了吧!!
当数据集中有多个维度,并且需要实现维度的多组合聚合, 使用GROUPING SETS可以完美解决
使用案例
有一组薪酬明细数据,维度有部门,职级,需要计算一下结果:
- 全年级成绩的Min,Max,Avg
- 班级成绩的Min,Max,Avg
- 课程成绩的Min,Max,Avg
- 班级课程成绩的Min,Max,Avg
1)准备数据
李明,一班,语文,80
韩梅梅,一班,语文,90
李明,一班,数学,90
韩梅梅,一班,数学,90
露西,二班,语文,90
莉莉,二班,语文,100
露西,二班,数学,100
莉莉,二班,数学,90
CREATE TABLE yishou_daily_test.score_detail (
name string,
class string,
course string,
score int
)
...
- 多维度分组聚合计算
SELECT
coalesce(class,'全部') as class,
coalesce(course,'全部') as course,
Min(score) as min_score,
Max(score) as max_score,
Avg(score) as avg_score
from
yishou_daily_test.score_detail
GROUP BY
class,
course
GROUPING SETS(
(class),
(course),
(class,course),
()
)
执行结果:
class | course | min_score | max_score | avg_score |
---|---|---|---|---|
一班 | 全部 | 80 | 90 | 87.5 |
一班 | 语文 | 80 | 90 | 85 |
一班 | 数学 | 90 | 90 | 90 |
全部 | 全部 | 80 | 100 | 91.25 |
全部 | 数学 | 90 | 100 | 92.5 |
全部 | 语文 | 80 | 100 | 90 |
二班 | 数学 | 90 | 100 | 95 |
二班 | 全部 | 90 | 100 | 95 |
二班 | 语文 | 90 | 100 | 95 |