報表開發之擴充套件GROUP BY

Allen-Li發表於2014-11-02
套件

在實際運用中，比如在資料倉儲中，經常需要對資料進行多維分析，不僅需要標準分組的結果（相當於

GROUP BY），還需要不同維度的小計（簡單 GROUP BY 中取部分列分組）和合計（不分組），從而

提供多角度的資料分析，對於這種複雜分組需求，簡單 GROUP BY 很難達到這種目的，當然，我們可以

使用 UNION 或 UNION ALL 將不同維度的分組結果聯合起來，但效能往往不好，此時，我們可以使用擴

展 GROUP BY 來滿足實際運用中出現的大部分多維分組問題。


1. 擴充套件 GROUP BY 概述

擴充套件 GROUP BY 進行多維資料統計的工作，主要表現在：

a. ROLLUP、CUBE、GROUPING SETS 擴充套件 GROUP BY 子句提供了豐富的多維分組統計功能；
b. 3個擴充套件分組函式：GROUPING、GROUPING_ID、GROUP_ID 提供擴充套件 GROUP BY 的輔助功
能，例如，提供區別結果行屬於哪個分組級別、區分 NULL 值、建立有意義的報表、對彙總結果排
序、過濾結果行等功能
c. 對擴充套件 GROUP BY 允許按重複列分組、組合列分組、部分分組、連線分組等，另外 GROUPING 
SETS 可以接受 CUBE、ROLLUP 操作作為引數，這些功能使擴充套件 GROUP BY 更加強大。


2. ROLLUP
2.1 UNION ALL 實現 ROLLUP 功能
假設有這樣的需求：
a. 統計每個部門每個職位的薪水和
b. 統計每個部門所有職位的薪水小計
c. 統計所有部門所有職位的薪水合計
d. 需要顯示部門名、職位名和累加後的薪水值

-- 需求一實現
select d.dname,e.job,sum(e.sal) sum_sal from dept d,emp e
where d.deptno=e.deptno group by d.dname,e.job
union all
-- 需求二實現
select d.dname,null,sum(e.sal) sum_sal from dept d,emp e 
where d.deptno=e.deptno group by d.dname
union all
-- 需求三實現
select null,null,sum(e.sal) sum_sal from dept d,emp e
where d.deptno=e.deptno

上面的程式碼通過執行計劃（set autotrace on）可以發現，需要多次訪問EMP、DEPT表的索引，如果
實際運用中表的結構很複雜，將嚴重影響效能。

2.2 ROLLUP 分組
從 Oracle 8i 開始，Oracle 使用 ROLLUP 對 GROUP BY 進行擴充套件，它允許計算標準分組及相應維度
 的小計、合計。
ROLLUP 的語法結構如下：
SELECT ... GROUP BY ROLLUP(grouping_column_reference_liist)
ROLLUP 後面指定的列以逗號分隔，ROLLUP 的計算和其後面指定列的順序有關，因為 ROLLUP 分組
過程具有方向性，先計算標準分組，然後列從右向左遞減計算更高一級的小計，一直到列全部被選完，
最後計算合計。
如果 ROLLUP 中指定 n 列，則整個計算過程中的分組方式有n+1種。

-- 使用ROLLUP 實現 2.1 節的需求
select d.dname,e.job,sum(e.sal) from dept d,emp e 
where d.deptno=e.deptno group by rollup(d.dname,e.job);

ROLLUP 分組具有方向性，從上面的結果可以看出，ROLLUP(d.dname,e.job) 分組的過程是：
a. 標準分組：GROUP BY(d.dname,e.job)，對每個部門每個職位進行分組；
b. 從右到左遞減：GROUP BY(d.dname,null)，其實這個null沒有必要使用，這裡只是方便分析， 
    這個過程是對上個級別分組的小計，也就是對每個 dname 值，計算橫跨所有 job 的小計；
c. 最後合計：相當於 GROUP BY(null,null)。
再例如 ROLLUP(a,b,c)


