Oracle的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()
grouping_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;
其它：
-------------------------------------------------------------
select ... from 表 group by rollup(.....) having grouping_id(....)<=1
總計是grouping_id=3
小計grouping_id=1
記錄是grouping_id=0
grouping_id中括號裡面的字段和rollup括號裡面的字段一致就行
追問
大俠，你好請問grouping_id是腫麼來的，
回答
oracle的函數，針對rollup和cube的
追問
大俠，你好，灰常感謝，我的問題解決了，只是還要向您討教一下，那個grouping_id的數字是怎麼確定的，假如我rollup(...)裡面有兩個列，那麼grouping_id(..)的最大值是多少呢，假如是三列呢。。。望請賜教。
回答
向量值，可以認為是二進值的數，例：grouping_id(a,b,c)，a為空就是0非空為1，b和c也一樣，結果會得到一個三位數，用二進制轉換成十進制就是了，例：a，b，c全是非空，即111就是7，三列就是7，如果是兩列自然是11就是3了
案例：
SQL> create table t as select * from dba_indexes;
表已創建。
SQL> select index_type, status, count(*) from t group by index_type, status;
------------------------------------------------------------------------------
INDEX_TYPE
STATUS
COUNT(*)
LOB
N/A
1
IOT - TOP
VALID
72
CLUSTER
VALID
10
DOMAIN
VALID
2
NORMAL
VALID
3439
LOB
VALID
231
NORMAL
N/A
79
FUNCTION-BASED NORMAL
VALID
30
------------------------------------------------------------------------------
已選擇8行
下面來看看ROLLUP和CUBE語句的執行結果。
SQL> select index_type, status, count(*) from t group by rollup(index_type, status);
------------------------------------------------------------------------------
INDEX_TYPE
STATUS
COUNT(*)
LOB
N/A
1
LOB
VALID
231
LOB
null
232
DOMAIN
VALID
2
DOMAIN
null
2
NORMAL
N/A
79
NORMAL
VALID
3439
NORMAL
null
3518
CLUSTER
VALID
10
CLUSTER
null
10
IOT - TOP
VALID
72
IOT - TOP
null
72
FUNCTION-BASED NORMAL
VALID
30
FUNCTION-BASED NORMAL
null
30
null
null
3864
------------------------------------------------------------------------------
已選擇15行。
SQL> select index_type, status, count(*) from t group by cube(index_type, status);
------------------------------------------------------------------------------
INDEX_TYPE
STATUS
COUNT(*)
null
null
3864
null
N/A
80
null
VALID
3784
LOB
null
232
LOB
N/A
1
LOB
VALID
231
DOMAIN
null
2
DOMAIN
VALID
2
NORMAL
null
3518
NORMAL
N/A
79
NORMAL
VALID
3439
CLUSTER
null
10
CLUSTER
VALID
10
IOT - TOP
null
72
IOT - TOP
VALID
72
FUNCTION-BASED NORMAL
null
30
FUNCTION-BASED NORMAL
VALID
30
------------------------------------------------------------------------------
已選擇17行。
查詢結果不是很一目瞭然，下面通過Oracle提供的函數GROUPING來整理一下查詢結果。
SQL> select grouping(index_type), grouping(status), index_type, status, count(*)
from t group by rollup(index_type, status) order by 1, 2;
------------------------------------------------------------------------------
GROUPING(INDEX_TYPE)
GROUPING(STATUS)
INDEX_TYPE
STATUS
COUNT(*)
0
0
DOMAIN
VALID
2
0
0
IOT - TOP
VALID
72
0
0
LOB
N/A
1
0
0
CLUSTER
VALID
10
0
0
LOB
VALID
231
0
0
NORMAL
N/A
79
0
0
NORMAL
VALID
3439
0
0
FUNCTION-BASED NORMAL
VALID
30
0
1
IOT - TOP
72
0
1
CLUSTER
10
0
1
DOMAIN
2
0
1
LOB
232
0
1
NORMAL
3518
0
1
FUNCTION-BASED NORMAL
30
1
1
3864
------------------------------------------------------------------------------
已選擇15行。
這個查詢結果就直觀多了，和不帶ROLLUP語句的GROUP BY相比，ROLLUP增加了對INDEX_TYPE的GROUP BY統計和對所有記錄的GROUP BY統計。
也就是說，如果是ROLLUP(A, B, C)的話，首先會對(A、B、C)進行GROUP BY，然後對(A、B)進行GROUP BY，然後是(A)進行GROUP BY，最後對全表進行GROUP BY操作。
下面看看CUBE語句。
SQL>
select grouping(index_type) , grouping(status), index_type, status, count(*) from t group by cube(index_type, status) order by 1, 2;G_IND
G_ST
INDEX_TYPE
STATUS
COUNT(*)
0
0
NORMAL
N/A
79
0
0
FUNCTION-BASED NORMAL
VALID
30
0
0
NORMAL
VALID
3439
0
0
IOT - TOP
VALID
72
0
0
LOB
N/A
1
0
0
LOB
VALID
231
0
0
CLUSTER
VALID
10
0
0
DOMAIN
VALID
2
0
1
DOMAIN
2
0
1
LOB
232
0
1
NORMAL
3518
0
1
FUNCTION-BASED NORMAL
30
0
1
IOT - TOP
72
0
1
CLUSTER
10
1
0
N/A
80
1
0
VALID
3784
1
1
3864
已選擇17行。
和ROLLUP相比，CUBE又增加了對STATUS列的GROUP BY統計。
如果是GROUP BY CUBE(A, B, C)，則首先會對(A、B、C)進行GROUP BY，然後依次是(A、B)，(A、C)，(A)，(B、C)，(B)，(C)，最後對全表進行GROUP BY操作。
除了使用GROUPING函數，還可以使用GROUPING_ID來標識GROUP BY結果。
SQL> select grouping_id(index_type, status) g_ind, index_type, status, count(*)
from t group by rollup(index_type, status) order by 1;
G_IND
INDEX_TYPE
STATUS
COUNT(*)
0
DOMAIN
VALID
2
0
IOT - TOP
VALID
72
0
LOB
N/A
1
0
CLUSTER
VALID
10
0
LOB
VALID
231
0
NORMAL
N/A
79
0
NORMAL
VALID
3439
0
FUNCTION-BASED NORMAL
VALID
30
1
IOT - TOP
72
1
CLUSTER
10
1
DOMAIN
2
1
LOB
232
1
NORMAL
3518
1
FUNCTION-BASED NORMAL
30
3
3864
已選擇15行。
SQL> select grouping_id(index_type, status) g_ind, index_type, status, count(*) from t group by cube(index_type, status) order by 1;
G_IND
INDEX_TYPE
STATUS
COUNT(*)
0
NORMAL
N/A
79
0
FUNCTION-BASED NORMAL
VALID
30
0
NORMAL
VALID
3439
0
IOT - TOP
VALID
72
0
LOB
N/A
1
0
LOB
VALID
231
0
CLUSTER
VALID
10
0
DOMAIN
VALID
2
1
DOMAIN
2
1
LOB
232
1
NORMAL
3518
1
FUNCTION-BASED NORMAL
30
1
IOT - TOP
72
1
CLUSTER
10
2
N/A
80
2
VALID
3784
3
3864
已選擇17行。
---------------------------------------------------------------
grouping_id()可以美化效果：
select
DECODE(GROUPING_ID(C1), 1, '合計', C1) D1,
DECODE(GROUPING_ID(C1, C2), 1, '小計', C2) D2,
DECODE(GROUPING_ID(C1, C2, C1 + C2), 1, '小計', C1 + C2) D3,
count(*),
GROUPING_ID(C1, C2, C1 + C2, C1 + 1, C2 + 1),
GROUPING_ID(C1)
from T2
group by rollup(C1, C2, C1 + C2, C1 + 1, C2 + 1);
=======================================================
1.報表合計專用的Rollup函數
銷售報表
廣州 1月 2000元
廣州 2月 2500元
廣州小計 4500元
深圳 1月 1000元
深圳 2月 2000元
深圳小計 3000元
所有地區 7500元
以往的查詢SQL:
Select area,month,sum(money) from SaleOrder group by area,month
然後廣州，深圳的合計和所有地區合計都需要在程序裡自行累計
1.其實可以使用如下SQL:
Select area,month,sum(total_sale) from SaleOrder group by rollup(area,month)
就能產生和報表一模一樣的紀錄
2.如果year不想累加，可以寫成
Select year,month,area,sum(total_sale) from SaleOrder group by year, rollup(month,area)
另外Oracle 9i還支持如下語法:
Select year,month,area,sum(total_sale) from SaleOrder group by rollup((year,month),area)
3.如果使用Cube(area,month)而不是RollUp(area,month)，除了獲得每個地區的合計之外，還將獲得每個月份的合計，在報表最後顯示。
4.Grouping讓合計列更好讀
RollUp在顯示廣州合計時，月份列為NULL，但更好的做法應該是顯示為"所有月份"
Grouping就是用來判斷當前Column是否是一個合計列，1為yes，然後用Decode把它轉為"所有月份"
Select Decode(Grouping(area),1,'所有地區',area) area, Decode(Grouping(month),1,'所有月份',month), sum(money) From SaleOrder Group by RollUp(area,month);
-----------------------------------------------------------------------------------------------
2.對多級層次查詢的start with.....connect by
比如人員組織,產品類別,Oracle提供了很經典的方法
SELECT LEVEL, name, emp_id,manager_emp_id FROM employee START WITH manager_emp_id is null CONNECT BY PRIOR emp_id = manager_emp_id;
上面的語句demo了全部的應用,start with指明從哪裡開始遍歷樹,如果從根開始,那麼它的manager應該是Null,如果從某個職員開始,可以寫成emp_id='11'
CONNECT BY 就是指明父子關係,注意PRIOR位置 。另外還有一個LEVEL列,顯示節點的層次
3.更多報表/分析決策功能
3.1 分析功能的基本結構
分析功能() over( partion子句,order by子句,窗口子句)
概念上很難講清楚,還是用例子說話比較好.
3.2 Row_Number 和 Rank, DENSE_Rank
用於選出Top 3 sales這樣的報表
當兩個業務員可能有相同業績時,就要使用Rank和Dense_Rank
比如
金額 RowNum Rank Dense_Rank
張三 4000元 1 1 1
李四 3000元 2 2 2
錢五 2000元 3 3 3
孫六 2000元 4 3 3
丁七 1000元 5 5 4
這時,應該把並列第三的錢五和孫六都選進去,所以用Ranking功能比RowNumber保險.至於Desnse還是Ranking就看具體情況了。
SELECT salesperson_id, SUM(tot_sales) sp_sales, RANK( ) OVER (ORDER BY SUM(tot_sales) DESC) sales_rank FROM orders GROUP BY salesperson_id
3.3 NTILE 把紀錄平分成甲乙丙丁四等
比如我想取得前25%的紀錄,或者把25%的紀錄當作同一個level平等對待,把另25%當作另一個Level平等對待
SELECT cust_nbr, SUM(tot_sales) cust_sales, NTILE(4) OVER (ORDER BY SUM(tot_sales) DESC) sales_quartile FROM orders GROUP BY cust_nbr ORDER BY 3,2 DESC;
NTITLE(4)把紀錄以 SUM(tot_sales)排序分成4份.
3.4 輔助分析列和Windows Function
報表除了基本事實數據外,總希望旁邊多些全年總銷量,到目前為止的累計銷量,前後三個月的平均銷量這樣的列來參考.
這種前後三個月的平均和到目前為止的累計銷量就叫windows function, 見下例
SELECT month, SUM(tot_sales) monthly_sales, SUM(SUM(tot_sales)) OVER (ORDER BY month ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) max_preceeding FROM orders GROUP BY month ORDER BY month;
SELECT month, SUM(tot_sales) monthly_sales, AVG(SUM(tot_sales)) OVER (ORDER BY month ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) rolling_avg FROM orders GROUP BY month ORDER BY month;
Windows Function的關鍵就是Windows子句的幾個取值
1 PRECEDING 之前的一條記錄
1 FOLLOWING 之後的一條記錄
UNBOUNDED PRECEDING 之前的所有記錄
CURRENT ROW 當前紀錄
4.SubQuery總結
SubQuery天天用了,理論上總結一下.SubQuery 分三種
1.Noncorrelated 子查詢 最普通的樣式.
2.Correlated Subqueries 把父查詢的列拉到子查詢裡面去,頭一回cyt教我的時候理解了半天.
3.Inline View 也被當成最普通的樣式用了.
然後Noncorrelated 子查詢又有三種情況
1.返回一行一列 where price < (select max(price) from goods )
2.返回多行一列 where price>= ALL (select price from goods where type=2)
or where NOT price< ANY(select price from goods where type=2)
最常用的IN其實就是=ANY()
3.返回多行多列 一次返回多列當然就節省了查詢時間
UPDATE monthly_orders SET (tot_orders, max_order_amt) = (SELECT COUNT(*), MAX(sale_price) FROM cust_order) DELETE FROM line_item WHERE (order_nbr, part_nbr) IN (SELECT order_nbr, part_nbr FROM cust_order c)
閱讀更多 識上樹既豬 的文章
