Oracle的rollup、cube、grouping sets函式

的group by除了基本用法以外，還有3種擴充套件用法，分別是rollup、cube、grouping sets。

1 rollup

假設有一個表test，有A、B、C、D、E5列。

如果使用group by rollup(A,B,C)，首先會對(A、B、C)進行GROUP BY，然後對(A、B)進行GROUP BY，然後是(A)進行GROUP BY，最後對全表進行GROUP BY操作。roll up的意思是“捲起”，這也可以幫助我們理解group by rollup就是對選擇的列從右到左以一次少一列的方式進行grouping直到所有列都去掉後的grouping(也就是全表grouping)，對於n個引數的rollup，有n+1次的grouping。以下2個sql的結果集是一樣的：

Select A,B,C,sum(E) from test group by rollup(A,B,C)

與

Select A,B,C,sum(E) from test group by A,B,C

union all

Select A,B,null,sum(E) from test group by A,B

union all

Select A,null,null,sum(E) from test group by A

union all

Select null,null,null,sum(E) from test

2 cube

cube的意思是立方，對cube的每個引數，都可以理解為取值為參與grouping和不參與grouping兩個值的一個維度，然後所有維度取值組合的集合就是grouping的集合，對於n個引數的cube，有2^n次的grouping。如果使用group by cube(A,B,C),，則首先會對(A、B、C)進行GROUP BY，然後依次是(A、B)，(A、C)，(A)，(B、C)，(B)，(C)，最後對全表進行GROUP BY操作，一共是2^3=8次grouping。同rollup一樣，也可以用基本的group by加上結果集的union all寫出一個與group by cube結果集相同的sql：

Select A,B,C,sum(E) from test group by cube(A,B,C);

與

Select A,B,C,sum(E) from test group by A,B,C

union all

Select A,B,null,sum(E) from test group by A,B

union all

Select A,null,C,sum(E) from test group by A,C

union all

Select A,null,null,sum(E) from test group by A

union all

Select null,B,C,sum(E) from test group by B,C

union all

Select null,B,null,sum(E) from test group by B

union all

Select null,null,C,sum(E) from test group by C

union all

Select null,null,null,sum(E) from test;

3 grouping sets

grouping sets就是對引數中的每個引數做grouping，也就是有幾個引數做幾次grouping,例如使用group by grouping sets(A,B,C)，則對(A),(B),(C)進行group by，如果使用group by grouping sets((A,B),C),則對(A,B),(C)進行group by。甚至grouping by grouping set(A,A)都是語法允許的，也就是對(A)進行2次group by,grouping sets的引數允許重複

4 總結

rollup        (N+1個分組方案)

cube         (2^N個分組方案)

grouping sets (自定義羅列出分組方案)

5 注意點

5.1 機制不同

在rollup和cube的說明中分別給出了用基本group by加結果集union all給出了結果集相同的sql，但這只是為了理解的方便而給出的sql，並不說明rollup和cube與基本group by加結果集union all等價。實際上兩者的內部機制是安全不一樣的，前者除了寫法簡潔以外，執行時不需多次掃描表，效率遠比後者高。

5.2 集合可運算

3種擴充套件用法的引數可以是源表中的某一個具體的列，也可以是若干列經過計算而形成的一個新列（比如說A+B，A||B），也可以是這兩種列的一個集合（例如（A+B，C）），對於grouping set更是特殊，可以是空集合()，表示對全表進行group by。

5.3 group by 與 rollup, cube組合使用

3）Group by的基本用法以及這3種擴充套件用法可以組合使用，也就是說可以出現group by A,rollup(A,B)這樣的用法，oracle將對出現在group by中的每種用法的grouping列集合做笛卡爾積然後對其中的每一個元素做group by。這話說起來挺繞口，舉例說明吧，group by A, rollup(A,B)，基本用法的grouping集合是(A),rollup(A,B)的grouping集合是((A,B),(A),()),兩個集合的笛卡爾積集合是((A,A,B),(A,A),(A))，所以會首先對(A,A,B)做group by，然後對(A,A)做group by，最後對(A)做group by。實際上對(A,A,B)做group by和對(A,B)做group by兩者是完全等價的(group by A,A,B結果和group by A,B完全一樣)，同理對(A,A)做group by和對(A)做group by也是等價的。簡化後的結果就是首先對(A,B)做group by，然後對(A)做group by，最後再對(A)做group by。下面給出兩個等價的sql以便理解：

Select A,B,sum(E) from test1 group by A, rollup(A,B);

與

Select A,B,sum(E) from test1 group by A,B

Union all

Select A,null,sum(E) from test1 group by A

Union all

Select A,null,sum(E) from test1 group by A;

6 grouping()、grouping_id()、group_id()

6.1 grouping()

引數只有一個，而且必須為group by中出現的某一列，表示結果集的一行是否對該列做了grouping。對於對該列做了grouping的行而言，grouping()=0，反之為1；

6.2 grouping_id()

引數可以是多個，但必須為group by中出現的列。Grouping_id()的返回值其實就是引數中的每列的grouping()值的二進位制向量，例如如果grouping(A)=1，grouping(B)=0，則grouping_id(A,B)的返回值就是二進位制的10，轉成10進位制就是2。

6.3 group_id()

無引數。見上面的說明3），group by對某些列的集合會進行重複的grouping，而實際上絕大多數情況下對結果集中的這些重複行是不需要的，那就必須有辦法剔出這些重複grouping的行。當結果集中有n條重複grouping而形成的行時，每行的group_id()分別是0,1,…,n，這樣我們在條件中加入一個group_id()<1就可以剔出這些重複grouping的行了。

7 示例

7.1 建表與資料

SQL> create table test(department_id number, a varchar2(20), b varchar2(20));

Table created

SQL> insert into test values(10, 'A', 'B');

1 row inserted

SQL> commit;

Commit complete

7.2 查詢語句

select department_id,

       a,

       b,

       grouping(department_id),

       grouping(a),

       grouping(b)

  from test

 group by rollup(department_id, a, b)

order by 4, 5, 6;

select department_id,

       a,

       b,

       grouping(department_id),

       grouping(a),

       grouping(b)

  from test

 group by cube(department_id, a, b)

order by 4, 5, 6;