範例：實現以下需求
a. 計算每個入職時間（年）、部門、職位的標準分組的薪水和
b. 計算每個入職時間（年）、部門的所有職位的薪水小計
c. 計算每個入職時間（年）的所有部門所有職位的薪水小計
d. 最後合計薪水，顯示入職時間（年）、部門名、職位名

with t as (
  select to_char(e.hiredate,'yyyy') hireyear,d.dname,e.job,sum(e.sal) sum_sal from emp e,dept d

  where e.deptno=d.deptno group by rollup(to_char(e.hiredate,'yyyy'),d.dname,e.job))

  select rownum,t.* from t;

接下來分析上述程式碼的結果：


因為 ROLLUP 分組過程具有方向性，所以通過改變 ROLLUP 中列的順序就可以達到改變報表結果和含義的目的，
如現在需要查詢的是 標準分組、計算每個 job 的所有部門的小計、最後合計，則程式碼為：
select e.job,d.dname,sum(e.sal) sum_sal from emp e,dept d
where e.deptno=d.deptno group by rollup(e.job,d.dname);

2.3 部分 ROLLUP 分組
通過將部分列從 ROLLUP 中移出來，放在 GROUP BY 中，這樣合計肯定沒有了，某些小計也沒有了。
需求：不需要每個入職時間（年）的所有部門所有職位的薪水小計，合計也不需要
select to_char(hiredate,'yyyy'),d.dname,e.job,sum(e.sal) sum_sal from emp e,dept d 
where e.deptno=d.deptno group by to_char(hiredate,'yyyy'),d.dname,rollup(e.job);
<=>
select to_char(hiredate,'yyyy'),d.dname,e.job,sum(e.sal) sum_sal from emp e,dept d
where e.deptno=d.deptno group by to_char(hiredate,'yyyy'),d.dname,e.job
union all
select null,null,null,sum(e.sal) sum_sal from emp e,dept d
where e.deptno=d.deptno group by to_char(hiredate,'yyyy'),d.dname;
注：將 hiredate 和 dname 從 ROLLUP 中移出來，就可以將每個入職時間（年）的所有部門所有職位的
薪水小計及合計剔除，最終只查詢標準分組和每個入職時間（年）、部門的所有職位的小計。
           
2.4 ROLLUP 總結
先進行標準分組，在標準分組的基礎上通過將列從右向左移動，然後進行更高一級的小計，最後合計。                     
                                                                                                                

3. CUBE
CUBE 是對不同維度的所有可能分組進行統計，從而生成交叉報表；這種需求比 ROLLUP更加精細，
包含了 ROLLUP 的統計結果，而且還有其他組合分組結果（小計）。
3.1 CUBE 分組
CUBE語法結構：
SELECT ... GROUP BY CUBE(grouping_column_reference_list)
如果 CUBE 中指定 n 列，則整個計算過程中的分組方式有 power(2,n) 種。

將 2.2 節使用 ROLLUP(dname,job) 替換為 CUBE
select d.dname,e.job,sum(e.sal) from dept d,emp e
where d.deptno=e.deptno group by cube(d.dname,e.job);
下圖分析 CUBE(dname,job) 對應分組級別：




3.2 部分 CUBE 分組
和 ROLLUP 一樣，也有部分 CUBE 操作，可以去掉合計及某些不需要的小計，比如上面的 GROUP BY
CUBE(d.dname,e.job) 改為 GROUP BY d.dname CUBE(e.job) 則剔除了合計及GROUP BY job。
select d.dname,e.job,sum(e.sal) sum_sal from dept d,emp e 
where d.deptno=e.deptno group by d.dname,cube(e.job);
<=>
select d.dname,e.job,sum(e.sal) sum_sal from dept d,emp e
where d.deptno=e.deptno group by d.dname,e.job
union all
select null,null,sum(e.sal) sum_sal from dept d,emp e
where d.deptno=e.deptno group by d.dname;

3.3 CUBE總結
先進行合計，然後小計，最後再按標準分組


