分析函数的应用(更新版)

最新推荐文章于 2024-05-29 11:11:48 发布

congqian1312

最新推荐文章于 2024-05-29 11:11:48 发布

阅读量116

点赞数

前言:
1. 对原文重新排版,增强可读性
2. 文中列举的例子都在scott/tiger用户下测试通过,增强可操作性，易验证。
3. 继续增加:分析函数在美同统计系统中的应用，分析函数性能比较
4. 欢迎大家批评指正.

[@more@]

概述:
分析函数（oracle8.1.6以后版本支持），为解决"Calculate a running total", "Find percentages within a group", "Top-N queries", "Compute a moving average"等问题而设计的。
标准的PL/SQL可以解决大部分问题，然而性能不尽人意。分析函数就是标准SQL的语言插件，不仅编码简单，而且性能比纯SQL和PL/SQL好。分析函数已经获得ANSI SQL机构鉴定，增添到标准的SQL规范里。

分析函数如何工作的呢？：
分析函数用来计算一组数据的集合的值。不同于每组返回多行的集合函数。一组多行数据的集合称为"窗口"，可以用分析子句实现界定。
针对每行数据而言，定义了一个多行的"移动"的窗口.在计算"当前行"数据时候，窗口决定了计算多行数据的一个范围，窗口的尺寸由物理的行数或逻辑分割点（如：时间）决定。
除了order by 语句外，分析函数是在查询语句中后最后执行的语句，
所有的表连接(joins)，group by,having 语句都是在分析函数之前执行。故分析函数只出现在select和order by语句中
。
语法:

Analytic-Function(,,...)
OVER (

)

分析函数:（黑体函数本文有例子解释）

AVG, CORR, COVAR_POP, COVAR_SAMP, COUNT, CUME_DIST, DENSE_RANK, FIRST, FIRST_VALUE, LAG, LAST,LAST_VALUE, LEAD, MAX, MIN, NTILE, PERCENT_RANK, PERCENTILE_CONT, PERCENTILE_DISC, RANK, RATIO_TO_REPORT, STDDEV, STDDEV_POP, STDDEV_SAMP, SUM, VAR_POP, VAR_SAMP, VARIANCE.CUBE,ROLLUP

参数：
分析函数需要0到3的参数

partition by语句：
根据partition by条件，逻辑上把一组结果集分为N组。关键字"partition" 和 "group"常常同时出现。分析函数独立应用于每组。

Order by 语句：
在每组(分区)排序,作用于分析函数的结果集.
Order by 后可以接以下语句：
Asc|Desc 升序/降序
Nulls First|Nulls Last 表示空值在排序中，置于开始还是最后

Windowsing-clause语句：
Between … and 指定窗口范围的起点与终点
Unbonded preceding 表示窗口的起点就是分区的第1行
Unbonded following 表示窗口的终点就是分区的最后1行
Current row 当前行
Rows 表示行数范围
Range 表示字段的范围

举例说明:

例子1: Calculate a running Total(累加)

SELECT ename "Ename", deptno "Deptno", sal "Sal",
SUM(sal) OVER (ORDER BY deptno, ename) "Running Total",
--根据deptno,name排序后的结果集累加
SUM(SAL) OVER (PARTITION BY deptno ORDER BY ename) "Dept Total",
--先deptno分区,再ename排序后结果集,然后累加
ROW_NUMBER() OVER (PARTITION BY deptno ORDER BY ENAME) "Seq"
FROM emp --行数统计
ORDER BY deptno, ename

Ename Deptno Sal Running Tota Dept Total Seq
CLARK 10 2450.00 2450 2450 1
KING 10 5000.00 7450 7450 2
MILLER 10 1300.00 8750 8750 3
ADAMS 20 1100.00 9850 1100 1
FORD 20 3000.00 12850 4100 2
JONES 20 2975.00 15825 7075 3
SCOTT 20 3000.00 18825 10075 4
SMITH 20 800.00 19625 10875 5
ALLEN 30 1600.00 21225 1600 1
BLAKE 30 2850.00 24075 4450 2
JAMES 30 950.00 25025 5400 3
MARTIN 30 1250.00 26275 6650 4
TURNER 30 1500.00 27775 8150 5
WARD 30 1250.00 29025 9400 6

