ORACLE 高级函数应用_建站学-个人建站指南,网页制作,网站设计,网站制作教程

--声明：本培训涉及的业务不一定准确，只为了说明函数的功能：

　　ORACLE 高级函数应用

　　--分组函数

　　1、ROLLUP

　　--统计标准分组及相应维度的小计、合计

　　--从右到左递减：group by rollup(a,b,c) : a,b,c ; a,b（对c的小计） ; a（对b,c的小计） ; 总计

　　--1)

　　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<5;

　　INSERT INTO T

　　SELECT TO_DATE('2010-06-01','YYYY-MM-DD')+LEVEL,

　　TRUNC(DBMS_RANDOM.value*1000),

　　'book2',200+LEVEL,LEVEL

　　FROM DUAL

　　CONNECT BY LEVEL<5;

　　--要求：每组order_book内，按日期升序排列（order_no排序不管），常规分组在前，小计在后，合计最后。

　　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;

　　--二、分析函数

　　--over()开窗函数按compid分组，按ttime排序，对记录的一个递增统计处理

　　SELECT COUNT(SUMMONEY) OVER(PARTITION BY COMPID ORDER BY TTIME) CNT_SAL,

　　SUm(SUMMONEY) OVER(PARTITION BY COMPID ORDER BY TTIME) SUM_SAL,

　　MAX(SUMMONEY) OVER(PARTITION BY COMPID ORDER BY TTIME) MAX_SAL,

　　MIN(SUMMONEY) OVER(PARTITION BY COMPID ORDER BY TTIME) MIN_SAL,

　　AVG(SUMMONEY) OVER(PARTITION BY COMPID ORDER BY TTIME) AVG_SAL

　　FROM XTYWBILL WHERE COMPID IN ('2601','2602');

　　--1、rows,range改变窗口范围的函数：

　　--窗口范围为该分区的第一行和该分区的最后一行

　　--sum(summoney) over(partition by compid order by ttime

　　-- rows between unbounded preceding and unbounded following) sum_1

　　--窗口范围为该分区内大于本记录ttime-365天，并且截止到当前记录的所有的金额累计

　　--sum(summoney) over(partition by compid order by ttime

　　-- range 365/*value_expr*/ preceding) sum_2

　　--2、keep的用法

　　SELECT BILLID,VPD_COMPID,TTIME,ZJE,

　　DENSE_RANK() OVER(PARTITION BY VPD_COMPID ORDER BY TTIME) DENSE_RANK,

　　MIN(ZJE) KEEP(DENSE_RANK FIRST ORDER BY TTIME) OVER(PARTITION BY VPD_COMPID) MIN_FIRST,

　　MIN(ZJE) KEEP(DENSE_RANK LAST ORDER BY TTIME) OVER(PARTITION BY VPD_COMPID) MIN_LAST,

　　MAX(ZJE) KEEP(DENSE_RANK LAST ORDER BY TTIME) OVER(PARTITION BY VPD_COMPID) MAX_LAST

　　FROM XTBILL2011;

　　--min(zje) dense_rank first order by ttime 是从时间最早的记录中找到最小的金额.

　　--max(zje) dense_rank last order by ttime 是从时间最晚的记录中找到最大的金额.

　　--注意:keep只能与dense_rank first\dense_rank last搭配使用.

　　--3、统计函数：

　　SELECT BILLID,VPD_COMPID,TTIME,ZJE,

　　MIN(ZJE) OVER(PARTITION BY VPD_COMPID ORDER BY TTIME) AS COMP_MIN,

　　MAX(ZJE) OVER(PARTITION BY VPD_COMPID ORDER BY TTIME) AS COMP_MAX,

　　AVG(ZJE) OVER(PARTITION BY VPD_COMPID ORDER BY TTIME) AS COMP_AVG,

　　SUM(ZJE) OVER(PARTITION BY VPD_COMPID ORDER BY TTIME) AS COMP_SUM,

　　COUNT(*) OVER(PARTITION BY VPD_COMPID ORDER BY ZJE) AS COUNT_BY,

　　COUNT(*) OVER(PARTITION BY VPD_COMPID ORDER BY ZJE RANGE BETWEEN 50 PRECEDING AND 150 FOLLOWING) AS BY_RANGE

　　FROM XTBILL2011;

　　--4、排序函数：

　　--问：rank()\dense_rank()区别：

　　RANK() DENSE_RANK()

　　1       1

　　1       1

　　3       2

　　--LOG(zje,1,0) --找到前面记录的值，1:表示找前一个的值，0：表示找不到默认的值

　　--LEAD(zje,1,) --找到后面记录的值，1：表示找前一个的值，0：表示找不到默认的值

　　--first_value() --取出该窗口的第一个值。

　　--last_value() --取该窗口最后一个值，注意是按渐进顺序来的，都是这样！

　　--null值排最后,作为最大值列

　　SELECT BILLID,VPD_COMPID,TTIME,ZJE,

　　/*RANK() OVER(PARTITION BY VPD_COMPID ORDER BY ZJE) AS RANK,

　　DENSE_RANK() OVER(PARTITION BY VPD_COMPID ORDER BY ZJE) AS DENSE_RANK,

　　MIN(ZJE) KEEP(DENSE_RANK FIRST ORDER BY TTIME) OVER(PARTITION BY VPD_COMPID) WORST,

　　MAX(ZJE) KEEP(DENSE_RANK LAST ORDER BY TTIME) OVER(PARTITION BY VPD_COMPID) BEST,

　　LAG(ZJE,1,0) OVER(ORDER BY TTIME) AS PREV_ZJE,

　　LEAD(ZJE,1,0) OVER(ORDER BY TTIME) AS NEXT_SAL,*/

