SQL 基础--> ROLLUP与CUBE运算符实现数据汇总

  1. 云栖社区>
  2. 博客>
  3. 正文

SQL 基础--> ROLLUP与CUBE运算符实现数据汇总

leshami 2010-06-01 17:16:00 浏览680
展开阅读全文

--=============================================

-- SQL 基础--> ROLLUPCUBE运算符实现数据汇总

--=============================================

 

    在使用ROLLUPCUBE运算符实现数据的汇总是,OracleSQL Server使用了不同的写法,但其实质是一样的,都遵循了SQL规范。

    ROLLUPCUBE运算符都是对GROUP BY 子句的扩展

       SQL Server中的用法

           GROUP BY col1,col2

           WITH ROLLUP | CUBE ;

       Oracle 中的用法

           GROUP BY

           ROLLUP | CUBE (col1,col2);

          

    ROLLUP:为每个分组返回小计记录以及为所有分组返回总计记录

    CUBE:返回列中所有组合的小计记录以及为所有分组返回总计记录

   

    关于在SQL SeverROLLUPCUBE运算符,请参照:ROLLUP CUBE 使

 

一、演示ROLLUP CUBE 的使用

   

    --使用常规的GROUP BY 实现数据的汇总

      

    SQL> SELECT deptno,SUM(sal) FROM emp WHERE deptno IS NOT NULL

      2  GROUP BY deptno;

 

       DEPTNO   SUM(SAL)

    ---------- ----------

           30       9400

           20      10875

           10       8750

      

    --增加ROLLUP运算符实现对数据的汇总,增加了对DEPTNO列的总计

    SQL> SELECT deptno,SUM(sal) FROM emp WHERE deptno IS NOT NULL

      2  GROUP BY ROLLUP (deptno);

 

       DEPTNO   SUM(SAL)

    ---------- ----------

           10       8750

           20      10875

           30       9400

                  29025 

   

    --使用ROLLUP实现对多列数据进行汇总

    --以下示例,先按deptno,job分组,并实现sum函数的聚合,接下来按从右向左的方向实现更高层次的聚合,如对同一个

    --部门的作汇总,最后对所有部门作汇总。汇总层数为n+1,其中nrollup中的列数

    --因表emp存在deptno为空的记录,故以下所有演示中使用了where 子句过滤空值

    SQL> SELECT deptno,job, SUM(sal)  FROM emp WHERE deptno IS NOT NULL

      2  GROUP BY ROLLUP (deptno,job)

      3  ORDER BY deptno,job;

 

       DEPTNO JOB         SUM(SAL)

    ---------- --------- ----------

           10 CLERK           1300

           10 MANAGER         2450

           10 PRESIDENT       5000

           10                 8750

           20 ANALYST         6000

           20 CLERK           1900

           20 MANAGER         2975

           20                10875

           30 CLERK            950

           30 MANAGER         2850

           30 SALESMAN        5600

 

       DEPTNO JOB         SUM(SAL)

    ---------- --------- ----------

           30                 9400

                           29025

                          

    --交换deptno,job列查看不同的列的顺序导致了不同的结果,原因是不同的分组产生了不同的结果

    SQL> SELECT deptno,job, SUM(sal)  FROM emp WHERE deptno IS NOT NULL

      2  GROUP BY ROLLUP (job,deptno)

      3  ORDER BY job,deptno;

 

       DEPTNO JOB         SUM(SAL)

    ---------- --------- ----------

           20 ANALYST         6000

              ANALYST         6000

           10 CLERK           1300

           20 CLERK           1900

           30 CLERK            950

              CLERK           4150

           10 MANAGER         2450

           20 MANAGER         2975

           30 MANAGER         2850

              MANAGER         8275

           10 PRESIDENT       5000

 

       DEPTNO JOB         SUM(SAL)

    ---------- --------- ----------

              PRESIDENT       5000

           30 SALESMAN        5600

              SALESMAN        5600

                           29025

                          

    --ROLLUP ,CUBE可以配合不同的聚合函数来使用 

    SQL> SELECT deptno,job, SUM(sal),ROUND(AVG(sal),2)  FROM emp WHERE deptno IS NOT NULL

      2  GROUP BY ROLLUP (deptno,job)

      3  ORDER BY deptno,job;

 

       DEPTNO JOB         SUM(SAL) ROUND(AVG(SAL),2)

    ---------- --------- ---------- -----------------

           10 CLERK           1300              1300

           10 MANAGER         2450              2450

           10 PRESIDENT       5000              5000

           10                 8750           2916.67

           20 ANALYST         6000              3000

           20 CLERK           1900               950

           20 MANAGER         2975              2975

           20                10875              2175

           30 CLERK            950               950

           30 MANAGER         2850              2850

           30 SALESMAN        5600              1400

 

       DEPTNO JOB         SUM(SAL) ROUND(AVG(SAL),2)

    ---------- --------- ---------- -----------------

           30                 9400           1566.67

                           29025           2073.21

 

    13 rows selected.                       

 

    --使用CUBE子句实现对数据的汇总

    --从结果集中可以看出CUBE对不同的维度也实现了数据汇总,本例中多出的列即为不同的JOB也产生了汇总数据

    SQL> SELECT deptno,job, SUM(sal)  FROM emp WHERE deptno IS NOT NULL

      2  GROUP BY CUBE (deptno,job)

      3  ORDER BY deptno,job;

 

       DEPTNO JOB         SUM(SAL)

    ---------- --------- ----------

           10 CLERK           1300

           10 MANAGER         2450

           10 PRESIDENT       5000

           10                 8750

           20 ANALYST         6000

           20 CLERK           1900

           20 MANAGER         2975

           20                10875

           30 CLERK            950

           30 MANAGER         2850

           30 SALESMAN        5600

 

       DEPTNO JOB         SUM(SAL)

    ---------- --------- ----------

           30                 9400

              ANALYST         6000

              CLERK           4150

              MANAGER         8275

              PRESIDENT       5000

              SALESMAN        5600

                           29025

 

    18 rows selected.

   

