sql server中cube運算子的使用

一、介紹

Cube運算子的作用是自動對group by子句中列出的欄位進行分組匯總運算。

Cube運算子產生的結果集是Cube。Cube是事實資料的擴充，事實資料即記錄個別事件的資料。擴充建立在使用者打算分析的列上，這些列稱為維。Cube是一個結果集，其中包含了各維度所有可能組合的交叉表格。

Cube運算子在SQL語句的Group by子句中指定。該語句的挑選清單應包含維度列和彙總函式運算式。Group by應指定維度列和關鍵字 with cube。結果集將包含維度列中各值的所有可能組合，以及與這些維度值組合相匹配的基礎行中的彙總值。

例如，一個簡單的存貨資訊表中包含如下內容：

項目	顏色	品質
Table	Blue	124
Table	Red	223
Chair	Blue	101
Chair	Red	210

 

下列查詢返回的結果集中，將包含項目和顏色的所有可能組合的品質總和，SQL語句如下：

Select 項目,顏色,sum(品質) as 總品質

From 存貨表

Group by 項目,顏色 with cube

 

下面則是顯示結果：

項目	顏色	總品質
Chair	Blue	101.00
Chair	Red	210.00
Chair	（null）	311.00
Table	Blue	124.00
Table	Red	223.00
Table	（null）	347.00
（null）	（null）	658.00
（null）	Blue	225.00
（null）	Red	433.00

 

二、執行個體講解

建立表

CREATE TABLE [dbo].[tb_stuAchievement07](

    [學生編號] [int] NULL,

    [學生姓名] [nvarchar](50) COLLATE Chinese_PRC_CI_AS NULL,

    [性別] [nvarchar](50) COLLATE Chinese_PRC_CI_AS NULL,

    [語文] [int] NULL,

    [代數] [int] NULL,

    [幾何] [int] NULL,

    [英語] [int] NULL,

    [班級] [char](10) COLLATE Chinese_PRC_CI_AS NULL

) ON [PRIMARY]

 

GO

輸入資料如所示：

  

 

輸入以下SQL語句：

select * from tb_stuAchievement07

 

select 班級,性別,avg(語文) as 語文平均成績,

avg(代數) as 代數平均成績,avg(幾何) as 幾何平均成績,

avg(英語) as 英語平均成績

from tb_stuAchievement07

group by 班級,性別

 

select 班級,性別,avg(語文) as 語文平均成績,

avg(代數) as 代數平均成績,avg(幾何) as 幾何平均成績,

avg(英語) as 英語平均成績

from tb_stuAchievement07

group by 班級,性別with cube

 

執行結果如下:

 

 

 

最後一張圖資料解析如下：

 

查詢的是二班的女生的各科平均成績

 

查詢的是二班的所有性別（男生和女生）的各科平均成績

 

查詢的是三班的所有性別（男生和女生）的各科平均成績

 

查詢的是所有班級所有性別的各科的平均成績

 

查詢的是所有班級的男生的的平均成績

 

查詢的是所有班級的男生的的平均成績

 

綜上，最後一張表中的‘null’並不是空值的意思，而是‘all’所有的意思。

 

使用 GROUPING 區分空值
CUBE 操作所產生的空值帶來一個問題：如何區分 CUBE 操作所產生的 NULL 值和從實際資料中返回的 NULL 值？這個問題可用 GROUPING 函數解決。如果列中的值來自事實資料，則 GROUPING 函數返回 0；如果列中的值是 CUBE 操作所產生的 NULL，則返回 1。在 CUBE 操作中，所產生的 NULL 代表全體值。可將 SELECT 語句寫成使用 GROUPING 函數將所產生的 NULL 替換為字串 ALL。因為事實資料中的 NULL 表明資料值未知，所以 SELECT 語句還可解碼為返回字串 UNKNOWN 替代來自事實資料的 NULL。

 

select * from tb_stuAchievement07

 

select 班級,性別,avg(語文) as 語文平均成績,

avg(代數) as 代數平均成績,avg(幾何) as 幾何平均成績,

avg(英語) as 英語平均成績

from tb_stuAchievement07

group by 班級,性別

 

select case when (grouping(班級)=1) then 'all'

       else isnull(班級,'UNKNOWN')

       end as 班級,

       case when (grouping(性別)=1) then 'all'

       else isnull(性別,'UNKNOWN')

       end as 性別,

avg(語文) as 語文平均成績,

avg(代數) as 代數平均成績,avg(幾何) as 幾何平均成績,

avg(英語) as 英語平均成績

from tb_stuAchievement07

group by 班級,性別with cube

 

執行結果如下: