数据库ROLLUP与CUBE

概览

ROLLUP 和 CUBE 都是用来在差查询结果中生成合计的项目。

这两个关键字需要和 GROUP BY 来一起使用,需要注意的是 GROUP BY 关键字后面可以跟多个列名,这多个列名之间的顺序和输出的顺序是相关的。

ROLLUP 和 CUBE 的操作都要依赖 GROUP BY。

GROUPING

GROUPING 用来区分是本来内容就是 null 还是是因为 ROLLUP 和 CUBE 而产生的 null 值,如果是后者那么 GROUPING(列名) 返回的结果就是 1,否则则是零。

ROLLUP 统计的时候会默认生成 null,这时候就需要自己填入统计的结果了。

ROLLUP

ROLLUP 产生的合计项目和 GROUP BY 中的多个列名的顺序有关。

ROLLUP 会根据 GROUP BY 的第一个列来进行分组并且输出汇总的内容。

例子:

1
2
3
4
5
6
7
8
select case when grouping(city) = 1 then 'ALL'
else city
end as city,
case when grouping(state) = 1 then 'ALL'
else state
end as state
from pubs.dbo.authors
group by city, state with rollup;

运行结果

1
2
3
4
5
6
7
8
Ann Arbor	MI
Ann Arbor ALL
……省略若干行
Vacaville CA
Vacaville ALL
Walnut Creek CA
Walnut Creek ALL
ALL ALL

在这里是对应的每个不同的州进行分组,然后对每个组进行汇总,最终对所有的州再进行一次汇总。

CUBE

CUBE 产生的合计项目和 GROUP BY 中的多个列名的顺序在某种程度上可以说是无关。

CUBE 产生的结果不仅会产生 ROLLUP 的结果,还会产生 ROLLUP 中没出现的另一种顺序的结果。

例子:

1
2
3
4
5
6
7
8
select case when grouping(city) = 1 then 'ALL'
else city
end as city,
case when grouping(state) = 1 then 'ALL'
else state
end as state
from pubs.dbo.authors
group by city, state with cube;

结果:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
Berkeley	CA
Covelo CA
Menlo Park CA
Oakland CA
Palo Alto CA
San Francisco CA
San Jose CA
Vacaville CA
Walnut Creek CA
ALL CA
Gary IN
ALL IN
Lawrence KS
ALL KS
Rockville MD
ALL MD
Ann Arbor MI
ALL MI
Corvallis OR
ALL OR
Nashville TN
ALL TN
Salt Lake City UT
ALL UT
ALL ALL
Ann Arbor ALL
Berkeley ALL
Corvallis ALL
Covelo ALL
Gary ALL
Lawrence ALL
Menlo Park ALL
Nashville ALL
Oakland ALL
Palo Alto ALL
Rockville ALL
Salt Lake City ALL
San Francisco ALL
San Jose ALL
Vacaville ALL
Walnut Creek ALL

如果忽视输出数据的顺序,那么这个结果相当于两个 ROLLUP 的结果作 UNION

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
select case when grouping(city) = 1 then 'ALL'
else city
end as city,
case when grouping(state) = 1 then 'ALL'
else state
end as state
from pubs.dbo.authors
group by city, state with rollup
union
select case when grouping(city) = 1 then 'ALL'
else city
end as city,
case when grouping(state) = 1 then 'ALL'
else state
end as state
from pubs.dbo.authors
group by state, city with rollup;