Chinaunix首页 | 论坛 | 博客
  • 博客访问: 1369602
  • 博文数量: 172
  • 博客积分: 0
  • 博客等级: 民兵
  • 技术积分: 3831
  • 用 户 组: 普通用户
  • 注册时间: 2011-03-30 13:00
个人简介

About me:Oracle ACE pro,optimistic,passionate and harmonious. Focus on ORACLE,MySQL and other database programming,peformance tuning,db design, j2ee,Linux/AIX,Architecture tech,etc

文章分类

全部博文(172)

文章存档

2024年(27)

2023年(28)

2022年(43)

2020年(62)

2014年(3)

2013年(9)

分类: Oracle

2020-06-02 16:02:34

接PART3:http://blog.chinaunix.net/uid-7655508-id-5833649.html

1.4   GROUPING SETS实现小计

前面已经说了两种多维数据统计的方法:ROLLUPCUBE,它们的输出结果是由对应分组的行伴随着小计行产生的,它们会产生标准分组,各种小计以及合计,但是有时候我们只关心对某个单列分组,从而得到其他维度小计信息,这样就需要使用到GROUPING SETS扩展分组,它是Oracle9i提供的。

比如GROUP BY GROUPING SETS(a,b,c)相当于GROUP BY aGROUP BY bGROUP BY c3个分组的UNION ALL结果,这样结果中只有指定某些维度的小计,没有常规分组结果以及合计结果,对只关注某些维度的小计分析很有用,从GROUPING SETS操作的功能看出,n列的GROUPING SETS的分组种类有n个。

1.4.1   GROUPING SETS分组

GROUPING SETS的语法很简单,和ROLLUPCUBE类似:

SELECT … GROUP BY GROUPING SETS(grouping_column_reference_list)

      

   现在把1.2.2中的例子中的ROLLUP改为GROUPING SETS

SELECT to_char(b.hiredate,'yyyy') hire_year,a.dname,b.job, SUM(sal) sum_sal

FROM dept a,emp b

WHERE a.deptno = b.deptno

GROUP BY GROUPING SETS(to_char(b.hiredate,'yyyy'),a.dname,b.job);

   结果如下:
HIRE_YEA DNAME                        JOB                   SUM_SAL
-------- ---------------------------- ------------------ ----------
                                      CLERK                    3050
                                      SALESMAN                 5600
                                      PRESIDENT                5000
                                      MANAGER                  8275
                                      ANALYST                  3000
         ACCOUNTING                                            8750
         RESEARCH                                              6775
         SALES                                                 9400
1980                                                            800
1982                                                           1300
1981                                                          22825

执行计划
---------------------------------------------------------------------------------------------------
Plan hash value: 18386332


----------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name                       | Rows  | Bytes | Cost (%CPU)| T
ime     |


----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                            |    12 |   384 |    15  (20)| 0
0:00:01 |


|   1 |  TEMP TABLE TRANSFORMATION     |                            |       |       |            |
        |


|   2 |   LOAD AS SELECT               |                            |       |       |            |
        |


|   3 |    NESTED LOOPS                |                            |    12 |   432 |     4   (0)| 0
0:00:01 |


|   4 |     TABLE ACCESS FULL          | EMP                        |    12 |   276 |     3   (0)| 0
0:00:01 |


|   5 |     TABLE ACCESS BY INDEX ROWID| DEPT                       |     1 |    13 |     1   (0)| 0
0:00:01 |


|*  6 |      INDEX UNIQUE SCAN         | PK_DEPT                    |     1 |       |     0   (0)| 0
0:00:01 |


|   7 |   LOAD AS SELECT               |                            |       |       |            |
        |


|   8 |    HASH GROUP BY               |                            |     1 |    19 |     3  (34)| 0
0:00:01 |


|   9 |     TABLE ACCESS FULL          | SYS_TEMP_0FD9D6609_43CAA9C |     1 |    19 |     2   (0)| 0
0:00:01 |


|  10 |   LOAD AS SELECT               |                            |       |       |            |
        |


|  11 |    HASH GROUP BY               |                            |     1 |    22 |     3  (34)| 0
0:00:01 |


|  12 |     TABLE ACCESS FULL          | SYS_TEMP_0FD9D6609_43CAA9C |     1 |    22 |     2   (0)| 0
0:00:01 |


|  13 |   LOAD AS SELECT               |                            |       |       |            |
        |


|  14 |    HASH GROUP BY               |                            |     1 |    17 |     3  (34)| 0
0:00:01 |


|  15 |     TABLE ACCESS FULL          | SYS_TEMP_0FD9D6609_43CAA9C |     1 |    17 |     2   (0)| 0
0:00:01 |


|  16 |   VIEW                         |                            |     1 |    32 |     2   (0)| 0
0:00:01 |


|  17 |    TABLE ACCESS FULL           | SYS_TEMP_0FD9D660A_43CAA9C |     1 |    32 |     2   (0)| 0
0:00:01 |


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