二、使用GROUPING函数处理汇总结果中的空值

    GROUPING函数仅在使用ROLLUPCUBE查询中使用,可以接受一列,其结果返回为或者,如果列值为空,则返回,否则返回

   

    --单列使用GROUPING函数

    SQL> SELECT GROUPING(deptno),deptno, SUM(sal)  FROM emp WHERE deptno IS NOT NULL

      2  GROUP BY ROLLUP(deptno)

      3  ORDER BY deptno;

 

    GROUPING(DEPTNO)     DEPTNO   SUM(SAL)

    ---------------- ---------- ----------

                 0         10       8750

                 0         20      10875

                 0         30       9400

                 1                 29025   

 

    --使用CASE子句转换GROUPING函数的返回值

    SQL> SELECT CASE GROUPING(deptno) WHEN 1 THEN 'All Deptno' ELSE TO_CHAR(deptno) END AS deptnumber, SUM(sal)

      2  FROM emp WHERE deptno IS NOT NULL

      3  GROUP BY ROLLUP(deptno)

      4  ORDER BY deptno;

 

    DEPTNUMBER                                 SUM(SAL)

    ---------------------------------------- ----------

    10                                             8750

    20                                            10875

    30                                             9400

    All Deptno                                    29025

 

    --使用CASE子句转换多列GROUPING函数的返回值

    SQL> SELECT CASE GROUPING(deptno) WHEN 1 THEN 'All Deptno' ELSE TO_CHAR(deptno) END AS "DeptNo",

      2      CASE GROUPING(job) WHEN 1 THEN 'All Job' ELSE job END AS "Job",

      3      SUM(sal)

      4  FROM emp WHERE deptno IS NOT NULL

      5  GROUP BY ROLLUP(deptno,job);

 

    DeptNo                                   Job         SUM(SAL)

    ---------------------------------------- --------- ----------

    10                                       CLERK           1300

    10                                       MANAGER         2450

    10                                       PRESIDENT       5000

    10                                       All Job         8750

    20                                       CLERK           1900

    20                                       ANALYST         6000

    20                                       MANAGER         2975

    20                                       All Job        10875

    30                                       CLERK            950

    30                                       MANAGER         2850

    30                                       SALESMAN        5600

 

    DeptNo                                   Job         SUM(SAL)

    ---------------------------------------- --------- ----------

    30                                       All Job         9400

    All Deptno                               All Job        29025

 

    --CUBEGROUPING函数的结合使用

    SQL> SELECT CASE GROUPING(deptno) WHEN 1 THEN 'All Deptno' ELSE TO_CHAR(deptno) END AS "DeptNo",

      2      CASE GROUPING(job) WHEN 1 THEN 'All Job' ELSE job END AS "Job",

      3      SUM(sal)

      4  FROM emp WHERE deptno IS NOT NULL

      5  GROUP BY CUBE(deptno,job)

      6  ORDER BY deptno,job;

 

    DeptNo                                   Job         SUM(SAL)

    ---------------------------------------- --------- ----------

    10                                       CLERK           1300

    10                                       MANAGER         2450

    10                                       PRESIDENT       5000

    10                                       All Job         8750

    20                                       ANALYST         6000

    20                                       CLERK           1900

    20                                       MANAGER         2975

    20                                       All Job        10875

    30                                       CLERK            950

    30                                       MANAGER         2850

    30                                       SALESMAN        5600

 

    DeptNo                                   Job         SUM(SAL)

    ---------------------------------------- --------- ----------

    30                                       All Job         9400

    All Deptno                               ANALYST         6000

    All Deptno                               CLERK           4150

    All Deptno                               MANAGER         8275

    All Deptno                               PRESIDENT       5000

    All Deptno                               SALESMAN        5600

    All Deptno                               All Job        29025

 

