oracle 中 rollup、cube、grouping 使用详解

juqtwwgnpm

oracle 中 rollup、cube、grouping 使用详解 -- 使用oracle 样例表演示 转自namesliu

-- 使用oracle 的样列库，演示 rollup, cube, grouping 的用法与使用场景  
  
--- ROLLUP ， 为了理解分组的成员数量，我增加了 分组的计数  COUNT(SAL)  
  SELECT   E1.DEPTNO,  
           JOB,  
           TO_CHAR (E1.HIREDATE, 'YYYY-MM-DD'),  
           SUM (SAL),  
           COUNT (SAL)  
    FROM   emp e1  
GROUP BY   ROLLUP (E1.DEPTNO, E1.JOB, E1.HIREDATE);  
  
/*  
分组情况为:  
DEPTNO，job,HIREDATE   第 1 种分组  (A,B,C)   
DEPTNO，job,           第 2 种分组  (A,B)   
DEPTNO                 第 3 种分组  (A)   
                       总分一个组  
                        
-- 结果如下：  并进行部分数据解释，以便读者理解  
    DEPTNO JOB       TO_CHAR(E1   SUM(SAL) COUNT(SAL)  
---------- --------- ---------- ---------- ----------  
        10 CLERK     1982-01-23       1300          1   (A,B,C)  -- 10号部门,CLERK工种，入职日期为1982-01-23，只有一个成员 工资和为：1300  
        10 CLERK                      1300          1   (A,B)    -- 10号部门,CLERK工种，只有一个成员 工资和为：1300  
        10 MANAGER   1981-06-09       2450          1   (A,B,C)  -- 与第一条记录相似的分析   
        10 MANAGER                    2450          1   (A,B)  
        10 PRESIDENT 1981-11-17       5000          1   (A,B,C)   
        10 PRESIDENT                  5000          1   (A,B,C)  
        10                            8750          3   (A)      -- 10号部门有3个成员，工资总计为8750  
        20 CLERK     1980-12-17        800          1  
        20 CLERK                       800          1  
        20 ANALYST   1981-12-03       3000          1  
        20 ANALYST                    3000          1  
        20 MANAGER   1981-04-02       2975          1  
        20 MANAGER                    2975          1  
        20                            6775          3  
        30 CLERK     1981-12-03        950          1  
        30 CLERK                       950          1  
        30 MANAGER   1981-05-01       2850          1  
        30 MANAGER                    2850          1  
        30 SALESMAN  1981-02-20       1600          1  
        30 SALESMAN  1981-02-22       1250          1  
        30 SALESMAN  1981-09-08       1500          1  
        30 SALESMAN  1981-09-28       1250          1  
        30 SALESMAN                   5600          4    (A,B)    -- 30号部门,SALESMAN工种，有4个成员 工资和为：5600   
        30                            9400          6    (A)      -- 30号部 有6个成员， 工资总计为：9400   
                                     24925         12     ()      -- 所有部门工资总和为：24925                          
*/  
  
  
--- CUBE ， 为了理解分组的成员数量，我增加了 分组的计数  COUNT(SAL)  
  SELECT   E1.DEPTNO,  
           JOB,  
           TO_CHAR (E1.HIREDATE, 'YYYY-MM-DD'),  
           SUM (SAL),  
           COUNT (SAL)  
    FROM   emp e1  
GROUP BY   CUBE (E1.DEPTNO, E1.JOB, E1.HIREDATE);  
  