4. GROUPING SETS 實現小計
前面所說的兩種多維資料統計的方法，即 ROLLUP 和 CUBE，它們的輸出結果是由對應分組的行伴隨
著小計行產生的，它們會產生標準分組、各種小計及總計，但是有時候我們只關心某個單列分組，從而
得到其它維度小計的資訊，這樣就需要使用 GROUPING SETS擴充套件分組，它是Oracle9i提供的。
比如 GROUP BY GROUPING SETS(a,b,c) 相當於 GROUP BY a、GROUP BY b、GROUP BY c 這三
個單列分組，從而得到其他維度的小計資訊。
n列的 GROUPING SETS 的分組總類有 n 個。
4.1 GROUPING SETS 分組
語法結構：
SELECT ... GROUP BY GROUPING SETS(grouping_column_reference_list)
將2.2節中的 ROLLUP 改為 GROUPING SETS
select to_char(e.hiredate,'yyyy') hireyear,d.dname,e.job,sum(e.sal) sum_sal from emp e,dept d
where e.deptno=d.deptno group by grouping sets(to_char(e.hiredate,'yyyy'),d.dname,e.job);
注：GROUPING SETS 的結果是分別按單列分組後 UNION ALL的結果；
        GROUPING SETS 的結果和列的順序沒有關係，而且結果的順序也是無序的。

4.2 部分 GROUPING SETS 分組
select d.dname,to_char(e.hiredate,'yyyy') hireyear,e.job,sum(e.sal) sum_sal from dept d,emp e
where d.deptno=e.deptno group by d.dname,grouping sets(to_char(e.hiredate,'yyyy'),e.job);
<=>
select d.dname,to_char(e.hiredate,'yyyy') hiredate,null job,sum(e.sal) sum_sal from dept d,emp e
where d.deptno=e.deptno group by d.dname,to_char(e.hiredate,'yyyy')
union all
select d.dname,null,e.job,sum(e.sal) sum_sal from dept d,emp e
where d.deptno=e.deptno group by d.dname,e.job;
上述語句統計的是：對於每個部門每個入職時間（年），對所有職位進行小計及
                             對於每個部門每個職位，對每個入職時間（年）進行小計。

4.3 CUBE、GROUPING 作為 GROUPING SETS 的引數
GROUPING SETS 操作能夠接受 ROLLUP 和 CUBE 作為它的引數，GROUPING SETS 操作只對
單列進行分組，而不提供合計的功能，如果需要 GROUPING SETS 提供合計的功能，那麼可以使
用 ROLLUP 或 CUBE 作為 GROUPING SETS 的引數。
改寫前面的 GROUPING SETS(d.dname,e.job)，提供合計功能。
select d.dname,e.job,sum(e.sal) from dept d,emp e
where d.deptno=e.deptno group by grouping sets(rollup(d.dname),rollup(e.job));
<=>
select d.dname,null job,sum(e.sal) sum_sal from dept d,emp e 
where d.deptno=e.deptno group by rollup(d.dname)
union all
select null dname,e.job,sum(e.sal) sum_sal from dept d,emp e
where d.deptno=e.deptno group by rollup(e.job);
注：上述語句會產生兩個合計行，因為 ROLLUP 或 CUBE 作為 GROUPING SETES 的引數，相當
       於對每個 ROLLUP 或 CUBE 操作的 UNION ALL；
       可使用 DISTINCT 剔除重複行；
       ROLLUP 和 CUBE 不能接受 GROUPING SETS 作為引數，ROLLUP 和 CUBE 之間相互作為
       引數也不可以。

4.4 GROUPING SETS 總結
GROUPING SETS 的結果和列的順序沒有關係，而且結果的順序也是無序的。


5. 組合列分組、連線分組、重複列分組
組合列分組、連線分組、重複列分組都是Oracle  9i 中才有的特性。組合列也就是將多個列用括號括
起來，從而將多個列當做整體對待，比如 GROUP BY ROLLUP((a,b),c) 相當於 GROUP BY ROLLUP(x,c)
，其中 x 相當於 (a,b) 這個組合列。組合列一般在 in 條件中比較常見，比如：
-- where in 中使用組合列
select empno,ename,job from emp where (empno,ename) in ((7369,'SMITH'),(7499,'ALLEN'));
下圖是普通列 ROLLUP 和組合列 ROLLUP 的對比（CUBE、GROUPING SETS類似）