例子说明
1.SUM(sal) OVER (ORDER BY deptno, ename) 表示针对整个查询逐个累加员工薪水
2.SUM(SAL) OVER (PARTITION BY deptno ORDER BY ename)表示针对公司每个部门逐个累加各部门内部员工的薪水
3.ROW_NUMBER() OVER (PARTITION BY deptno ORDER BY ENAME)表示顺序显示每个部门员工的行号

例子2:Top-N Queries(前N位查询--没有并列的名次）

SELECT * FROM (
SELECT deptno, ename, sal, ROW_NUMBER()
OVER (
PARTITION BY deptno ORDER BY sal DESC
) Top3 FROM emp
)
WHERE Top3 <= 3

DEPTNO ENAME SAL TOP3
1 10 KING 5000.00 1
2 10 CLARK 2450.00 2
3 10 MILLER 1300.00 3
4 20 SCOTT 3000.00 1
5 20 FORD 3000.00 2 -> 只是顺序的往下排，没有并列的名次
6 20 JONES 2975.00 3
7 30 BLAKE 2850.00 1
8 30 ALLEN 1600.00 2
9 30 TURNER 1500.00 3

例子说明:
查询各个部门薪水排在前3名的员工的资料（没有并列的名次）

例子3：Top-N Queries(前N位查询--产生并列的名次)

3-1: 两个相同的数据,那么后面的数据就会不跳过这个排名,
SELECT * FROM (
SELECT deptno, ename, sal,
DENSE_RANK()
OVER (
PARTITION BY deptno ORDER BY sal desc
) TopN FROM emp
)
WHERE TopN <= 3
ORDER BY deptno, sal DESC

DEPTNO ENAME SAL TOPN
1 10 KING 5000.00 1
2 10 CLARK 2450.00 2
3 10 MILLER 1300.00 3
4 20 SCOTT 3000.00 1--〉
5 20 FORD 3000.00 1--〉如果两个员工的薪水一样，
6 20 JONES 2975.00 2 产生并列的名次
7 20 ADAMS 1100.00 3
8 30 BLAKE 2850.00 1
9 30 ALLEN 1600.00 2
10 30 TURNER 1500.00 3

例子说明:
查询各个部门薪水排在前名的员工的资料（并列的名次）

3-2: 两个相同的数据,那么后面的数据就会直接跳过这个排名,
SELECT * FROM (
SELECT deptno, ename, sal, rank()
OVER (
PARTITION BY deptno ORDER BY sal DESC
) Top3 FROM emp
)
WHERE Top3 <= 3
DEPTNO ENAME SAL TOP3
10 KING 5000.00 1
10 CLARK 2450.00 2
10 MILLER 1300.00 3
20 SCOTT 3000.00 1
20 FORD 3000.00 1
20 JONES 2975.00 3->跳过2的排名
30 BLAKE 2850.00 1
30 ALLEN 1600.00 2
30 TURNER 1500.00 3

例子4：窗口---移动累加

SELECT deptno "Deptno", ename "Ename", sal "Sal",
SUM(SAL)
OVER (PARTITION BY deptno
ORDER BY ename
ROWS 2 PRECEDING) "Sliding Total"
FROM emp
ORDER BY deptno, ename

Deptno Ename Sal Sliding Total
1 10 CLARK 2450.00 2450
2 10 KING 5000.00 7450
3 10 MILLER 1300.00 8750
4 20 ADAMS 1100.00 1100
5 20 FORD 3000.00 4100
6 20 JONES 2975.00 7075
7 20 SCOTT 3000.00 8975 --〉（8975 = 3000+2975+3000)
8 20 SMITH 800.00 6775
9 30 ALLEN 1600.00 1600
10 30 BLAKE 2850.00 4450
11 30 JAMES 950.00 5400
12 30 MARTIN 1250.00 5050
13 30 TURNER 1500.00 3700
14 30 WARD 1250.00 4000

例子说明
sum(sal) over (partition by deptno order by ename rows 2 preceding)
查询的时候,累加各个部门的当前员工前2位员工的工资。

例子5：窗口--范围(根据时间)内累加
SELECT ename, hiredate, hiredate-100 hiredate_pre,
COUNT(*)
OVER (
ORDER BY hiredate ASC
RANGE 100 PRECEDING
) cnt
FROM emp
ORDER BY hiredate ASC