Predicate Information (identified by operation id):
---------------------------------------------------


   6 - access("SYS_TBL_$2$"."DEPTNO"="SYS_TBL_$1$"."DEPTNO")


从结果上很容易看出这个统计的规律,也就是分别按单列分组之后UNION ALL的结果。比如前5行就是对于每个JOB值,
计算横跨所有入职时间
()和部门的小计。

 
注意:GROUPING SETS的结果和列的顺序没有关系,而且结果的顺序也是无序的,从计划看出,上面的GROUPING SETS的计划还是很复杂的。

1.4.2   部分GROUPING SETS分组

每种扩展GROUP BY都有部分分组特性,GROUPING SETS也不例外,改写上面的语句:

SELECT a.dname,to_char(b.hiredate,'yyyy') hire_year,b.job, SUM(sal) sum_sal

FROM dept a,emp b

WHERE a.deptno = b.deptno

GROUP BY a.dname, GROUPING SETS(to_char(b.hiredate,'yyyy'),b.job);

      

显示结果为:

DNAME                        HIRE_YEA JOB                   SUM_SAL
---------------------------- -------- ------------------ ----------
SALES                                 MANAGER                  2850
SALES                                 CLERK                     950
ACCOUNTING                            MANAGER                  2450
ACCOUNTING                            PRESIDENT                5000
ACCOUNTING                            CLERK                    1300
SALES                                 SALESMAN                 5600
RESEARCH                              MANAGER                  2975
RESEARCH                              ANALYST                  3000
RESEARCH                              CLERK                     800
RESEARCH                     1981                              5975
SALES                        1981                              9400
ACCOUNTING                   1981                              7450
ACCOUNTING                   1982                              1300
RESEARCH                     1980                               800


已选择14行。


  将部门名从GROUPING SETS中移到GROUP BY中,则语句的含义发生了变化,现在就是统计对于每个部门每个入职时间()
对所有职位进行小计以及对于每个部门每个职位,对入职时间
()进行小计。


1.4.3   CUBEROLLUP作为GROUPING SETS的参数

  GROUPING SETS操作能够接受ROLLUPCUBE作为它的参数, GROUPING SETS操作只对单列分组,而不提供合计的功能,
如果需要GROUPING SETS提供合计的功能,那么可以使用ROLLUPCUBE作为GROUPING SETS的参数,比如改写前面的
GROUPING SETS(a.dname,b.job),提供合计功能:


SELECT a.dname,b.job, SUM(sal) sum_sal

FROM dept a,emp b

WHERE a.deptno = b.deptno

GROUP BY GROUPING SETS(ROLLUP(a.dname),ROLLUP(b.job));

结果如下:

DNAME                        JOB                   SUM_SAL

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

                             CLERK                    3050

                             SALESMAN                 5600

                             PRESIDENT                5000

                             MANAGER                  8275

                             ANALYST                  3000

ACCOUNTING                                            8750

RESEARCH                                              6775

SALES                                                 9400

                                                     24925

                                                     24925


       这条语句产生了
2个合计行,因为ROLLUPCUBE作为GROUPING SETS的参数,则相当于对每个ROLLUPCUBE
操作的UNION ALL。所以上面的语句等价于:

SELECT a.dname,NULL job, SUM(sal) sum_sal

FROM dept a,emp b

WHERE a.deptno = b.deptno

GROUP BY ROLLUP(a.dname)

UNION ALL

SELECT NULL dname,b.job, SUM(sal) sum_sal

FROM dept a,emp b

WHERE a.deptno = b.deptno

GROUP BY  ROLLUP(b.job);


   这样,就很好理解ROLLUPCUBE作为GROUPING SETS所实现的功能了,上面的SQL具有重复合计,
可以使用DISTINCT剔除重复行,另外在扩展分组中有特殊的函数可以使用,后面会说到GROUP_ID函数专门
用来剔除重复分组(注意DISTINCTGROUP_ID实现的功能还是不一样的,见GROUP_ID部分讲解)。
当然,在GROUPING SETS中,ROLLUPCUBE也可以混合使用,而且也能使用其他扩展功能,
如部分分组、复合列分组、连接分组等。ROLLUPCUBE就不能接受GROUPING SETS作为参数了,
ROLLUPCUBE之间互相作为参数也是不可以的。

1.4.4   GROUPING SETS总结

GROUPING SETS很简单,就是分别对单列进行分组,从而统计其他维度的小计,对于GROUPING SETS中无合计,
   Oracle允许ROLLUP,CUBE作为GROUPING SETS的参数,增强了GROUPING SETS分组功能。

现在3个扩展GROUP BYROLLUPCUBEGROUPING SETS已经基本讲完。实际上里面还有很多值得研究的内容,
比如3个扩展GROUP BY之间是否可以转换、如何对结果中的NULL进行判断是否是小计或合计列、如何制作可读性强的报表、
如何对结果显式排序、如何实现更加复杂的需求,比如部分ROLLUP中需要保留合计、
如何简单地剔除某些不必要的行(强大的GROUPING_ID函数使用)等等,在后续的内容中,会逐一介绍。



阅读(983) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~