三、使用GROUPING SETS 子句列出小计

    --GROUPING SETS 子句仅返回小计的记录

    --注意GROUPING SETS的用法是替换掉ROLLUP CUBE

    SQL> SELECT deptno,job,SUM(sal)

      2  FROM emp

      3  WHERE deptno IS NOT NULL

      4  GROUP BY GROUPING SETS(deptno,job)

      5  ORDER BY deptno,job;

 

       DEPTNO JOB         SUM(SAL)

    ---------- --------- ----------

           10                 8750

           20                10875

           30                 9400

              ANALYST         6000

              CLERK           4150

              MANAGER         8275

              PRESIDENT       5000

              SALESMAN        5600

 

四、使用GROUPING_ID函数和HAVING子句过滤结果集

    --GROUPING_ID函数接受一列或多列输入,返回一个十进制的值

    --GROUPING_ID返回值为调用GROUPING函数的组合结果

    --0 0 ==> 00  ,0 1 ==> 1 , 1 0 ==> 2 , 1 1 ==> 3(左边为GROUPING函数得到的列值,右边为转换结果)

    SQL> SELECT deptno,job,GROUPING(deptno) dept_grp,GROUPING(job) job_grp,

      2      GROUPING_ID(deptno,job),SUM(sal)

      3  FROM emp

      4  WHERE deptno IS NOT NULL

      5  GROUP BY CUBE(deptno,job)

      6  ORDER BY deptno,job;

 

       DEPTNO JOB         DEPT_GRP    JOB_GRP GROUPING_ID(DEPTNO,JOB)   SUM(SAL)

    ---------- --------- ---------- ---------- ----------------------- ----------

           10 CLERK              0          0                       0       1300

           10 MANAGER            0          0                       0       2450

           10 PRESIDENT          0          0                       0       5000

           10                    0          1                       1       8750

           20 ANALYST            0          0                       0       6000

           20 CLERK              0          0                       0       1900

           20 MANAGER            0          0                       0       2975

           20                    0          1                       1      10875

           30 CLERK              0          0                       0        950

           30 MANAGER            0          0                       0       2850

           30 SALESMAN           0          0                       0       5600

 

       DEPTNO JOB         DEPT_GRP    JOB_GRP GROUPING_ID(DEPTNO,JOB)   SUM(SAL)

    ---------- --------- ---------- ---------- ----------------------- ----------

           30                    0          1                       1       9400

              ANALYST            1          0                       2       6000

              CLERK              1          0                       2       4150

              MANAGER            1          0                       2       8275

              PRESIDENT          1          0                       2       5000

              SALESMAN           1          0                       2       5600

                              1          1                       3      29025  

   

    --使用HAVING子句和GROUPING_ID过滤非小计和总计记录  

    SQL> SELECT deptno,job,                                                

      2      GROUPING_ID(deptno,job),SUM(sal)

      3  FROM emp

      4  WHERE deptno IS NOT NULL

      5  GROUP BY CUBE(deptno,job)

      6  HAVING GROUPING_ID(deptno,job) > 0

      7  ORDER BY deptno,job;

 

       DEPTNO JOB       GROUPING_ID(DEPTNO,JOB)   SUM(SAL)

    ---------- --------- ----------------------- ----------

           10                                 1       8750

           20                                 1      10875

           30                                 1       9400

              ANALYST                         2       6000

              CLERK                           2       4150

              MANAGER                         2       8275

              PRESIDENT                       2       5000

              SALESMAN                        2       5600

                                          3      29025

                                     

