ORACLE 高级函数应用二

张兴康 · 发表于 2020-6-30 14:12:18

--二、分析函数
--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;