Chinaunix首页 | 论坛 | 博客
  • 博客访问: 1370951
  • 博文数量: 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-07 21:03:16

接PART6:

1.6.2 GROUP_ID函数

GROUP_ID函数也提供了很好的功能,它无参数,因为扩展GROUP BY分组允许多种复杂分组操作,如部分分组,重复列分组,连接分组等,有时候为了实现复杂的报表功能,会有重复分组统计出现,而GROUP_ID函数就可以区分重复分组结果,第1次出现为0,以后每次出现增1GROUP_IDSELECT中出现意义不大,常使用在HAVING中达到过滤重复统计的目的。

  现在就用GROUP_ID函数解决1.4.3中的遗留问题,1.4.3使用的是DISTINCT剔除重复行,在扩展分组中,当然最好使用GROUP_ID了,因为GROUP_ID函数就是Oracle设计出来专门用于剔除重复分组结果的,注意DISTINCT是剔除重复行,而不是按重复分组级别剔除的,GROUP_ID则是按重复分组级别来区分的,所以用DISTINCT有时候可能不对,但是用GROUP_ID就没有这个问题了。

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

FROM dept a,emp b

WHERE a.deptno = b.deptno

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

  显示结果如下:

DNAME                        JOB                   SUM_SAL         GI

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

                             CLERK                    3050          0

                             SALESMAN                 5600          0

                             PRESIDENT                5000          0

                             MANAGER                  8275          0

                             ANALYST                  3000          0

ACCOUNTING                                            8750          0

RESEARCH                                              6775          0

SALES                                                 9400          0

                                                     24925          0

                                                     24925          1


已选择10行。

看最后两行,第1行的GROUP_ID结果为0,第2次为1,为了获得非重复统计,只要GROUP_ID()=0即可。

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))

HAVING GROUP_ID() = 0;

最终剔除重复统计的结果:

DNAME                        JOB                   SUM_SAL

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

                             CLERK                    3050

                             SALESMAN                 5600

                             PRESIDENT                5000

                             MANAGER                  8275

                             ANALYST                  3000

ACCOUNTING                                            8750

RESEARCH                                              6775

SALES                                                 9400

                                                     24925


已选择9行。

  看看DISTINCTGROUP_ID的区别:

WITH t

AS

(

SELECT 1 s,'a' name FROM DUAL

UNION ALL

SELECT 1 s,NULL name FROM DUAL

UNION ALL

SELECT 2 s, NULL name FROM DUAL

UNION ALL

SELECT 1 s,'a' name FROM DUAL

)

SELECT s,name,COUNT(1),GROUP_ID()

FROM

t

GROUP BY ROLLUP(s,name);


  结果为:

        S NA   COUNT(1) GROUP_ID()

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

        1             1          0

        1 a           2          0

        1             3          0

        2             1          0

        2             1          0

                      4          0


已选择6行。

  看GROUP_ID都是0,说明无重复分组,如果使用DISTINCT,那么不应该剔除的会剔除,所以还是使用GROUP_ID准确。

1.6.4 扩展GROUP BY函数总结

  GROUPINGGROUPING_IDGROUP_ID在扩展GROUP BY中的作用很大,用于格式化报表,过滤结果行,排序等很有用,特别是很多扩展分组很复杂,那么使用扩展GROUP BY函数进行一些过滤工作就很有必要了,ROLLUPCUBEGROUPING SETS的结果不管是否有默认排序,都是不准确的,需要手动进行有意义的排序,所以必然要使用到扩展GROUP BY函数。到这里,基本的扩展GROUP BY功能已经讲完,扩展GROUP BY功能强大,而且变化万千,所以深刻理解其中的内容,才能灵活运用。

注意:GROUPINGGROUPING_ID函数是没有组合列的,也就是里面不能加括号,比如GROUPING_ID((a,b),c)是错的,而且它们要来自于分组列。

1.7  扩展分组综合实例

1.7节会结合具体实例讨论扩展GROUP BY的应用,具体见实例。

这个例子是简化的例子,一个简单的订单报表统计,只是为了说明扩展GROUP BY的使用。

DROP TABLE t;

CREATE TABLE t

(

--订购日期

order_date DATE,

--订购号

order_no  NUMBER,

--订购书籍

order_book VARCHAR2(10),

--订单总金额

order_fee NUMBER,

--订单明细数目

order_num NUMBER

);

