SQL 研究 - Cube 和 RollUp

使用Pivot，你终于满足了老板的要求，生成了各个投资人在Asia和Europe的投资统计表。在赞许的同时，他又提出新的需求：

需要统计每个投资方在各个地区的投资总和，各个投资方的所有投资，以及各个地区所吸引的所有投资。

 

又一次，你跑到数据库里，看到如下的表：

CREATE TABLE [dbo].[Investment](
 [Investor] [nvarchar](256) NULL,
 [Capital] [float] NULL,
 [District] [nvarchar](256) NULL
)

 

select * from [dbo].[Investment] 得到如下结果：

Investor Capital District

 IBM 1000000 Asia
IBM 150000 Europe
Oracle 800000 Asia
Oracle 180000 Europe
Microsoft 900000 Asia
Microsoft 180000 Europe
IBM 800000 Europe
Oracle 80000 Asia
Microsoft 70000 Europe

 

于是你自然的想到这样的方法：

select Investor, Capital, sum(District) from dbo.Investment group by Investor,Capital

union all

select Investor, null, sum(District) from dbo.Investment group by Investor

Union all

select Null, Capital, sum(District) from dbo.Investment group by Capital

 

这样你当然得到了想要的结果，可是三个语句独立执行，相互间存在重复的运算，效率必然不高。再者，这里只有两个维度，Investor和District，倘若有三个维度，就需要执行7次；4个维度就需要15次。可见这种方法有它的局限性。

 

正确的做法是什么呢？ 对， Cube，它能自动列出维度列的值所有可能的组合，并计算这些组合对应的聚合函数。

应用Cube来解决上面的问题，我们可以得到如下简洁的答案：

select Investor, Capital, sum(District)

from dbo.Investment

group by Investor,Capital with Cube

 

它的结果能回答老板所提出的3个问题，并附赠一行，所有投资方在所有地区的投资总和。

 

当然，写SQL有时候跟作画一样，不能少一笔，也不能多一笔，现在多了这行，也挺难看的。别急，办法是有的：

select Investor, District, sum(Capital)

from dbo.Investment

group by Investor,Districtwith Cube

Having Grouping(Investor)<>1 or grouping(District)<>1

好吧，Grouping是依附于Cube的一个函数，它用于判断在当前的分组中，它参数指定的列是不是被置为null。注意是置为null，不是本身就是null。

 

当然，聪明如你的读者，也就明白如果表中存在多个维度列，而你只希望对其中一些做聚合时，该如何使用Cube这一强有力的工具了。

 

当然老板的要求总是变化多端的，如果老板只对投资人感兴趣，他很可能不要查看Investor被聚合掉的情况，这个时候你需要Rollup，因为Investor和District此时已不是平等的维度关系，Investor高于后者。

SQL如下：

select Investor, District, sum(Capital)

from dbo.Investment

group by Investor,District with rollup

 

结果如下：

Investor District Capital

 IBM Asia 1000000
IBM Europe 950000
IBM NULL 1950000
Microsoft Asia 900000
Microsoft Europe 250000
Microsoft NULL 1150000
Oracle Asia 880000
Oracle Europe 180000
Oracle NULL 1060000
NULL NULL 4160000

 

跟之前with Cube产生的结果对比，新的结果集不存在Investor为Null的情况，除了最后一行。如果将上述结果做成一个view，便可以在之上的查询加入District不为NULL的条件，最后得到各个Investor的投资总和。

 

 最后，需要注意的是，跟手动编写的Group by相比，产生相同结果，With Cube要多花10%左右的时间，是什么原因造成这个性能的差异还需进一步研究。