.Net程式員學用Oracle系列(21)：分組查詢(GROUP BY)

1、 " GROUP BY 標準分組 " 1.1、 "GROUP BY 概述" 1.2、 "WHERE 和 HAVING 的區別？" 2、 " GROUP BY 擴展分組 " 2.1、 "ROLLUP 分組" 2.2、 "CUBE 分組" 2.3、 "GROUPING SETS 分組" 2.4、 " ...

1、GROUP BY 標準分組
- 1.1、GROUP BY 概述
- 1.2、WHERE 和 HAVING 的區別？
2、GROUP BY 擴展分組
- 2.1、ROLLUP 分組
- 2.2、CUBE 分組
- 2.3、GROUPING SETS 分組
- 2.4、複雜分組（組合列分組、重覆列分組、連接分組）
3、GROUP BY 擴展函數
- 3.1、GROUPING 函數
- 3.2、GROUPING_ID 函數
- 3.3、GROUP_ID 函數
4、總結

1、GROUP BY 標準分組

為了便於講述，我將把簡單 GROUP BY 子句的分組稱之為標準分組，把 GROUP BY 子句中出現的列（或表達式）稱之為分組列。

1.1、GROUP BY 概述

在分組查詢中，GROUP BY 子句的作用就是按指定的（一或多個）列或表達式的值將選定行集進行分組，並針對每一組返回一行從組中收集到的數據。基本語法：

SELECT expression1, expression2, ... expression_n, 
       aggregate_function (aggregate_expression)
FROM tables
[WHERE conditions]
GROUP BY expression1, expression2, ... expression_n
[HAVING having_condition];

簡單示例：

SELECT t.dept_code,
  MAX(t.post_salary) max_salary,  -- 部門最高崗位工資
  MIN(t.post_salary) min_salary,  -- 部門最低崗位工資
  AVG(t.post_salary) avg_salary,  -- 部門平均崗位工資
  SUM(t.post_salary) sum_salary,  -- 部門崗位工資之和
  COUNT(t.post_salary) cnt_salary -- 部門工資份數
FROM demo.t_staff t
GROUP BY t.dept_code
HAVING AVG(t.post_salary)>3500
ORDER BY t.dept_code;

註意事項：

1、SELECT 子句中只能出現分組列或聚合函數或常量。
2、HAVING 子句中只能出現分組列或聚合函數或常量。
3、GROUP BY 子句中只能出現標量基元類型（如 VARCHAR2、NUMBER、DATE 等）的列或常量，不能出現 BLOB、CLOB 等類型的列。
4、實際上分組列可以不出現在 SELECT 子句中，但這可能會讓查詢結果含義不明確。
5、在 SELECT、HAVING、GROUP BY 子句中還可以出現某些特殊的函數，如 SYSDATE（感覺沒啥意義）。

1.2、WHERE 和 HAVING 的區別？

我首先要說明的是：“WHERE 和 HAVING 的區別？”這絕對是一個有問題的問題！因為它兩根本就沒有可比性，實際上問這種問題的人，往往 SQL 基礎也不夠扎實。

在包含 GROUP BY 子句的查詢語句中：WHERE 子句的作用是在對查詢結果分組前過濾行數據，將不符合條件的行去掉；而 HAVING 子句的作用是在對查詢結果分組後過濾組數據，將不符合條件的組去掉。換句話說，因為聚合函數的作用是提供有關組的信息，還沒分組之前當然是無法提供組的信息的，也就是說 WHERE 子句中無法使用聚合函數，這也正是會出現 HAVING 子句的原因。有了 HAVING 子句，我們就可以很方便的在分組之後對組的數據進行過濾了。一般來說，能用 WHERE 的過濾的就不應該用 HAVING 過濾！

2、GROUP BY 擴展分組

在實際業務開發中，只有標準分組可能還不夠，往往還需要更多維度的小計、合計。針對這類需求，Oracle 提供了豐富的擴展分組功能；儘管用 UNION ALL 一般也能實現類似效果，但不夠靈活且性能比較低。

2.1、ROLLUP 分組

ROLLUP 是 GROUP BY 子句的一種擴展，它允許計算標準分組及部分維度的小計、合計。語法：

GROUP BY ROLLUP(grouping_column_reference_list)

ROLLUP 的計算結果與括弧中指定列的順序有關，因為 ROLLUP 的分組過程具有方向性，先計算標準分組，然後從右到左遞減計算更高一級的小計，直到所有列被計算完，最後計算合計。當 ROLLUP 中指定 n 列時，整個計算過程中分組方式有 n+1 種。如GROUP BY ROLLUP(A,B)的分組過程相當於是：第 1 步按 GROUP BY(A,B) 分組求小計，第 2 步按 GROUP BY(A,NULL) 分組求小計，第 3 步按 GROUP BY(NULL,NULL) 分組求合計。