ENAME HIREDATE HIREDATE_PRE CNT
1 SMITH 1980-12-17 1980-9-8
2 ALLEN 1981-2-20 1980-11-12 2
3 WARD 1981-2-22 1980-11-14 3
4 JONES 1981-4-2 1980-12-23 3
5 BLAKE 1981-5-1 1981-1-21 4
6 CLARK 1981-6-9 1981-3-1 3 -> 1981-3-17 TURNER 1981-9-8 1981-5-31 2 有3个员工的雇佣时间在这个区间
8 MARTIN 1981-9-28 1981-6-20 2
9 KING 1981-11-17 1981-8-9 3
10 JAMES 1981-12-3 1981-8-25 5
11 FORD 1981-12-3 1981-8-25 5
12 MILLER 1982-1-23 1981-10-15 4
13 SCOTT 1987-4-19 1987-1-9 1
14 ADAMS 1987-5-23 1987-2-12 2

例子6：窗口--范围（根据时间）内平均数

SELECT ename, hiredate, sal,
AVG(sal)
OVER (
ORDER BY hiredate ASC
RANGE 100 PRECEDING
) avg_sal
FROM emp
ORDER BY hiredate ASC

ENAME HIREDATE SAL AVG_SAL
1 SMITH 1980-12-17 800.00 800
2 ALLEN 1981-2-20 1600.00 1200
3 WARD 1981-2-22 1250.00 1216.
4 JONES 1981-4-2 2975.00 1941.
5 BLAKE 1981-5-1 2850.00 2168.
6 CLARK 1981-6-9 2450.00 2758. --&gt 100天以内即1981-3-17 TURNER 1981-9-8 1500.00 1975 员工工资的平均数 2758=(2975+2850+2450)/3
8 MARTIN 1981-9-28 1250.00 1375
9 KING 1981-11-17 5000.00 2583
10 JAMES 1981-12-3 950.00 2340
11 FORD 1981-12-3 3000.00 2340
12 MILLER 1982-1-23 1300.00 2562
13 SCOTT 1987-4-19 3000.00 3000
14 ADAMS 1987-5-23 1100.00 2050

例子7：窗口--范围（根据行数）的平均数

SELECT ename, hiredate, sal,
round(AVG(sal)
OVER (ORDER BY hiredate ASC ROWS 5 PRECEDING)) AvgAsc,
COUNT(*)
OVER (ORDER BY hiredate ASC ROWS 5 PRECEDING) CntAsc,
round(AVG(sal)
OVER (ORDER BY hiredate DESC ROWS 5 PRECEDING) )AvgDes,
COUNT(*)
OVER (ORDER BY hiredate DESC ROWS 5 PRECEDING) CntDes
FROM emp
ORDER BY hiredate

1 SMITH 1980-12-17 800.00 800 1 1988 6
2 ALLEN 1981-2-20 1600.00 1200 2 2104 6
3 WARD 1981-2-22 1250.00 1217 3 2046 6
4 JONES 1981-4-2 2975.00 1656 4 2671 6
5 BLAKE 1981-5-1 2850.00 1895 5 2675 6
6 CLARK 1981-6-9 2450.00 1988 6 2358 6
注：1988=(800+1600+1250+2975+2850+2450)/6
7 TURNER 1981-9-8 1500.00 2104 6 2167 6
8 MARTIN 1981-9-28 1250.00 2046 6 2417 6
9 KING 1981-11-17 5000.00 2671 6 2392 6
10 JAMES 1981-12-3 950.00 2333 6 1588 4
11 FORD 1981-12-3 3000.00 2358 6 1870 5
12 MILLER 1982-1-23 1300.00 2167 6 1800 3
13 SCOTT 1987-4-19 3000.00 2417 6 2050 2
14 ADAMS 1987-5-23 1100.00 2392 6 1100 1

