ORACLE 高级函数应用

2024年 1月 22日 82.0k 0

ORACLE 高级函数应用
--分组函数
1、ROLLUP 
--统计标准分组及相应维度的小计、合计
Oracle的GROUP BY语句除了最基本的语法外,还支持ROLLUP和CUBE语句。如果是ROLLUP(A, B, C)的话,首先会对(A、B、C)进行GROUP BY,然后对(A、B)进行GROUP BY,然后是(A)进行GROUP BY,最后对全表进行GROUP BY操作。如果是GROUP BY CUBE(A, B, C),则首先会对(A、B、C)进行GROUP BY,然后依次是(A、B),(A、C),(A),(B、C),(B),(C),最后对全表进行GROUP BY操作。 grouping_id()可以美化效果:

CALL VPD_PKG.SET_CONTEXT_COMPID('-1');
SELECT A.VPD_COMPID,TO_CHAR(A.TTIME,'MM'),COUNT(*)
   FROM XTBILL2011 A 
   GROUP BY ROLLUP(A.VPD_COMPID,TO_CHAR(A.TTIME,'MM'));
--2)部分rollup分组
--对标准分组后,对a.typeid进行小计
SELECT A.DWDH,A.YEAR,A.TYPEID,COUNT(*)
   FROM XTYWBILL A 
   GROUP BY A.DWDH,A.YEAR,ROLLUP(A.TYPEID);

2、CUBE
--rollup只能对“从右到左递减”,如需要全方位的维度进行统计,需要用到cube函数
--1)
SELECT A.VPD_COMPID,TO_CHAR(A.TTIME,'MM'),COUNT(*)
   FROM XTBILL2011 A 
   GROUP BY CUBE(A.VPD_COMPID,TO_CHAR(A.TTIME,'MM'));
--2) 部分cube:可以去掉合计与某些不需要的小计。 
SELECT A.VPD_COMPID,TO_CHAR(A.TTIME,'MM'),COUNT(*)
   FROM XTBILL2011 A 
   GROUP BY A.VPD_COMPID,CUBE(TO_CHAR(A.TTIME,'MM'));
   
3、GROUPING SETS
--说明:仅关注单列分组,某些维度的小计
--group by grouping sets(a,b,c)相当于group by a,group by b,group by c
--这三组的union all结果
--1)
SELECT A.VPD_COMPID,TO_CHAR(A.TTIME,'MM'),COUNT(*)
   FROM XTBILL2011 A 
   GROUP BY GROUPING SETS(A.VPD_COMPID,TO_CHAR(A.TTIME,'MM'));
--2)部分grouping sets分组
--在group by的基础上进行小计,仅关注小计的情况
SELECT A.DWDH,A.YEAR,A.TYPEID,COUNT(*)
   FROM XTYWBILL A 
   GROUP BY A.DWDH,GROUPING SETS(A.YEAR,A.TYPEID);
SELECT A.VPD_COMPID,TO_CHAR(A.TTIME,'MM'),COUNT(*)
   FROM XTBILL2011 A 
   GROUP BY A.VPD_COMPID,GROUPING SETS(TO_CHAR(A.TTIME,'MM'));

4、CUBE,ROLLUP作为GROUPING SETS的参数
--grouping sets操作只对单列进行分组,而不提供合计的功能,如果需要grouping sets提供合计的功能,
--那么可以使用rollup或cube作为grouping sets的参数,比如下面的语句提供合计功能:
SELECT A.VPD_COMPID,TO_CHAR(A.TTIME,'MM') AS TTIME,COUNT(*)
   FROM XTBILL2011 A 
   GROUP BY GROUPING SETS(ROLLUP(A.VPD_COMPID),ROLLUP(TO_CHAR(A.TTIME,'MM')));
--这条语句产生了两个合计行,因为rollup或cube作为grouping sets的参数,相当于对每个
--rollup与cube操作的union all。所以上面的语句等价于:
SELECT A.VPD_COMPID,NULL AS TTIME,COUNT(*)
   FROM XTBILL2011 A 
   GROUP BY ROLLUP(A.VPD_COMPID)
UNION ALL 
SELECT NULL,TO_CHAR(A.TTIME,'MM'),COUNT(*)
   FROM XTBILL2011 A 
   GROUP BY ROLLUP(TO_CHAR(A.TTIME,'MM'));
   