/*  
分组原则：  
GROUP BY CUBE(A, B, C)，则首先会对(A、B、C)进行GROUP BY，然后依次是(A、B)，(A、C)，(A)，(B、C)，(B)，(C)，最后对全表进行GROUP BY操作。  
  
-- 结果 ：  并进行部分数据解释，以便读者理解  
    DEPTNO JOB       TO_CHAR(E1   SUM(SAL) COUNT(SAL)  
---------- --------- ---------- ---------- ----------  
                                     24925         12   -- 全表分组，工资总合:24925   
                     1980-12-17        800          1     
                     1981-02-20       1600          1  
                     1981-02-22       1250          1  
                     1981-04-02       2975          1  
                     1981-05-01       2850          1  
                     1981-06-09       2450          1  
                     1981-09-08       1500          1  
                     1981-09-28       1250          1  
                     1981-11-17       5000          1  
                     1981-12-03       3950          2   (C)  1981-12-03 入职的有2位员工，工资总计3950  
                     1982-01-23       1300          1  
           CLERK                      3050          3   (B) CLERK 工种，共计有3位员工，工资总计：3050  
           CLERK     1980-12-17        800          1  
           CLERK     1981-12-03        950          1  
           CLERK     1982-01-23       1300          1  
           ANALYST                    3000          1  
           ANALYST   1981-12-03       3000          1  
           MANAGER                    8275          3  
           MANAGER   1981-04-02       2975          1  
           MANAGER   1981-05-01       2850          1  
           MANAGER   1981-06-09       2450          1  
           SALESMAN                   5600          4  
           SALESMAN  1981-02-20       1600          1  
           SALESMAN  1981-02-22       1250          1  
           SALESMAN  1981-09-08       1500          1  
           SALESMAN  1981-09-28       1250          1  
           PRESIDENT                  5000          1  
           PRESIDENT 1981-11-17       5000          1  
        10                            8750          3  
        10           1981-06-09       2450          1  
        10           1981-11-17       5000          1  
        10           1982-01-23       1300          1  
        10 CLERK                      1300          1  
        10 CLERK     1982-01-23       1300          1  
        10 MANAGER                    2450          1  
        10 MANAGER   1981-06-09       2450          1  
        10 PRESIDENT                  5000          1  
        10 PRESIDENT 1981-11-17       5000          1  
        20                            6775          3  (A) -- 20号部 有3个成员， 工资总计为：6775   
        20           1980-12-17        800          1  
        20           1981-04-02       2975          1  
        20           1981-12-03       3000          1  
        20 CLERK                       800          1  
        20 CLERK     1980-12-17        800          1  
        20 ANALYST                    3000          1  
        20 ANALYST   1981-12-03       3000          1  
        20 MANAGER                    2975          1  
        20 MANAGER   1981-04-02       2975          1  
        30                            9400          6  (A) -- 30号部 有6个成员， 工资总计为：9400   
        30           1981-02-20       1600          1  
        30           1981-02-22       1250          1  
        30           1981-05-01       2850          1  
        30           1981-09-08       1500          1  
        30           1981-09-28       1250          1  
        30           1981-12-03        950          1  
        30 CLERK                       950          1  
        30 CLERK     1981-12-03        950          1  
        30 MANAGER                    2850          1  
        30 MANAGER   1981-05-01       2850          1  
        30 SALESMAN                   5600          4  (A、B) 30号部门，  SALESMAN 工种，有4 个成员，工资总计：5600  
        30 SALESMAN  1981-02-20       1600          1   
        30 SALESMAN  1981-02-22       1250          1  
        30 SALESMAN  1981-09-08       1500          1  (A、B、C)  0号部门，  SALESMAN 工种，1981-09-08入职，1 个员工，工资总计：1500  
        30 SALESMAN  1981-09-28       1250          1  (A、B、C)  0号部门，  SALESMAN 工种，1981-09-28入职，1 个员工，工资总计：1250  
  
已选择65行。  
*/  
  
--- GROUPING函数  
/*  
GROUPING 是一个聚合函数,它产生一个附加的列，当用 CUBE 或 ROLLUP 运算符添加行时，附加的列输出值为1，当所添加的行不是由 CUBE 或 ROLLUP 产生时，附加列值为0。  
仅在与包含 CUBE 或 ROLLUP 运算符的 GROUP BY 子句相联系的选择列表中才允许分组。  
  
语法:  GROUPING ( column_name )  
  
是 GROUP BY 子句中用于检查 CUBE 或 ROLLUP 空值的列。  
  
返回类型: int  
  
分组用于区分由 CUBE 和 ROLLUP 返回的空值和标准的空值。作为CUBE 或 ROLLUP 操作结果返回的 NULL 是 NULL 的特殊应用。它在结果集内作为列的占位符，意思是"全体"。  
*/  
  
-- grouping 样列  
  SELECT   E1.DEPTNO,  
           JOB,  
           TO_CHAR (E1.HIREDATE, 'YYYY-MM-DD') HIREDATE,  
           SUM (SAL),  
           COUNT (SAL),  
           GROUPING (E1.DEPTNO) d,  
           GROUPING (JOB) j,  
           GROUPING (E1.HIREDATE) h  
    FROM   emp e1  
GROUP BY   ROLLUP (E1.DEPTNO, E1.JOB, E1.HIREDATE);  
  
  
  