示例 1：

SELECT t.dept_code,SUM(t.post_salary) sum_salary,MAX(t.post_salary) max_salary
FROM demo.t_staff t
WHERE t.dept_code IN('010102','010103')
GROUP BY ROLLUP(t.dept_code);

結果：

DEPT_CODE                                          SUM_SALARY MAX_SALARY
-------------------------------------------------- ---------- ----------
010102                                                  13500       7500
010103                                                   7850       5050
                                                        21350       7500

示例 2：

SELECT t.dept_code,t.post_code,SUM(t.post_salary) sum_salary,MAX(t.post_salary) max_salary
FROM demo.t_staff t
WHERE t.dept_code IN('010102','010103')
GROUP BY ROLLUP(t.dept_code,t.post_code);

結果：

DEPT_CODE                                      POST_CODE                                    SUM_SALARY MAX_SALARY
---------------------------------------------- -------------------------------------------- ---------- ----------
010102                                         P40                                                7500       7500
010102                                         P50                                                6000       6000
010102                                                                                           13500       7500
010103                                         P40                                                5050       5050
010103                                         P50                                                2800       2800
010103                                                                                            7850       5050
                                                                                                 21350       7500

示例 3，部分 ROLLUP 分組（不需要某些小計、合計時可用該種寫法）：

SELECT t.dept_code,t.post_code,SUM(t.post_salary) sum_salary,MAX(t.post_salary) max_salary
FROM demo.t_staff t
WHERE t.dept_code IN('010102','010103')
GROUP BY t.dept_code,ROLLUP(t.post_code);

結果：

DEPT_CODE                                     POST_CODE                                    SUM_SALARY MAX_SALARY
--------------------------------------------- -------------------------------------------- ---------- ----------
010102                                        P40                                                7500       7500
010102                                        P50                                                6000       6000
010102                                                                                          13500       7500
010103                                        P40                                                5050       5050
010103                                        P50                                                2800       2800
010103                                                                                           7850       5050

2.2、CUBE 分組

CUBE 是 GROUP BY 子句的一種擴展，它允許計算標準分組及所有維度的小計、合計。語法：

GROUP BY CUBE(grouping_column_reference_list)

CUBE 會對所有可能的分組進行統計，從而生成交叉報表。CUBE 比 ROLLUP 的分組更多，且包含了 ROLLUP 的統計結果，且計算結果與分組列的順序無關，但如果列順序不同，預設的結果排序會有不同。當 CUBE 中指定 n 列時，整個計算過程中分組方式有 2 的 n 次方種。如GROUP BY CUBE(A,B)相當於：按 GROUP BY(A,B) 分組的小計，加按 GROUP BY(A,NULL) 分組的小計，加按 GROUP BY(NULL,B) 分組的小計，加按 GROUP BY(NULL,NULL) 分組的合計。

示例 1：

SELECT t.dept_code,SUM(t.post_salary) sum_salary,MAX(t.post_salary) max_salary
FROM demo.t_staff t
WHERE t.dept_code IN('010102','010103')
GROUP BY CUBE(t.dept_code);

結果：

DEPT_CODE                                          SUM_SALARY MAX_SALARY
-------------------------------------------------- ---------- ----------
                                                        21350       7500
010102                                                  13500       7500
010103                                                   7850       5050

示例 2：

SELECT t.dept_code,t.post_code,SUM(t.post_salary) sum_salary,MAX(t.post_salary) max_salary
FROM demo.t_staff t
WHERE t.dept_code IN('010102','010103')
GROUP BY CUBE(t.dept_code,t.post_code);

結果：

DEPT_CODE                                     POST_CODE                                  SUM_SALARY MAX_SALARY
--------------------------------------------- ------------------------------------------ ---------- ----------
                                                                                              21350       7500
                                              P40                                             12550       7500
                                              P50                                              8800       6000
010102                                                                                        13500       7500
010102                                        P40                                              7500       7500
010102                                        P50                                              6000       6000
010103                                                                                         7850       5050
010103                                        P40                                              5050       5050
010103                                        P50                                              2800       2800

示例 3，部分 CUBE 分組（不需要某些小計、合計時可用該種寫法）：

SELECT t.dept_code,t.post_code,SUM(t.post_salary) sum_salary,MAX(t.post_salary) max_salary
FROM demo.t_staff t
WHERE t.dept_code IN('010102','010103')
GROUP BY t.dept_code,CUBE(t.post_code);

結果：

DEPT_CODE                                    POST_CODE                                SUM_SALARY MAX_SALARY
-------------------------------------------- ---------------------------------------- ---------- ----------
010102                                                                                     13500       7500
010102                                       P40                                            7500       7500
010102                                       P50                                            6000       6000
010103                                                                                      7850       5050
010103                                       P40                                            5050       5050
010103                                       P50                                            2800       2800

