解析數倉OLAP函式：ROLLUP、CUBE、GROUPING SETS

華為雲開發者社群發表於2022-04-27
函式
摘要：GaussDB(DWS) ROLLUP，CUBE，GROUPING SETS等OLAP函式的原理解析。

本文分享自華為雲社群《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_class
union all
select c_country, null, sum(c_balance) from customer group by c_country
union all
select 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_class
union all
select c_country, null, sum(c_balance) from customer group by c_country
union all
select null, null, sum(c_balance) from customer
union all
select 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_country
union all
select null, c_class, sum(c_balance) from customer group by c_class
order 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函式的執行流程。

 

點選關注，第一時間瞭解華為雲新鮮技術~

相關文章