/*  
-- 结果 ：  并进行部分数据解释，以便读者理解  
  
    DEPTNO JOB       HIREDATE     SUM(SAL) COUNT(SAL)   D   J   H  
---------- --------- ---------- ---------- ---------- --- --- ---  
        10 CLERK     1982-01-23       1300          1   0   0   0   所有列都有数据作为分组，所以全为 0  
        10 CLERK                      1300          1   0   0   1   DEPTNO JOB列 有数据，而HIREDATE没有数据，所以 H 列产生的值为：1  
        10 MANAGER   1981-06-09       2450          1   0   0   0      
        10 MANAGER                    2450          1   0   0   1  
        10 PRESIDENT 1981-11-17       5000          1   0   0   0  
        10 PRESIDENT                  5000          1   0   0   1  
        10                            8750          3   0   1   1  
        20 CLERK     1980-12-17        800          1   0   0   0  
        20 CLERK                       800          1   0   0   1  
        20 ANALYST   1981-12-03       3000          1   0   0   0  
        20 ANALYST                    3000          1   0   0   1  
        20 MANAGER   1981-04-02       2975          1   0   0   0  
        20 MANAGER                    2975          1   0   0   1  
        20                            6775          3   0   1   1  
        30 CLERK     1981-12-03        950          1   0   0   0  
        30 CLERK                       950          1   0   0   1  
        30 MANAGER   1981-05-01       2850          1   0   0   0  
        30 MANAGER                    2850          1   0   0   1  
        30 SALESMAN  1981-02-20       1600          1   0   0   0  
        30 SALESMAN  1981-02-22       1250          1   0   0   0  
        30 SALESMAN  1981-09-08       1500          1   0   0   0  
        30 SALESMAN  1981-09-28       1250          1   0   0   0  
        30 SALESMAN                   5600          4   0   0   1  
        30                            9400          6   0   1   1  
                                     24925         12   1   1   1     
*/  
  
  
-- 应用 grouping   
SELECT   CASE  
              WHEN (    GROUPING (E1.DEPTNO) = 0 AND GROUPING (JOB) = 0 AND GROUPING (HIREDATE) = 0)  
                   THEN DEPTNO|| ' '|| JOB|| ' ' || TO_CHAR (HIREDATE, 'YYYY-MM-DD')|| ' subtotal:'  
              WHEN (    GROUPING (E1.DEPTNO) = 0 AND GROUPING (JOB) = 0 AND GROUPING (HIREDATE) = 1)  
                   THEN DEPTNO || ' ' || JOB || ' subtotal:'  
              WHEN (    GROUPING (E1.DEPTNO) = 0 AND GROUPING (JOB) = 1 AND GROUPING (HIREDATE) = 1)  
                   THEN DEPTNO || ' subtotal:'  
              WHEN (    GROUPING (E1.DEPTNO) = 1 AND GROUPING (JOB) = 1 AND GROUPING (HIREDATE) = 1)  
                   THEN 'Total:'  
           END  
              "Total",   
           SUM (SAL), COUNT (SAL)  
    FROM   emp e1  
GROUP BY   ROLLUP (E1.DEPTNO, E1.JOB, E1.HIREDATE);  
  
-- 结果如下：  
/*  
Total                                  SUM(SAL) COUNT(SAL)  
------------------------------------ ---------- ----------  
10 CLERK 1982-01-23 subtotal:              1300          1   (A,B,C) 分组  
10 CLERK subtotal:                         1300          1   (A,B) 分组  
10 MANAGER 1981-06-09 subtotal:            2450          1  
10 MANAGER subtotal:                       2450          1  
10 PRESIDENT 1981-11-17 subtotal:          5000          1  
10 PRESIDENT subtotal:                     5000          1  
10 subtotal:                               8750          3  (A) 分组 10号部门，共3个成员，工资总计：8750  
20 CLERK 1980-12-17 subtotal:               800          1  
20 CLERK subtotal:                          800          1  
20 ANALYST 1981-12-03 subtotal:            3000          1  
20 ANALYST subtotal:                       3000          1  
20 MANAGER 1981-04-02 subtotal:            2975          1  
20 MANAGER subtotal:                       2975          1  
20 subtotal:                               6775          3  
30 CLERK 1981-12-03 subtotal:               950          1  
30 CLERK subtotal:                          950          1  
30 MANAGER 1981-05-01 subtotal:            2850          1  
30 MANAGER subtotal:                       2850          1  
30 SALESMAN 1981-02-20 subtotal:           1600          1  
30 SALESMAN 1981-02-22 subtotal:           1250          1  
30 SALESMAN 1981-09-08 subtotal:           1500          1  
30 SALESMAN 1981-09-28 subtotal:           1250          1  
30 SALESMAN subtotal:                      5600          4  
30 subtotal:                               9400          6  
Total:                                    24925         12  () 全部总计：12 个成员，工资总计为：24925  
  
已选择25行。  
*/