2.3、GROUPING SETS 分組

GROUPING SETS 是 GROUP BY 子句的一種擴展，它允許一次計算多個標準分組的小計。語法：

GROUP BY GROUPING SETS(grouping_column_reference_list)

GROUPING SETS 的計算結果和分組列的順序無關，結果集排序也和分組列的順序無關。當 GROUPING SETS 中指定 n 列時，整個計算過程中分組方式有 n 種。如GROUPING SETS(A,B,C)相當於 GROUP BY A、GROUP BY B 和 GROUP BY C 這 3 個分組 UNION ALL 的結果。

示例 1：

SELECT t.dept_code,SUM(t.post_salary) sum_salary,MAX(t.post_salary) max_salary
FROM demo.t_staff t
WHERE t.dept_code IN('010102','010103')
GROUP BY GROUPING SETS(t.dept_code);

結果：

DEPT_CODE                                          SUM_SALARY MAX_SALARY
-------------------------------------------------- ---------- ----------
010102                                                  13500       7500
010103                                                   7850       5050

示例 2：

SELECT t.dept_code,t.post_code,SUM(t.post_salary) sum_salary,MAX(t.post_salary) max_salary
FROM demo.t_staff t
WHERE t.dept_code IN('010102','010103')
GROUP BY GROUPING SETS(t.dept_code,t.post_code);

結果：

DEPT_CODE                                     POST_CODE                                  SUM_SALARY MAX_SALARY
--------------------------------------------- ------------------------------------------ ---------- ----------
010102                                                                                        13500       7500
010103                                                                                         7850       5050
                                              P50                                              8800       6000
                                              P40                                             12550       7500

示例 3，部分 GROUPING SETS 分組：

SELECT t.dept_code,t.post_code,SUM(t.post_salary) sum_salary,MAX(t.post_salary) max_salary
FROM demo.t_staff t
WHERE t.dept_code IN('010102','010103')
GROUP BY t.dept_code,GROUPING SETS(t.post_code);

結果：

DEPT_CODE                                    POST_CODE                                 SUM_SALARY MAX_SALARY
-------------------------------------------- ----------------------------------------- ---------- ----------
010103                                       P40                                             5050       5050
010102                                       P40                                             7500       7500
010102                                       P50                                             6000       6000
010103                                       P50                                             2800       2800

示例 4，GROUPING SETS 能夠接受 ROLLUP 和 CUBE 作為它的參數；GROUPING SETS 只對單列進行分組，而不提供合計的功能，如果需要 GROUPING SETS 提供合計，可用 ROLLUP 或 CUBE 作參數來提供合計功能（註意 ROLLUP 和 CUBE 不接受 GROUPING SETS 作參數，ROLLUP 和 CUBE 之間互相作為參數也不可以）：

SELECT t.dept_code,t.post_code,SUM(t.post_salary) sum_salary,MAX(t.post_salary) max_salary
FROM demo.t_staff t
WHERE t.dept_code IN('010102','010103')
GROUP BY GROUPING SETS(ROLLUP(t.dept_code),ROLLUP(t.post_code));

結果：

DEPT_CODE                                    POST_CODE                                  SUM_SALARY MAX_SALARY
-------------------------------------------- ------------------------------------------ ---------- ----------
010102                                                                                       13500       7500
010103                                                                                        7850       5050
                                             P50                                              8800       6000
                                             P40                                             12550       7500
                                                                                             21350       7500
                                                                                             21350       7500

2.4、複雜分組（組合列分組、重覆列分組、連接分組）

簡單來說：組合列分組就是允許 ROLLUP、CUBE 和 GROUPING SETS 中可以有多個列或列組合；重覆列分組就是允許 GROUP BY 後面重覆出現分組列；連接分組就是允許 GROUP BY 後面有多個 ROLLUP、CUBE 或 GROUPING SETS。

組合列分組有過濾某些小計或計算一些額外的小計的功能。前面的部分 ROLLUP 和部分 CUBE 都沒有合計，使用組合列分組既可以實現部分 ROLLUP 或部分 CUBE 的功能，還能有合計。如ROLLUP(A,(B,C))，既能過濾 B、C 的小計，還能計算 ABC 的合計。

連接分組的分組級別是由所有 ROLLUP、CUBE 或 GROUPING SETS 分組的級別組成的笛卡爾積。如ROLLUP(A,B),ROLLUP(C,D,E)的分組級別是 (2+1)×(3+1)=12，CUBE(A,B),CUBE(C,D,E)的分組級別是 (4)×(8)=32，CUBE(A,B),CUBE(C,D,E)的分組級別是 (4)×(8)=32。

