學習目標： -使用ROLLUP操作符產生小計值 -使用CUBE操作符產生交叉製表 -使用GROUPING函數來標識ROLLUP或CUBE運算符生成的結果集中的聚合級別 -使用GROUPING SETS生成一個相當於UNION ALL方法的結果集 ROLLUP操作符 SELECT [column,] ...
學習目標： -使用ROLLUP操作符產生小計值
-使用CUBE操作符產生交叉製表
-使用GROUPING函數來標識ROLLUP或CUBE運算符生成的結果集中的聚合級別
-使用GROUPING SETS生成一個相當於UNION ALL方法的結果集
ROLLUP操作符
SELECT [column,] group_function(column) . . . FROM <table_name> [WHERE condition] [GROUP BY [ROLLUP] group_by_expression] [HAVING having_expression] [ORDER BY column];-ROLLUP操作符可以提取結果集的統計和彙總信息。累積總量可用於報表，圖表和圖形。
-ROLLUP操作符通過向一個方向移動創建分組，從右到左，在GROUP BY子句中指定的列的列表，然後將這些分組聚合。N+1
註意：
-GROUP BY ROLLUP(col1,col2,col3,..n)產生組合有N+1個。
例子一：
10:40:59 SQL> select department_id,job_id,sum(salary) 10:41:19 2 from emp 10:41:24 3 where department_id<60 10:41:44 4 group by rollup(department_id,job_id) --組合執行的順序：(department_id，job_id) 、(department_id)和() 10:42:00 5 ; DEPARTMENT_ID JOB_ID SUM(SALARY) ------------- -------------------- ----------- 20 MK_MAN 12990--這裡是是部門id和job id組合 20 12990--這裡是部門id分組求和 30 PU_MAN 10990 30 10990 50 ST_MAN 36350 50 36350 60330--這是是總體分組求和。 已選取 7 個資料列.
例子二： 原始數據
ID NAME ---------- ------------------- 370101 A000001 370101 A000002 370102 A000003 370103 A000004 370201 A000006 370202 A000005 370301 A000007 370302 A000008 需產生報表格式：
ID COUNT(*) ---------- ---------- 37 8 3701 4 3702 2 3703 2 370101 2 370102 1 370103 1 370201 1 370202 1 370301 1 370302 1 可以實現方法：
SELECT DECODE (DECODE (id, NULL, TO_NUMBER (SUBSTR (Id, 0, 4)), id), NULL, TO_NUMBER (SUBSTR (Id, 0, 2)), DECODE (id, NULL, TO_NUMBER (SUBSTR (Id, 0, 4)), id)) AS "ID", COUNT (*) FROM T_0927 GROUP BY ROLLUP (TO_NUMBER (SUBSTR (Id, 0, 2)), TO_NUMBER (SUBSTR (Id, 0, 4)),id) ORDER BY 1;
CUBE操作符
SELECT [column,] group_function(column) ... FROM <table_name> [WHERE condition] [GROUP BY [CUBE] group_by_expression] [HAVING having_expression] [ORDER BY column];-CUBE是一個多維數據集操作符，可以應用於所有的聚合函數(avg,sum,max,min和count)。
-CUBE用於生成交叉報表的結果集，CUBE在GROUP BY子句中指定的所有可能的組合進行分組小計和總計。2^N次方
註意：
-GROUP BY CUBE(col1,col2,col3,...n)產生組合有2^N個。
例子一：
11:02:41 SQL> select department_id,job_id,sum(salary) 11:02:41 2 from emp 11:02:41 3 where department_id<60 11:02:41 4 group by cube(department_id,job_id)--組合：()、(department_id)、(job_id)和(department_id,job_id) 11:02:41 5 ; DEPARTMENT_ID JOB_ID SUM(SALARY) ------------- -------------------- ----------- 60330--這是總體分組求和 MK_MAN 12990--這是job_id分組求和 PU_MAN 10990 ST_MAN 36350 20 12990--這是department_id分組求和 20 MK_MAN 12990--這是department_id和job_id分組求和 30 10990 30 PU_MAN 10990 50 36350 50 ST_MAN 36350 已選取 10 個資料列.
GROUPING函數
SELECT [column,] group_function(column) ..., GROUPING(expression) FROM <table_name> [WHERE conditiion] [GROUP BY [ROLLUP|CUBE] group_by_expression] [HAVING having_expression] [ORDER BY column];-GROUPING函數幫助理解分組後CUBE或ROLLUP操作運算。 -GROUPING函數只能使用單列。 -GROUPING函數返回一個值0或1， --0表示使用CUBE或ROLLUP操作符計算聚合 --1表示沒有被CUBE或ROLLUP操作符計算聚合。 例子一：
11:22:13 SQL> select department_id,job_id,sum(salary),grouping(department_id),grouping(job_id) 11:22:31 2 from emp 11:22:31 3 where department_id<60 11:22:31 4 group by cube(department_id,job_id) 11:22:31 5 ; DEPARTMENT_ID JOB_ID SUM(SALARY) GROUPING(DEPARTMENT_ID) GROUPING(JOB_ID) ------------- -------------------- ----------- ----------------------- ---------------- 60330 1 1--department_id和job_id都沒被計算聚合 MK_MAN 12990 1 0--job_id被計算聚合 PU_MAN 10990 1 0 ST_MAN 36350 1 0 20 12990 0 1--department_id被計算聚合 20 MK_MAN 12990 0 0--department_id和job_id都被計算聚合 30 10990 0 1 30 PU_MAN 10990 0 0 50 36350 0 1 50 ST_MAN 36350 0 0 已選取 10 個資料列.
GROUPING SETS操作符
SELECT [column,] group_function(column) ... FROM <table_name> [WHERE condition] [GROUP BY GROUPING SETS( group_by_expression)] [HAVING having_expression] [ORDER BY column];-指定多維度組合成員，優化ROLLUP或CUBE指定組合。
例子一：
11:40:14 SQL> select department_id,job_id,sum(salary) 11:40:14 2 from emp 11:40:14 3 where department_id<60 11:40:14 4 group by grouping sets((department_id,job_id),(department_id)) 11:40:14 5 ; DEPARTMENT_ID JOB_ID SUM(SALARY) ------------- -------------------- ----------- 20 MK_MAN 12990 20 12990 30 PU_MAN 10990 30 10990 50 ST_MAN 36350 50 36350 已選取 6 個資料列.例子二：
11:45:18 SQL> SELECT DECODE (DECODE (id, NULL, TO_NUMBER (SUBSTR (Id, 0, 4)), id), 11:45:18 2 NULL, TO_NUMBER (SUBSTR (Id, 0, 2)), 11:45:18 3 DECODE (id, NULL, TO_NUMBER (SUBSTR (Id, 0, 4)), id)) 11:45:18 4 AS "ID", 11:45:18 5 COUNT (*) 11:45:18 6 FROM T_0927 11:45:18 7 GROUP BY GROUPING SETS((SUBSTR (Id, 0, 4)), (SUBSTR (Id, 0, 2)),(ID)) 11:45:18 8 ORDER BY 1; ID COUNT(*) ---------- ---------- 37 8 3701 4 3702 2 3703 2 370101 2 370102 1 370103 1 370201 1 370202 1 370301 1 370302 1 已選取 11 個資料列.
複合欄 如ROLLUP (a,(b,c),d)，(b,c)作為複合列在分組計算時被視為一個單元。 ROLLUP(A,(B,C)) 相當於(A,B,C)、(A)和()全體聯合。 例子一：
11:48:04 SQL> select department_id,job_id,employee_id,sum(salary) 11:54:22 2 from emp 11:54:22 3 where department_id<60 11:54:22 4 group by rollup(department_id,(job_id,employee_id)) 11:54:22 5 ; DEPARTMENT_ID JOB_ID EMPLOYEE_ID SUM(SALARY) ------------- -------------------- ----------- ----------- 20 MK_MAN 201 12990 20 12990 30 PU_MAN 114 10990 30 10990 50 ST_MAN 120 7990 50 ST_MAN 121 8190 50 ST_MAN 122 7890 50 ST_MAN 123 6490 50 ST_MAN 124 5790 50 36350 60330 已選取 11 個資料列.
混合組合 -可以將GROUP BY/ROLLUP/CUBE或GPOUPING SETS混合使用。 如GROUP BY GROUPING SETS(a,b) ,GROUPING SETS(c,d) GROUP BY <column>,ROLLUP(a),CUBE(b); 例子一：
11:55:33 SQL> select department_id,job_id,employee_id,sum(salary) 11:59:02 2 from emp 11:59:02 3 where department_id<60 11:59:02 4 group by department_id,rollup(job_id),cube(employee_id) 11:59:02 5 ; DEPARTMENT_ID JOB_ID EMPLOYEE_ID SUM(SALARY) ------------- -------------------- ----------- ----------- 20 MK_MAN 201 12990 30 PU_MAN 114 10990 50 ST_MAN 120 7990 50 ST_MAN 121 8190 50 ST_MAN 122 7890 50 ST_MAN 123 6490 50 ST_MAN 124 5790 20 201 12990 30 114 10990 50 120 7990 50 121 8190 DEPARTMENT_ID JOB_ID EMPLOYEE_ID SUM(SALARY) ------------- -------------------- ----------- ----------- 50 122 7890 50 123 6490 50 124 5790 20 MK_MAN 12990 20 12990 30 PU_MAN 10990 30 10990 50 ST_MAN 36350 50 36350 已選取 20 個資料列.
學習總結： -學習ROLLUP、CUBE操作符功能、方法和之間的不同點。 -學習GROUPING函數功能，用於區別單列是否被計算聚合(1/0)。 -學習GROUPING SETS操作符用於指定多維度的組合成員，進一步優化ROLLUP/CUBE操作符。 -學習在操作符內採用複合列來限制組合操作。 -學習混合維度查詢。
