写点什么

解析数仓 OLAP 函数:ROLLUP、CUBE、GROUPING SETS

  • 2022 年 4 月 27 日
  • 本文字数:2890 字

    阅读完需:约 9 分钟

本文分享自华为云社区《GaussDB(DWS) OLAP函数浅析》,作者: DWS_Jack_2。


在一些报表场景中,经常会对数据做分组统计(group by),例如对一级部门下辖的二级部门员工数进行统计 

create table emp(id int,     --工号name text,  --员工名dep_1 text, --一级部门dep_2 text  --二级部门);gaussdb=# select count(*), dep_2 from emp group by dep_2; count | dep_2-------+-------   200 | SRE   100 | EI(2 rows)
复制代码

常见的统计报表业务中,通常需要进一步计算一级部门的“合计”人数,也就是二级部门各分组的累加,就可以借助于 rollup,如下所示,比前面的分组计算结果多了一行合计的数据

gaussdb=# select count(*), dep_2 from emp group by rollup(dep_2); count | dep_2-------+-------   200 | SRE   100 | EI   300 |(3 rows)
复制代码

如上是一种 group by 扩展的高级分组函数使用场景,这一类分组函数统称为 OLAP 函数,在 GaussDB(DWS)中支持 ROLLUP,CUBE,GROUPING SETS,下面对这几种 OLAP 函数的原理和应用场景做一下分析。


首先我们来创建一张表,customer,用户信息表,其中包含了用户 id,用户名,年龄,国家,用户级别,性别,余额等信息

create table customer( c_id char(16) not null, c_name char(20) , c_age integer , c_country varchar(20) , c_class char(10), c_sex text, c_balance numeric);insert into customer values(1, 'tom', '20', 'China', '1', 'male', 300);insert into customer values(2, 'jack', '30', 'USA', '1', 'male', 100);insert into customer values(3, 'rose', '40', 'UK', '1', 'female', 200);insert into customer values(4, 'Frank', '60', 'GER', '1', 'male', 100);insert into customer values(5, 'Leon', '20', 'China', '2', 'male', 200);insert into customer values(6, 'Lucy', '20', 'China', '1', 'female', 500);
复制代码

ROLLUP

本文开头的示例已经解释了,ROLLUP 是在分组计算基础上增加了合计,从字面意思理解,就是从最小聚合级开始,聚合单位逐渐扩大,例如如下语句

select c_country, c_class, sum(c_balance) from customer group by rollup(c_country, c_class) order by 1,2,3; c_country |  c_class   | sum  -----------+------------+------ China     | 1          |  800 China     | 2          |  200 China     |            | 1000 GER       | 1          |  100 GER       |            |  100 UK        | 1          |  200 UK        |            |  200 USA       | 1          |  100 USA       |            |  100           |            | 1400(10 rows)
复制代码

该语句功能等价于如下

select c_country, c_class, sum(c_balance) from customer group by c_country, c_classunion allselect c_country, null, sum(c_balance) from customer group by c_countryunion allselect null, null, sum(c_balance) from customer order by 1,2,3; c_country |  c_class   | sum  -----------+------------+------ China     | 1          |  800 China     | 2          |  200 China     |            | 1000 GER       | 1          |  100 GER       |            |  100 UK        | 1          |  200 UK        |            |  200 USA       | 1          |  100 USA       |            |  100           |            | 1400(10 rows)
复制代码

尝试理解一下

GROUP BY ROLLUP(A,B):

首先对(A,B)进行 GROUP BY,然后对(A)进行 GROUP BY,最后对全表进行 GROUP BY 操作

CUBE

CUBE 从字面意思理解,就是各个维度的意思,也就是说全部组合,即聚合键中所有字段的组合的分组统计结果,例如如下语句

select c_country, c_class, sum(c_balance) from customer group by cube(c_country, c_class) order by 1,2,3; c_country |  c_class   | sum  -----------+------------+------ China     | 1          |  800 China     | 2          |  200 China     |            | 1000 GER       | 1          |  100 GER       |            |  100 UK        | 1          |  200 UK        |            |  200 USA       | 1          |  100 USA       |            |  100           | 1          | 1200           | 2          |  200           |            | 1400(12 rows)
复制代码

该语句功能等价于如下

select c_country, c_class, sum(c_balance) from customer group by c_country, c_classunion allselect c_country, null, sum(c_balance) from customer group by c_countryunion allselect null, null, sum(c_balance) from customerunion allselect NULL, c_class, sum(c_balance) from customer group by c_class order by 1,2,3; c_country |  c_class   | sum  -----------+------------+------ China     | 1          |  800 China     | 2          |  200 China     |            | 1000 GER       | 1          |  100 GER       |            |  100 UK        | 1          |  200 UK        |            |  200 USA       | 1          |  100 USA       |            |  100           | 1          | 1200           | 2          |  200           |            | 1400(12 rows)
复制代码

理解一下

GROUP BY CUBE(A,B):

首先对(A,B)进行 GROUP BY,然后依次对(A)、(B)进行 GROUP BY,最后对全表进行 GROUP BY 操作。


GROUPING SETS

GROUPING SETS 区别于 ROLLUP 和 CUBE,并没有总体的合计功能,相当于从 ROLLUP 和 CUBE 的结果中提取出部分记录,例如如下语句

select c_country, c_class, sum(c_balance) from customer group by grouping sets(c_country, c_class) order by 1,2,3; c_country |  c_class   | sum  -----------+------------+------ China     |            | 1000 GER       |            |  100 UK        |            |  200 USA       |            |  100           | 1          | 1200           | 2          |  200(6 rows)
复制代码

该语句功能等价于如下

select c_country, null, sum(c_balance) from customer group by c_countryunion allselect null, c_class, sum(c_balance) from customer group by c_classorder by 1,2,3; c_country |  ?column?  | sum  -----------+------------+------ China     |            | 1000 GER       |            |  100 UK        |            |  200 USA       |            |  100           | 1          | 1200           | 2          |  200(6 rows)
复制代码

理解一下

GROUP BY GROUPING SETS(A,B):

分别对(B)、(A)进行 GROUP BY 计算

目前在 GaussDB(DWS)中,OLAP 函数的实现,会有排序(sort)操作,相比等价的 union all 操作,效率并不会有提升,后续会通过 mixagg 的支持来提升 OLAP 函数的执行效率,有兴趣的同学,可以 explain 打印一下计划,来看一下 OLAP 函数的执行流程。


点击关注,第一时间了解华为云新鲜技术~​

发布于: 刚刚阅读数: 2
用户头像

提供全面深入的云计算技术干货 2020.07.14 加入

华为云开发者社区,提供全面深入的云计算前景分析、丰富的技术干货、程序样例,分享华为云前沿资讯动态,方便开发者快速成长与发展,欢迎提问、互动,多方位了解云计算! 传送门:https://bbs.huaweicloud.com/

评论

发布
暂无评论
解析数仓OLAP函数:ROLLUP、CUBE、GROUPING SETS_Rollup_华为云开发者社区_InfoQ写作社区