- -- 使用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行。
- */
oracle 中 rollup、cube、grouping 使用详解 -- 使用oracle 样例表演示
最新推荐文章于 2022-12-15 09:12:53 发布