上圖的組合列分組達到了剔除某些小計的功能，且保證了最終結果又合計行。

連線分組允許在 GROUP BY 之後出現多個 ROLLUP、CUBE、GROUPING SETS 操作，這樣分組級別
更多，報表更加精細。


實際上不管是同型別的連線分組還是不同型別的連線分組之間，最後的分組級別種類都是每個擴充套件
分組級別種類的乘積，分組級別是笛卡爾積。比如同型別連線分組 ROLLUP(a,b)，ROLLUP(c) 最終
結果有 3*2=6 種分組級別，不同型別連線分組 ROLLUP(a,b)，GROUPING SETS(c) 有3*1=3 種分
組級別。

重複列分組就是 GROUP BY 中允許重複列，比如在 ROLLUP 中使用複雜的複合列分組可能會用到，
比如 GROUP BY ROLLUP(a,(a,b))、GROUP BY a,ROLLUP(a,b) 都屬於重複列。

5.1 組合列分組
組合列分組有過濾某些小計或計算一些額外的小計等功能。
前面的部分 ROLLUP、部分CUBE 都沒有合計，使用組合列可以實現部分 ROLLUP、部分 CUBE的
功能，還能有合計。
需求：
a. 對部門、入職時間（年）、職位進行標準分組
b. 對每個部門計算橫跨入職時間（年）和職位的小計
c. 最後合計
select d.dname,to_char(e.hiredate,'yyyy') hireyear,e.job,sum(e.sal) sum_sal from dept d,emp e
where d.deptno=e.deptno group by rollup(d.dname,(to_char(e.hiredate,'yyyy'),e.job));
CUBE 和 ROLLUP 操作都可以用組合列分組轉為對應的 GROUPING SETS， 例如，
ROLLUP(a,b,c) 轉為等價的 GROUPING SETS 是 GROUPING SETS((a,b,c),(a,b),(a),NULL);
CUBE(a,b,c) 轉為等價的 GROUPING SETS 是 GROUPING SETS((a,b,c),(a,b),(a,c),(b,c),(a),(b),(c),NULL);

5.2 連線分組
連線分組是Oracle 9i 才有的功能，它允許 GROUP BY後面有多個 ROLLUP、CUBE、GROUPING SETS，
連線分組的分組級別是由每個 ROLLUP、CUBE、GROUPING SETS 分組組成的笛卡爾積。
比如 ROLLUP(a,b),ROLLUP(c,d,e) 共有分組統計級別為 3*4=12 種。
select d.dname,e.job,to_char(e.hiredate,'yyyy') hireyear,sum(sal) sum_sal from dept d,emp e
where d.deptno=e.deptno group by rollup(d.dname,e.job),rollup(to_char(e.hiredate,'yyyy'));
GROUP BY ROLLUP(d.dname,e.job),ROLLUP(to_char(e.hiredate),'yyyy') 實現了 6 種分組結果，
相當於兩個 ROLLUP 的笛卡爾積，如下表：


CUBE、GROUPING SETS 都類似，利用連線分組，CUBE 可以用 ROLLUP轉換：
a. 當只有一列的時候，比如 ROLLUP(a) 與 CUBE(a) 是一樣的，都有兩種統計方式；
b. 當有 n 列的時候，比如 CUBE(a,b,c) 可以轉為 ROLLUP(a),ROLLUP(b),ROLLUP(c) 的連線分組表示，
也就是有 n 列的 CUBE 轉為 ROLLUP 則需要拆開，轉為單列 ROLLUP的連線分組即可。
select d.dname,e.job,to_char(e.hiredate,'yyyy') hireyear,sum(e.sal) sum_sal from dept d,emp  e
where d.deptno=e.deptno group by rollup(d.dname),rollup(e.job),rollup(to_char(e.hiredate,'yyyy'));
<=> group by cube(d.dname,e.job,to_char(hiredate,'yyyy'));
注：連線分組一般是同型別的連線分組，不同型別的連線分組比如 GROUP BY ROLLUP...CUBE... 等是不常用的，
        除非有複雜需求。