--测试数据

INSERT INTO t

SELECT DATE '2010-5-1'+LEVEL,

TRUNC(DBMS_RANDOM.VALUE *1000),

'book1',100*LEVEL,LEVEL

FROM DUAL

CONNECT BY LEVEL<5;


INSERT INTO t

SELECT DATE '2010-6-1'+LEVEL,

TRUNC(DBMS_RANDOM.VALUE *1000),

'book2',200*LEVEL,LEVEL

FROM DUAL

CONNECT BY LEVEL<5;

COMMIT;

 
  表数据如下:


SCOTT>ALTER SESSION SET NLS_DATE_FORMAT='YYYY-MM-DD';


会话已更改。


SCOTT>SELECT * FROM t;


ORDER_DATE   ORDER_NO ORDER_BOOK            ORDER_FEE  ORDER_NUM

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

2010-05-02        315 book1                       100          1

2010-05-03        483 book1                       200          2

2010-05-04        890 book1                       300          3

2010-05-05        604 book1                       400          4

2010-06-02        746 book2                       200          1

2010-06-03        800 book2                       400          2

2010-06-04        758 book2                       600          3

2010-06-05         91 book2                       800          4


已选择8行。


要求每组order_book内,按日期升序排列(order_no排序不管),小计在后,合计最后。效果如下表所示:


ORDER_DATE

ORDER_NO

ORDER_BOOK

SUM_ORDER_FEE

SUM_ORDER_NUM

2010-05-02 

315

book1

100         

1

2010-05-03 

483

book1

200

2

2010-05-04 

890

book1

300

3

2010-05-05

604

book1

400

4

book1小计

1000

10

2010-06-02

746

book2

200

1

2010-06-03 

800

book2

400

2

2010-06-04 

758

book2

600

3

2010-06-05

91

book2

800

4

book2小计

2000

10

合计

3000

20


  前3列是原始表的列,后两列是聚合结果列。通过分析上面的结果可以看出,使用ROLLUP组合列分组很容易实现,比如对于book1来说,前4行就是标准分组,第5行小计可以看作是GROUP BY order_book,然后计算横跨order_date,order_no列的小计,最后通过DECODE函数+GROUPING_ID函数实现报表格式化,最终结果为:

SELECT DECODE( GROUPING_ID(order_date,order_no,order_book),6,order_book||'小计',

            7,'合计',

            TO_CHAR(order_date,'yyyy-mm-dd')

            ) order_date1,

   order_no,

   DECODE(GROUPING_ID(order_date,order_no,order_book),6,NULL,order_book) order_book1,

  SUM(order_fee) order_fee,

  SUM(order_num) order_num

FROM t

GROUP BY ROLLUP(order_book,(order_date,order_no))

ORDER BY  order_book,order_date;


  GROUP BY ROLLUP(order_book,(order_date,order_no))实现了先标准分组,然后对每个order_book计算横跨order_dateorder_no的小计,对于GROUPING_ID(order_date,order_no,order_book)可以区别分组级别,等于6的为小计,等于7的为合计,通过DECODE函数格式化。
注意:上面的SQL1列取别名为order_date1,2列为order_book1,如果不取别名则会覆盖表t的列,那么ORDER BY order_book,order_date排序结果就不符合要求了,或在ORDER BY中的排序列加前缀t,比如改为ORDER BY t.order_book,t.order_date也可)
上面的SQL输出结果为:


ORDER_DATE1                        ORDER_NO ORDER_BOOK1           ORDER_FEE  ORDER_NUM

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

2010-05-02                              315 book1                       100          1

2010-05-03                              483 book1                       200          2

2010-05-04                              890 book1                       300          3

2010-05-05                              604 book1                       400          4

book1小计                                                              1000         10

2010-06-02                              746 book2                       200          1

2010-06-03                              800 book2                       400          2

2010-06-04                              758 book2                       600          3

2010-06-05                               91 book2                       800          4

book2小计                                                              2000         10

合计                                                                   3000         20


已选择11行。


  结果符合要求,当然,有时候不加ORDER BY也可以默认是上面的排序,但是不准确,想要排序必须加ORDER BY。本章内容已经讲完,里面还有很多细节,还需要读者仔细品味。

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