五、GROUP BY ,CUBE ROLLUP 中同时使用一列的处理

    --即某些列同时在GROUP BY 子句和CUBE(ROLLUP)中存在

    --结果中可以看出多出了一些重复的列

    SQL> SELECT deptno,job,SUM(sal)

      2  FROM emp

      3  WHERE deptno IS NOT NULL

      4  GROUP BY deptno,ROLLUP(deptno,job)

      5  ORDER BY deptno,job;

 

       DEPTNO JOB         SUM(SAL)

    ---------- --------- ----------

           10 CLERK           1300

           10 MANAGER         2450

           10 PRESIDENT       5000

           10                 8750

           10                 8750

           20 ANALYST         6000

           20 CLERK           1900

           20 MANAGER         2975

           20                10875

           20                10875

           30 CLERK            950

 

       DEPTNO JOB         SUM(SAL)

    ---------- --------- ----------

           30 MANAGER         2850

           30 SALESMAN        5600

           30                 9400

           30                 9400                                    

      

六、使用GROUP_ID函数过滤结果集

    --GROUP_ID函可以消除GROUP BY 子句返回的重复记录

    --GROUP_ID函数不接受任何参数

    --对于特定分组结果中重复出现n次,GROUP_ID()函数返回到n-1 之间的一个整数

    SQL> SELECT deptno,job,GROUP_ID(),SUM(sal)

      2  FROM emp

      3  WHERE deptno IS NOT NULL

      4  GROUP BY deptno,ROLLUP(deptno,job)

      5  ORDER BY deptno,job;

 

       DEPTNO JOB       GROUP_ID()   SUM(SAL)

    ---------- --------- ---------- ----------

           10 CLERK              0       1300

           10 MANAGER            0       2450

           10 PRESIDENT          0       5000

           10                    1       8750

           10                    0       8750

           20 ANALYST            0       6000

           20 CLERK              0       1900

           20 MANAGER            0       2975

           20                    1      10875

           20                    0      10875

           30 CLERK              0        950

 

       DEPTNO JOB       GROUP_ID()   SUM(SAL)

    ---------- --------- ---------- ----------

           30 MANAGER            0       2850

           30 SALESMAN           0       5600

           30                    1       9400

           30                    0       9400

 

    --使用HAVING子句和GROUP_ID函数过滤结果集

    SQL> SELECT deptno,job,GROUP_ID(),SUM(sal)

      2  FROM emp

      3  WHERE deptno IS NOT NULL

      4  GROUP BY deptno,ROLLUP(deptno,job)

      5  HAVING GROUP_ID() = 0            

      6  ORDER BY deptno,job;

 

       DEPTNO JOB       GROUP_ID()   SUM(SAL)

    ---------- --------- ---------- ----------

           10 CLERK              0       1300

           10 MANAGER            0       2450

           10 PRESIDENT          0       5000

           10                    0       8750

           20 ANALYST            0       6000

           20 CLERK              0       1900

           20 MANAGER            0       2975

           20                    0      10875

           30 CLERK              0        950

           30 MANAGER            0       2850

           30 SALESMAN           0       5600

 

       DEPTNO JOB       GROUP_ID()   SUM(SAL)

    ---------- --------- ---------- ----------

           30                    0       9400

 

七、总结:

    ROLLUP CUBE 是对GROUP BY 子句的扩展

    ROLLUP:根据grouping list rollup条款创建小计,然后从左向右再实现聚合。

       1.首先按group by (col1,col2)标准分组聚合

       2.按照rollup(col1,col2)中的列从右向左的顺序进行更高层次的聚合

       3.创建n+1层的总计,n = rollup(col1,col2...)中的列数

      

    CUBE: 创建所有可能的小计

       1.结果集将会包括相应rollup的所有值并加上额外的合并

       2.如果有n类在cube中规定,将会有的n次方的小计返回

      

    GROUPING()函数:

       仅仅接受CUBEROLLUP中的单列,不能使用复合列,如果列值为空,将返回,否则返回

        通常配合CASE WHEN 用于替换空值

      

    GROUPING SETS子句:

        用于只返回小计记录

      

    GROUPING_ID() 函数:

        接受ROLLUPCUBE中的多列,返回一个十进制值,实际上是GROUPING()函数值的组合

       可以配合HAVING子句来过滤掉不需要的统计信息

      

    GRUOUP_ID() 函数:

        不接受任何参数

        对于特定出现n次的分组,   GRUOUP_ID()返回从到n-1之间的一个整数  

       可以配合HAVING子句消除group by 子句返回的重复记录

      

八、更多

       

    ROLLUP 与 CUBE 运算符的使用

 

VmWare6.5.2下安装RHEL 5.4(配置Oracle安装环境)

 

Oracle 10g SGA 的自动化管理

 

Oracle 表空间与数据文件

 

SQL基础-->层次化查询(START BY ... CONNECT BY PRIOR)

 

Oracle 常用目录结构(10g)

 

网友评论

登录后评论
0/500
评论