5.3 重複列分組
重複列分組也是Oracle 9i 才有的，也就是 GROUP BY 後面允許重複列。
select d.dname,e.job,sum(e.sal) sum_sal from dept d,emp e
where d.deptno=e.deptno group by d.dname,rollup(d.dname,e.job);
<=>
select d.dname,e.job,sum(e.sal) sum_sal from dept d,emp e 
where d.deptno=e.deptno group by d.dname,e.job
union all
select null,null,sum(e.sal) sum_sal from dept d,emp e
where d.deptno=e.deptno group by d.dname
union all
select null,null,sum(e.sal) sum_sal from dept d,emp e
where d.deptno=e.deptno group by d.dname;

5.4 組合列分組、連線分組、重複列分組總結
a. 組合列主要實現剔除某些不必要的小計保留合計；
b. 連線分組按每個擴充套件分組的分組級別的笛卡爾積形式進行操作，分組型別更多更細，
    比如 ROLLUP 連線分組就實現了類似 CUBE 的功能。


6. 3個擴充套件分組函式：GROUPING、GROUPING_ID、GROUP_ID 
主要內容有：
a. 使用 GROUPING 函式製作有意義的報表，以及對結果進行過濾；
b. 使用 GROUPING_ID 函式對結果進行過濾及排序；
c. 使用 GROUP_ID 函式剔除重複行。

6.1 GROUPING 函式
對擴充套件 GROUP BY 子句來說，比如 ROLLUP、CUBE 會生成標準分組、一系列小計及合計，這樣查詢結果中，
有些行的列值就會存在 NULL。NULL 在擴充套件 GROUP BY 中有特殊的意義，結果行中的列值為 NULL，一般
就意味著是此列的小計或合計，但是 NULL 也有可能是原始資料存在的 NULL(如 emp.mgr=NULL)，所以引入
了 GROUPING 函式專門處理擴充套件GROUP BY 分組結果中 NULL 的問題：
a. 它只接受一個引數，此引數來自 ROLLUP、CUBE、GROUPING SETS 中的列；
b. GROUPING 函式對於是小計或合計的列返回 1，否則返回 0。如果小計或合計列的值是 NULL，但是原始
     資料可能也存在 NULL，則常使用 GROUPING 函式來區分最終結果行中的 NULL 是原始資料中存在的，
     還是小計或合計列的值，常和 DECODE 函式配合使用。
6.1.1 用於格式化報表，生成有意義的報表
select d.dname,e.mgr,sum(e.sal) from dept d,emp e 
where d.deptno=e.deptno group by rollup(d.dname,e.mgr);   <== 對於每個dname，計算橫跨 mgr 列的小計
上述結果中第 9 行和第 11 行的 mgr 列都為 NULL，無法區分哪個列是小計，此時就可以使用 GROUPING
函式進行區分。
select d.dname,e.mgr,sum(e.sal),grouping(mgr) from dept d,emp e 
where d.deptno=e.deptno group by rollup(d.dname,e.mgr);  
上述結果中第 8 行的 GROUPING(mgr)=0，第 11 行為 1，所有第 8 行的 mgr 列不是小計列，11 行才是。
下面使用DECODE + GROUPING 來製作有意義的報表：
select decode(grouping(d.dname),1,'TOTAL_DEPT',d.dname) dname,
            decode(grouping(e.mgr),1,'SUBTOTAL_DEPT',nvl(to_char(e.mgr),'BOSS')) mgr,
            sum(e.sal) sum_sal from dept d,emp e
where d.deptno=e.deptno group by rollup(d.dname,e.mgr);
如果要將所有的 mgr 列小計一起放在後面顯示，位置在合計之間，這種需求如何解決?（詳見GROUPING_ID）
6.1.2 過濾某些分組結果
一般使用 GROUPING_ID 代替
需求：對 group by rollup(d.dname,e.mgr,e.job) 的結果保留合計和標準分組
select d.dname,e.mgr,e.job,sum(e.sal) sum_sal  from dept d,emp e 
where d.deptno=e.deptno group by rollup(d.dname,e.mgr,e.job)
having grouping(d.dname)=1 or grouping(e.job)=0;