5、组合列分组简介:
--分组方式: rollup(a,b,c)group by a,b,c; group by a,b; group by null
--分组方式: rollup(a,(b,c))group by a,b,c; group by a; group by null
--分组方式: rollup(a,b),rollup(c)group by a,b,c; group by a,b; group by a,c; group by a; group by c; group by null
--分组方式: rollup(a,b),grouping sets(c)group by a,b,c; group by a,c; group by c 
--分组方式: rollup(a),rollup(b),rollup(c)group by a; group by b; group by c; group by a,b; group by a,c; group by b,c; group by a,b,c; group by null

6、GROUPING函数
--为了区别哪些是小计,grouping函数派上用场了!
SELECT A.VPD_COMPID,TO_CHAR(A.TTIME,'MM'),COUNT(*),GROUPING(A.VPD_COMPID),GROUPING(TO_CHAR(A.TTIME,'MM')),
   DECODE(GROUPING(A.VPD_COMPID),1,'所有单位',A.VPD_COMPID) VPD_COMPID,
   DECODE(TO_CHAR(A.TTIME,'MM'),1,'所有月份',TO_CHAR(A.TTIME,'MM')) TTIME   
   FROM XTBILL2011 A 
   GROUP BY ROLLUP(A.VPD_COMPID,TO_CHAR(A.TTIME,'MM'));
--过滤某些分组结果
SELECT A.VPD_COMPID,TO_CHAR(A.TTIME,'MM'),COUNT(*),GROUPING(A.VPD_COMPID),GROUPING(TO_CHAR(A.TTIME,'MM')),
   DECODE(GROUPING(A.VPD_COMPID),1,'所有单位',A.VPD_COMPID) VPD_COMPID,
   DECODE(TO_CHAR(A.TTIME,'MM'),1,'所有月份',TO_CHAR(A.TTIME,'MM')) TTIME   
   FROM XTBILL2011 A 
   GROUP BY ROLLUP(A.VPD_COMPID,TO_CHAR(A.TTIME,'MM'))
   HAVING GROUPING(A.VPD_COMPID)=1 OR GROUPING(TO_CHAR(A.TTIME,'MM'))=0;

7、GROUPING_ID函数
--可用rollup或cube与grouping_id组合运用,过滤出想要的分组统计信息
SELECT A.VPD_COMPID,TO_CHAR(A.TTIME,'MM'),GROUPING_ID(A.VPD_COMPID,TO_CHAR(A.TTIME,'MM')),COUNT(*)
   FROM XTBILL2011 A 
   GROUP BY CUBE(A.VPD_COMPID,TO_CHAR(A.TTIME,'MM'))
   HAVING GROUPING_ID(A.VPD_COMPID,TO_CHAR(A.TTIME,'MM'))=2;  --1,2,3,0
--GROUPING_ID(a,b,c)过滤分组结果
分组级别   位向量    GROUPING_ID结果
a,b,C      0 0 0     0 
a,B        0 0 1     1
A          0 1 1     3
汇总       1 1 1     7

8、GROUP_ID函数
--判断重复的分组
SELECT A.VPD_COMPID,TO_CHAR(A.TTIME,'MM') AS TTIME,GROUP_ID() ID,COUNT(*)
   FROM XTBILL2011 A 
   GROUP BY GROUPING SETS(ROLLUP(A.VPD_COMPID),ROLLUP(TO_CHAR(A.TTIME,'MM')))
  -- HAVING GROUP_ID()=0;
  
9、实例应用说明:
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 TO_DATE('2010-05-01','YYYY-MM-DD')+LEVEL,
  TRUNC(DBMS_RANDOM.value*1000),
  'book1',100+LEVEL,LEVEL 
  FROM DUAL
  CONNECT BY LEVEL

相关文章

Oracle如何使用授予和撤销权限的语法和示例
Awesome Project: 探索 MatrixOrigin 云原生分布式数据库
下载丨66页PDF,云和恩墨技术通讯(2024年7月刊)
社区版oceanbase安装
Oracle 导出CSV工具-sqluldr2
ETL数据集成丨快速将MySQL数据迁移至Doris数据库

发布评论