例子说明：
1.round(AVG(sal) OVER (ORDER BY hiredate ASC ROWS 5 PRECEDING)
当前行再向前(上）推5行的平均数(共6行),5行以内以所在的实际行数平均.
2.round(AVG(sal)OVER (ORDER BY hiredate DESC ROWS 5 PRECEDING) )
当前行再向后(下)推5行的平均数(共6行),5行以内以所在的实际行数平均.

例子8：Lag and lead分析函数
SELECT deptno, ename, hiredate,
LAG(hiredate,1,NULL)
OVER (PARTITION BY deptno
ORDER BY hiredate, ename) last_hire,
hiredate - LAG(hiredate,1,NULL)
OVER (PARTITION BY deptno
ORDER BY hiredate, ename) days_last,
LEAD(hiredate,1,NULL)
OVER (PARTITION BY deptno
ORDER BY hiredate, ename) next_hire,
LEAD(hiredate,1,NULL)
OVER (PARTITION BY deptno
ORDER BY hiredate, ename) - hiredate days_next
FROM emp
ORDER BY deptno, hiredate

DEPTNO ENAME HIREDATE LAST_HIRE DAYS_LAST NEXT_HIRE DAYS_NEXT
10 CLARK 1981-6-9 1981-11-17 161
10 KING 1981-11-17 1981-6-9 161 1982-1-23 67
10 MILLER 1982-1-23 1981-11-17 67
20 SMITH 1980-12-17 1981-4-2 106
20 JONES 1981-4-2 1980-12-17 106 1981-12-3 245
20 FORD 1981-12-3 1981-4-2 245 1987-4-19 1963
20 SCOTT 1987-4-19 1981-12-3 1963 1987-5-23 34
20 ADAMS 1987-5-23 1987-4-19 34
30 ALLEN 1981-2-20 1981-2-22 2
30 WARD 1981-2-22 1981-2-20 2 1981-5-1 68
30 BLAKE 1981-5-1 1981-2-22 68 1981-9-8 130
30 TURNER 1981-9-8 1981-5-1 130 1981-9-28 20
30 MARTIN 1981-9-28 1981-9-8 20 1981-12-3 66
30 JAMES 1981-12-3 1981-9-28 66

例子说明：
取出上一个员工和下一个员工的雇佣时间放在当前员工的同一行(有点列行互换的意思)

例子9:first_value

SELECT deptno, ename, sal,
FIRST_VALUE(ename)
OVER (PARTITION BY deptno
ORDER BY sal ASC) AS MIN_SAL_HAS
FROM emp
ORDER BY deptno, ename

DEPTNO ENAME SAL MIN_SAL_HAS
10 CLARK 2450.00 MILLER
10 KING 5000.00 MILLER
10 MILLER 1300.00 MILLER->MILLER是部门10薪水最低的员工
20 ADAMS 1100.00 SMITH
20 FORD 3000.00 SMITH
20 JONES 2975.00 SMITH
20 SCOTT 3000.00 SMITH
20 SMITH 800.00 SMITH
30 ALLEN 1600.00 JAMES
30 BLAKE 2850.00 JAMES
30 JAMES 950.00 JAMES
30 MARTIN 1250.00 JAMES
30 TURNER 1500.00 JAMES
30 WARD 1250.00 JAMES

例子10：rollup，cube,grouping 汇总函数
10-1：
SELECT deptno,SUM(sal) FROM emp
GROUP BY ROLLUP(deptno)
DEPTNO SUM(SAL)
10 8750
20 10875
30 9400
29025 ----各个部门合计的结果

10-2：统计各部门员工的薪水情况，并且汇总各部门员工的
薪水
SELECT deptno,ename,SUM(sal) FROM emp
GROUP BY ROLLUP(deptno,ename)
10 KING 5000
10 CLARK 2450
10 MILLER 1300
10 8750 ---累加当前部门(10)员工的薪水
20 FORD 3000
20 ADAMS 1100
20 JONES 2975
20 SCOTT 3000
20 SMITH 800
20 10875
30 WARD 1250
30 ALLEN 1600
30 BLAKE 2850
30 JAMES 950
30 MARTIN 1250
30 TURNER 1500
30 9400
10-3:统计各部门每个职位的薪水情况
SELECT deptno,job,SUM(sal) FROM emp
GROUP BY CUBE(deptno,job)
ORDER BY deptno,job NULLS LAST

DEPTNO JOB SUM_TOTAL
10 CLERK 1300
10 MANAGER 2450
10 PRESIDENT 5000
10 8750-〉汇总部门所有职位的薪水
20 ANALYST 6000
20 CLERK 1900
20 MANAGER 2975
20 10875
30 CLERK 950
30 MANAGER 2850
30 SALESMAN 5600
30 9400
ANALYST 6000-〉汇总该职位在公司所有部门薪水
CLERK 4150
MANAGER 8275
PRESIDENT 5000
SALESMAN 5600
29025->汇总所有部门所用职位的薪水

10-4:与10-3等价.用到了gouping函数
grouping函数介绍
如果当前的汇总记录是利用该字段得出的,grouping函数就会返回1,否则返回0
SELECT decode(GROUPING(deptno),1,'All Dept',to_char(deptno)) deptno,
decode(GROUPING(job),1,'ALL Job',job) job,
SUM(sal) sum_t
FROM emp
GROUP BY CUBE(deptno,job)
ORDER BY deptno,job NULLS LAST

DEPTNO JOB SUM_T
10 ALL Job 8750
10 CLERK 1300
10 MANAGER 2450
10 PRESIDENT 5000
20 ALL Job 10875
20 ANALYST 6000
20 CLERK 1900
20 MANAGER 2975
30 ALL Job 9400
30 CLERK 950
30 MANAGER 2850
30 SALESMAN 5600
All Dept ALL Job 29025
All Dept ANALYST 6000
All Dept CLERK 4150
All Dept MANAGER 8275
All Dept PRESIDENT 5000
All Dept SALESMAN 5600

例子11. ratio_to_report比率函数

SELECT deptno,ename,SUM(sal),
trunc((ratio_to_report(SUM(sal))over(PARTITION BY deptno )),3) ratio
FROM emp
GROUP BY deptno,ename

DEPTNO ENAME SUM(SAL) RATIO
10 CLARK 2450 0.28  2450/(2450+5000+1300)=0.28
10 KING 5000 0.571
10 MILLER 1300 0.148
20 ADAMS 1100 0.101
20 FORD 3000 0.275
20 JONES 2975 0.273
20 SCOTT 3000 0.275
20 SMITH 800 0.073
30 ALLEN 1600 0.17
30 BLAKE 2850 0.303
30 JAMES 950 0.101
30 MARTIN 1250 0.132
30 TURNER 1500 0.159
30 WARD 1250 0.132

例子12：FIRST函数
功能说明：DENSE_RANK返回的集合中取出排在最前面的一个值的行
SELECT deptno,ename,empno,sal, MIN(sal) KEEP
(dense_rank FIRST ORDER BY empno) over (PARTITION BY deptno) "min_sal"
FROM emp

DEPTNO ENAME EMPNO SAL min_sal
10 CLARK 7782 2450.00 2450
10 KING 7839 5000.00 2450
10 MILLER 7934 1300.00 2450
20 SMITH 7369 800.00 800
20 ADAMS 7876 1100.00 800
20 FORD 7902 3000.00 800
20 SCOTT 7788 3000.00 800
20 JONES 7566 2975.00 800
30 ALLEN 7499 1600.00 1600
30 BLAKE 7698 2850.00 1600
30 MARTIN 7654 1250.00 1600
30 JAMES 7900 950.00 1600
30 TURNER 7844 1500.00 1600
30 WARD 7521 1250.00 1600

例子说明：
DENSE_RANK 按部门分区，再按工号排序，FIRST取出工号最小的对应的所有行
然后前面的MIN函数从这个集合中取出薪水最低的值

分析函数在美同统计系统的应用(陆续增加中)

应用1:每个小时mt失败率
SELECT /*+RULE*/
TO_CHAR(MT_TIME, 'yyyymmddhh24') DD,
CLIENT_ID,
CLIENT_NAME,
SERVICE_ID,
MT_REPORT,
COUNT(*) MT_REPORT_CNT,
ratio_to_report(COUNT(*)) over
(PARTITION BY TO_CHAR(MT_TIME, 'yyyymmddhh24')) ratio
FROM SMSUSER.MT_LOG
WHERE MT_TIME >= TRUNC(SYSDATE)
AND MT_TIME < TRUNC(SYSDATE) + 1
GROUP BY TO_CHAR(MT_TIME, 'yyyymmddhh24'),
CLIENT_ID,
CLIENT_NAME,
SERVICE_ID,
MT_REPORT
应用2：

附:
关键字:
analytic functions 分析函数
unbounded 极大的
preceding 在前面
ratio 比率
dense 密集的，浓厚的
rows between 1 preceding and 1 following 当前行的前1行和后一行
rows between unbounded preceding and unbounded following
每行对应的数据窗口是从第一行到最后一行
range between 50 preceding and 100 following 等价于 [n-50,n+100]
count(*) over () as alias 加总
KEEP (dense_rank FIRST ORDER BY empno)..keep（ dense_rank first..）
常搭配使用

参考文献：
oracle官方文档Oracle9i SQL Reference a90125.pdf

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/8109090/viewspace-907865/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/8109090/viewspace-907865/