6.2 GROUPING_ID 函式
GROUPING 函式用來生成有意義的報表及過濾一些分組級別；
GROUPING_ID 函式主要用來過濾分組級別和排序結果（顯示排序）。
不管 ROLLUP、CUBE、GROUPING SETS 的結果是否有預設順序，都是不可靠的。
GROUPING_ID 函式可以接受對個引數，這些引數來自於 ROLLUP、CUBE、GROUPING SETS中的
列（引數來源和 GROUPING 函式一致），按列從左到右順序計算，如果此列是分組列則為 0 ，如果是
對此列的小計或合計則為 1，然後按列順序將計算結果組成二進位制序列（位向量），最後將位向量轉為
十進位制數。如CUBE(a,b)，則GROUPING_ID(a,b) 的結果如下圖所示：


GROUPING_ID的好處就是可以對多列進行計算，從而得到此列的分組級別。
從上圖可以看出，GROUPING_ID(column_list) 中的 colum_list 和擴充套件分組保持一致，那麼 GROUPING_ID
值得種類必須與對應擴充套件分組數目保持一致：
比如 CUBE(a,b,c) 的GROUPING_ID(a,b,c)的值有8 種，ROLL(a,b,c)的GROUPING_ID(a,b,c) 的值有4 種。
GROUPING_ID 的取值範圍都一樣，和列的數目有關，比如有 n 列，則 GROUPING_ID的取值範圍在[ 0-2^n-1 ]
6.2.1 GROUPING_ID 函式過濾某些分組結果
需求：改寫6.1 節 GROUPING 過濾結果的例子，用 GROUPING_ID 實現同等功能。
先分析對於 ROLLUP(d.dname,e.mgr,e.job) 使用 GROUPING_ID 函式的結果，注意的是，一般使用
GROUPING_ID函式，列的順序要與 ROLLUP、CUBE、GROUPING SETS 中的順序保持一致。


從表中就可以清楚的看出，實現這個需求只要 GROUPING_ID(d.dname,e.mgr,e.job) 取 0 和 7 即可。
select d.dname,e.mgr,e.job,sum(e.sal) sum_sal from dept d,emp e 
where d.deptno=e.deptno group by rollup(d.dname,e.mgr,e.job)
having grouping_id(d.dname,e.mgr,e.job) in (0,7)
6.2.2 使用 GEOUPING_ID 對結果進行排序
對擴充套件分組，需要手動進行有意義的排序。
需求：將所有的 mgr 列小計一起放在後面顯示，位置在合計之前。
select decode(grouping_id(d.dname),1,'TOTAL_EPT',d.dname) dname,
            decode(grouping_id(e.mgr),1,'SUBTOAL_DEPT',nvl(to_char(e.mgr),'BOSS')) mgr,
            sum(e.sal) sum_sal
from dept d,emp e where d.deptno=e.deptno
group by rollup(d.dname,e.mgr) order by grouping_id(d.dname,e.mgr);
注：排序還有很多靈活性，比如改變 GROUPING_ID 中列的屬順序，就可以達到不同的排序效果：
        order by grouping_id(d.dname,e.job,e.mgr);
        order by grouping_id(d.dname,e.mgr,e.job);
注：GROUPING  函式也能用來排序，甚至可以將 GROUPING 函式與 GROUPING_ID 函式 排序結合起來。
select d.dname,to_char(e.hiredate,'yyyy') hireyear,e.job,sum(e.sal) sum_sal,
grouping(d.dname)+grouping(to_char(e.hiredate,'yyyy'))+grouping(e.job) ord1,
grouping_id(d.dname,to_char(e.hiredate,'yyyy'),e.job) ord2
from dept d,emp e where d.deptno=e.deptno
group by cube(d.dname,to_char(e.hiredate,'yyyy'),e.job)
order by grouping(d.dname)+grouping(to_char(e.hiredate,'yyyy'))+grouping(e.job) desc,
grouping_id(d.dname,to_char(e.hiredate,'yyyy'),e.job);

