grouping sets和rollup、cube的用法

grouping sets和rollup、cube都可以对不同维度的数据进行汇总，主要区别就是不同用法的灵活性不一样。

1、grouping sets

grouping sets可以根据自己的需要，灵活调整维度的组合，可以根据实际需要调整维度组合。

语法：

select A,B,sum(total) total

from table

group by

grouping sets(

(),  ----不保留维度，所有数据汇总

(A,B),  ----相当于group by A,B

(A),  ----相当于group by A

(B)  ---相当于 group by B

)

例如：

假设有张数据表sales_info，里面存了region，product和sales三个字段

region	product	sales
SOUTH	A	100
SOUTH	B	200
SOUTH	C	300
NORTH	A	200
NORTH	B	350
WEST	A	350
WEST	C	450

如果要对不同维度的数据进行汇总，grouping sets的执行如下：

select region,product,sum(sales) total_sales
from sales_info 
group by 
grouping sets
(
    (),
    (region),
    (product),
    (region,product),
);

结果为：

1）没有任何维度，总的结果

2）region作为维度分组的结果

3）product作为维度分组的结果

4）region，product作为维度分组的结果

region	product	total_sales
null	null	1950
NORTH	null	550
SOUTH	null	600
WEST	null	800
null	A	650
null	B	550
null	C	750
NORTH	A	200
NORTH	B	350
SOUTH	A	100
SOUTH	B	200
SOUTH	C	300
WEST	A	350
WEST	C	450

等同于

select null region,null product,sum(sales) total_sales
from sales_info 
union all
select region,null product,sum(sales) total_sales
from sales_info 
group by region
union all
select null region,product,sum(sales) total_sales
from sales_info 
group by product
union all
select region,product,sum(sales) total_sales
from sales_info 
group by region,product

---

2、rollup

rollup生成分层汇总，从最详细的级别到总计

语法：

（1）

select A,B,sum(total) total

from table

group by

a）rollup (A,B)  ---有三层group by, 一是group by A,B；二是group by A；三是不保留维度，直接汇总

b）rollup (B,A)   ---有三层group by, 一是group by B,A；二是group by B；三是不保留维度，直接汇总

（2）

select A,sum(total) total

from table

group by

rollup (A)    ---有两层group by, 一是group by A；二是不保留维度，直接汇总

（3）

select B,sum(total) total

from table

group by

rollup (B)   ---有两层group by, 一是group by B；二是不保留维度，直接汇总

---

例如：

select region,product,sum(sales) total_sales
from sales_info
group by 
rollup (region,product)

结果为：

要实现region和product的维度计算，用rollup只能得到

1）region，product作为维度分组的结果

2）region作为维度分组的结果

3）没有任何维度，总的结果

region	product	total_sales
NORTH	A	200
NORTH	B	350
SOUTH	A	100
SOUTH	B	200
SOUTH	C	300
WEST	A	350
WEST	C	450
NORTH	null	550
SOUTH	null	600
WEST	null	800
null	null	1950

等同于

select region,product,sum(sales) total_sales
from sales_info 
group by region,product
union all
select region,null product,sum(sales) total_sales
from sales_info 
group by region
union all
select null region,null product,sum(sales) total_sales
from sales_info 

---

3、cube

cube生成所有可能的分组组合的汇总，包含所有维度的组合

语法：

select A,B,sum(total) total

from table

group by

cube (A,B)   ---穷尽所有组合：group by A,B；group by A；group by B；不保留维度，直接汇总

例如：

select region,product,sum(sales) total_sales
from sales_info
group by 
cube (region,product)

结果为：

用cube来实现分组，得到的结果是

1）没有维度，总的结果

2）region作为维度分组的结果

3）product作为维度分组的结果

4）region，product作为维度分组的结果

region	product	total_sales
null	null	1950
NORTH	null	550
SOUTH	null	600
WEST	null	800
null	A	650
null	B	550
null	C	750
NORTH	A	200
NORTH	B	350
SOUTH	A	100
SOUTH	B	200
SOUTH	C	300
WEST	A	350
WEST	C	450

cube穷尽了所有维度的组合，在这里等同于grouping sets把所有组合列举出来。如果需要穷尽所有维度的组合，用cube就可以。