按我的理解來說：複雜分組無非也就是對標準擴展分組的綜合運用。在實際開發中，可能會遇到一些僅使用標準擴展分組無法實現的需求，這時候就可以考慮靈活的運用標準擴展分組，通過複雜分組來實現。

3、GROUP BY 擴展函數

3.1、GROUPING 函數

GROUPING 語法：GROUPING(expr)。因為原始數據中可能存在 NULL，且小計或合計的值也可能為 NULL，這樣一來就顯得數據比較混亂了。當該函數出現在 SELECT 子句中時，如果聚集行的 expr 為 NULL，它就返回 1；如果常規行的 expr 為 NULL，它就返回 0。通常將一個分組列作為該函數的參數，然後通過判斷它的返回值來區分聚集行與常規行，從而進一步對結果集美化或過濾。示例：

SELECT DECODE(GROUPING(t.dept_code),1,'合計',t.dept_code) dept_code,
  SUM(t.post_salary) sum_salary,GROUPING(t.dept_code) gd
FROM demo.t_staff t
WHERE t.dept_code IN('010102','010103')
GROUP BY ROLLUP(t.dept_code);

結果：

DEPT_CODE                                          SUM_SALARY         GD
-------------------------------------------------- ---------- ----------
010102                                                  13500          0
010103                                                   7850          0
合計                                                    21350          1

3.2、GROUPING_ID 函數

GROUPING_ID 語法：GROUPING_ID(expr [, expr ]...)。當該函數出現在 SELECT 子句中時，它將返回與行相關聯的 GROUPING 位向量對應的數值。GROUPING_ID 函數按從左到右的順序計算，如果此列是分組列，則為 0，如果是小計或合計則為 1，然後按列的順序將計算結果組成二進位序列（位向量），最後將位向量轉化為十進位數。GROUPING_ID 函數在功能上等效於多個 GROUPING 函數的結果，有了 GROUPING_ID 就不必再寫多個 GROUPING 了，也使得行過濾條件更容易表達。當查詢結果有多個聚合級別時，該函數特別有用，可通過它的返回值來排序和過濾結果集。示例：

SELECT t.dept_code,t.post_code,SUM(t.post_salary) sum_salary,
  GROUPING_ID(t.dept_code) gd,
  GROUPING_ID(t.post_code) gp,
  GROUPING_ID(t.dept_code,t.post_code) gdp,
  GROUPING_ID(t.post_code,t.dept_code) gpd
FROM demo.t_staff t
WHERE t.dept_code IN('010102','010103')
GROUP BY CUBE(t.dept_code,t.post_code)
ORDER BY GROUPING_ID(t.dept_code,t.post_code);

結果（結果集相當於是按 GDP 升序排序）：

DEPT_CODE                     POST_CODE                SUM_SALARY         GD         GP        GDP        GPD
----------------------------- ------------------------ ---------- ---------- ---------- ---------- ----------
010102                        P40                            7500          0          0          0          0
010103                        P40                            5050          0          0          0          0
010102                        P50                            6000          0          0          0          0
010103                        P50                            2800          0          0          0          0
010103                                                       7850          0          1          1          2
010102                                                      13500          0          1          1          2
                              P50                            8800          1          0          2          1
                              P40                           12550          1          0          2          1
                                                            21350          1          1          3          3

3.3、GROUP_ID 函數

GROUP_ID 語法：GROUP_ID()。當該函數出現在 SELECT 子句中時，如果結果集中存在 n 個重覆，那麼它將返回範圍從 0 到 n-1 中的數字，這對於從查詢結果中剔除重覆分組來說非常有用。示例（一般通過HAVING GROUP_ID()<1將重覆行全部剔除）：

SELECT t.dept_code,SUM(t.post_salary) sum_salary,GROUP_ID() group_id
FROM demo.t_staff t
WHERE t.dept_code IN('010102','010103')
GROUP BY ROLLUP(t.dept_code),CUBE(t.dept_code);

結果：

DEPT_CODE                                          SUM_SALARY   GROUP_ID
-------------------------------------------------- ---------- ----------
010102                                                  13500          0
010103                                                   7850          0
010102                                                  13500          2
010103                                                   7850          2
010102                                                  13500          1
010103                                                   7850          1
                                                        21350          0

4、總結

本文主要講述了 Oracle 中分組查詢的標準分組、擴展分組、擴展函數等 GROUP BY 相關的知識點。

本文鏈接：http://www.cnblogs.com/hanzongze/p/Oracle-Group-By.html
版權聲明：本文為博客園博主 韓宗澤 原創，作者保留署名權！歡迎通過轉載、演繹或其它傳播方式來使用本文，但必須在明顯位置給出作者署名和本文鏈接！本人初寫博客，水平有限，若有不當之處，敬請批評指正，謝謝！