6.3 GROUP_ID 函式
GROUP_ID 函式無引數，可以區分重複分組結果，第一次出現為 0，以後每次出現增 1。
需要注意的是，DISTINCT是剔除重複行，而不是按重複分組級別剔除，GROUP_ID 函式則是
按重複分組級別來剔除。
-- 為了獲得非重複統計，只需要 GROUP_ID()=0 即可
select d.dname,e.job,sum(e.sal) sum_sal from dept d,emp e
where d.deptno=e.deptno group by grouping sets(rollup(d.dname),rollup(e.job))
having group_id()=0;

6.4 擴充套件 GROUP BY 函式總結
a. GROUPING 函式用於製作有意義的報表，以及對結果進行過濾(對於小計或合計的列返回1，否則返回0)；
b. GROUPING_ID 函式對結果行進行過濾(此列為分組列則為0，此列是小計或合計則為1)和排序；
c. GROUP_ID 函式無引數，用於剔除重複行(按重複分組級別來剔除，第一次出現為0，以後出現每次增1)；
d. GROUPING 函式和 GROUPING_ID 函式是沒有組合列的，也就是裡面不能加括號，比如
    GROUPING_ID((a,b),c) 是錯的，而且它們要來自於分組列。


7. 擴充套件分組綜合例項 
7.1 需求：按照規則生成報表，並且排序
select decode(grouping_id(order_date,order_no,order_book),6,'SUM('||order_book||')',
                     7,'SUM',order_date)/*order_date1*/,
          order_no,
          decode(grouping_id(order_date,order_no,order_book),6,NULL,order_book) /*order_book1*/,
          sum(order_fee) order_fee,
          sum(order_num) order_num
from t_order
group by rollup(order_book,(order_date,order_no))
order by order_book,order_date;
7.2 實現類似 SQL*PLUS 的 BREAK 報表功能
BREAK 的功能就是能夠替換重複出現的單元格值為空格，即在做報表的時候，對同一個大類，
只需要第一次出現的時候單元格值保留即可。
需求：將重複的部門名替換為空格
SCOTT>BREAK ON dname
SCOTT>select d.dname,e.job,sum(e.sal) sum_sal from dept d,emp e
                where d.deptno=e.deptno group by rollup(d.dname,e.job)
                order by 1,2;
這樣報表的可讀性就更強了，但這是利用 SQL*PLUS 中的BREAK指令實現的。
7.3 利用分析函式實現類似 BREAK 的功能
select decode(lag(d.dname,1)over(partition by d.dname order by d.dname,e.job),
                           d.dname,NULL,d.dname) dname,
            e.job,sum(e.sal) sum_sal from dept d,emp e
where d.deptno=e.deptno group by rollup(d.dname,e.job)
order by d.dname,e.job;


8. 分組求和後的行列轉換例項
需求：生成所有員工薪水等級分佈情況的報表，格式如下

分析：
a. 需要求出每個 deptno，橫跨所有 grade 列的小計；
b. 需要求出每個 grade，橫跨所有 deptno 列的小計；
所以可以選擇 CUBE(deptno,grade)
c. 使用 grouping_id(deptno,grade) 可以判斷哪些列是小計、合計，及分組列；
d. 使用 decode 進行行轉列。
with t as (
select deptno,grade,count(*) cnt,grouping_id(deptno,grade) gg_id
from emp,salgrade where sal between losal and hisal 
group by cube(deptno,grade) order by deptno,grade),
u as (select cast(decode(gg_id,2,'TOTAL',3,'TOTAL',deptno) as varchar2(10)) deptno,
decode(gg_id,1,cnt,3,cnt) subtotal,decode(grade,1,cnt) grade1,decode(grade,2,cnt) grade2,
decode(grade,3,cnt) grade3,decode(grade,4,cnt) grade4,decode(grade,5,cnt) grade5 from t)
select deptno,max(subtotal) subtotal,max(grade1) grade1,max(grade2) grade2, max(grade3) grade3,
max(grade4) grade4, max(grade5) grade5 from u group by deptno order by deptno;







說明：文中部分內容是從《劍破冰山 — Oracle開發藝術》中總結出來的，需要詳細瞭解的讀者可以
            去參考這本書，當然，如果文章中有不對的地方，還望指出來，我好及時改正！
謝謝！

相關文章