　　FIRST_VALUE(BILLID) OVER(PARTITION BY VPD_COMPID ORDER BY ZJE) AS FIRST_VALUE_ASC,

　　FIRST_VALUE(BILLID) OVER(PARTITION BY VPD_COMPID ORDER BY NVL(ZJE,0) DESC) AS FIRST_VALUE_DESC,

　　LAST_VALUE(BILLID) OVER(PARTITION BY VPD_COMPID ORDER BY NVL(ZJE,0)) AS LAST_VALUE_ASC,

　　LAST_VALUE(BILLID) OVER(PARTITION BY VPD_COMPID ORDER BY ZJE DESC) AS LAST_VALUE_DESC,

　　ROW_NUMBER() OVER(PARTITION BY VPD_COMPID ORDER BY BILLID) AS ROW_NUMBER

　　FROM XTBILL2011 ORDER BY VPD_COMPID,ZJE DESC;

　　--5、ratio_to_report() 函数

　　--记录，所占的百分比

　　SELECT BILLID,VPD_COMPID,TTIME,ZJE,

　　RATIO_TO_REPORT(ZJE) OVER()

　　FROM XTBILL2011;

　　三、树查询相关的函数

　　--1、ORDER SIBLINGS BY 先按上级的compid排序，再按下级的compid排序。

　　--2、level与rpad\lpad组合应用，构造可通的层级

　　--3、CONNECT_BY_ISLEAF 判断当前结点是否为叶子结点，0表示为非叶子结点，1则表示为叶子结点

　　--（如果不存在下级节点就是路子节点）

　　--4、CONNECT_BY_ROOT 列出树的根结点对应的字段值，需与字段值组合使用

　　--5、SYS_CONNECT_BY_PATH 函数就是从start with 开始的地方开始遍历，并记下其遍历到的节点，

　　-- start with 开始的地方被视为根节点，将遍历到的路径根据函数中的分隔符，组成一个新的

　　-- 字符串。

　　SELECT LEVEL,LPAD(' ',(LEVEL-1)*3)||A.COMPID,CONNECT_BY_ISLEAF,

　　CONNECT_BY_ROOT COMPNAME ROOT_COMPNAME,CONNECT_BY_ISCYCLE "CYCLE",

　　SYS_CONNECT_BY_PATH(COMPID,'/') FORMAT_COMPID,

　　SYS_CONNECT_BY_PATH(COMPNAME,'->') FORMAT_COMPNAME,

　　A.*

　　FROM COMPANYINFO A

　　START WITH A.COMPID = '0000'

　　CONNECT BY NOCYCLE PRIOR A.COMPID = A.PCOMPID

　　ORDER SIBLINGS BY COMPID;

　　SELECT * FROM COMPANYINFO WHERE COMPID='0000' FOR UPDATE;

　　--with关键字与树查询组合应用例子：

　　--查询与compid=’0001‘的同级节点

　　WITH COMPANYINFO_TMP AS

　　(SELECT LEVEL LEV,LPAD(' ',(LEVEL-1)*3)||A.COMPID,CONNECT_BY_ISLEAF,

　　A.*

　　FROM COMPANYINFO A

　　START WITH A.COMPID = '0000'

　　CONNECT BY PRIOR A.COMPID = A.PCOMPID

　　)

　　SELECT * FROM COMPANYINFO_TMP

　　WHERE LEV=(SELECT LEV FROM COMPANYINFO_TMP

　　WHERE COMPID='0001') ORDER BY COMPID;

　　四、其它：

　　NVL(a,b);    --若a为null，则返回b,否则返回a(即不转换)

　　NVL2(a,b,c); --不管a是不是Null都要转换，如果为空，返回c,否则返回b.

　　NULLIF(a,b); --判断a与b是否相等，若相等则返回null,不支持类型自动转换。

　　COALESC(a,b,...,n); --从左到右返回第1个非null值，若所有的列表元素都为null,则返回null.它有短路计算功能，

　　--比如，a为null,b非null,则返回b的值，不再计算下一个值。

　　DECODE\SIGN;

　　--应用例子：

　　WITH T AS

　　(SELECT LEVEL ID

　　FROM DUAL

　　CONNECT BY LEVEL<10

　　)

　　SELECT ID,

　　DECODE(-1,SIGN(ID-5),'low',

　　SIGN(ID-8),'mid',

　　'high') RESULTS

　　FROM T;

　　--decode的常用应用之一是实现固定行转列：

　　--下面的一decode的综合应用：查询借货不平衡的数据

　　CALL VPD_PKG.SET_CONTEXT_COMPID('-1') ;

　　SELECT BILLID,SUM(DECODE(BZJD,1,TMONEYF,0)) AS DFJE,SUM(DECODE(BZJD,1,0,TMONEYF)) AS JFJE

　　FROM (SELECT A.BILLID,DECODE(A.CXBZ,1,1-A.BZJD,A.BZJD) AS BZJD,SUM(DECODE(A.CXBZ,1,-1*A.TMONEYF,A.TMONEYF)) AS TMONEYF

　　FROM XTBILLMX2010 A,XTBILL2010 B

　　WHERE B.BILLID=A.BILLID AND B.BZWC=1

　　GROUP BY A.BILLID,DECODE(A.CXBZ,1,1-A.BZJD,A.BZJD))

　　GROUP BY BILLID

　　HAVING SUM(DECODE(BZJD,1,TMONEYF,0))<>SUM(DECODE(BZJD,1,0,TMONEYF)); (责任编辑：admin)

搜索

热门标签:

ORACLE 高级函数应用