内容
概要介绍
分析函数如何工作？
语法
计算动态总和
Top-N查询
例1
例2
窗口
范围窗口
计算特定范围内的平均工资
行窗口
访问你当前行附近的行
LAG
LEAD
确定一个集合中的头值、尾值
跨表查询，还是重点查询
结论
概论
分析函数从Oracle 8.1.6开始出现，用于解决诸如“计算动态总和”、“计算集合中的百分比”、“查询Top-N”、“计算移动平均数”以及更多的问题。其中许多问题可以使用标准PL/SQL解决，但性能不好。分析函数对于SQL语言进行了扩展，不仅体现在易于对这些操作编码，还体现在与纯SQL或PL/SQL在速度上的提高。ANSI SQL委员会正在对这些扩展进行审查以便将它们包含进SQL规范中来。
分析函数如何工作？
分析函数基于一个数据行的集合计算聚合值。它们有别于对每个集合返回多行的聚合函数。数据行的集合被称为窗口，可以通过分析从句来定义。每一行都可以定义一个“滑动”窗口。基于“当前行”的窗口决定了用于进行计算的数据行的范围。窗口大小可以基于物理上的数据行数，也可以基于一个逻辑间隔，比如说时间。分析函数在查询语句中仅order by从句之前。所有的连接和所有的where、group by和having从句都位于分析函数之前。因此，分析函数旨在select列表或者order从句中出现。
语法 查询分割从句
分析函数的语法非常简单直接 ：
分析函数(<参数>，<参数>，……)
OVER(
<查询分割从句>
<Order by从句>
<窗口从句>
）
分析函数
Oracle提供了许多分析函数，比如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，等等。
参数
分析函数可以有0到3个参数
由于通过partition表达式将标准集合分为N个集合，Partition By从句逻辑上使得对于N个集合不再有单一的结果集。“partition”和“group”在这里意思一样。分析函数被独立应用于各个集合，对于每一个集合来说它们都是从头开始计算的。
Order-By从句
Order By从句用于指出数据如何存储在每一个集合中。这将对任何一个分析函数的输出产生重要影响。
窗口从句
窗口从句给我们一种定义滑动或固定数据窗口的方法，分析函数将在特定集合中的这个窗口范围内操作。这个从句可以用于使用分析函数计算特定集合中任意滑动或固定窗口中的数值
例：计算动态总和
这个例子显示了某部门中按行计算的累积工资值，每行都包括前面所有行的工资在内。
set autotrace traceonly explain
break on deptno skip 1
column ename format A6
column deptno format 999
column sal format 99999
column seq format 999
SELECT ename "Ename", deptno "Deptno", sal "Sal",
SUM(sal)
OVER (ORDER BY deptno, ename) "Running Total",
SUM(SAL)
OVER (PARTITION BY deptno
ORDER BY ename) "Dept Total",
ROW_NUMBER()
OVER (PARTITION BY deptno
ORDER BY ENAME) "Seq"
FROM emp
ORDER BY deptno, ename
/
Ename Deptno Sal Running Total Dept Total Seq
------ ------ ------ ------------- ---------- ----
CLARK 10 2450 2450 2450 1
KING 5000 7450 7450 2
MILLER 1300 8750 8750 3
ADAMS 20 1100 9850 1100 1
FORD 3000 12850 4100 2
JONES 2975 15825 7075 3
SCOTT 3000 18825 10075 4
SMITH 800 19625 10875 5
ALLEN 30 1600 21225 1600 1
BLAKE 2850 24075 4450 2
JAMES 950 25025 5400 3
MARTIN 1250 26275 6650 4
TURNER 1500 27775 8150 5
WARD 1250 29025 9400 6
Execution Plan
---------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 WINDOW (SORT)
2 1 TABLE ACCESS (FULL) OF 'EMP'
Statistics
---------------------------------------------------
0 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
1658 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
14 rows processed
break on deptno skip 1
column ename format A6
column deptno format 999
column sal format 99999
column seq format 999
SUM(sal)
OVER (ORDER BY deptno, ename) "Running Total",
SUM(SAL)
OVER (PARTITION BY deptno
ORDER BY ename) "Dept Total",
ROW_NUMBER()
OVER (PARTITION BY deptno
ORDER BY ENAME) "Seq"
FROM emp
ORDER BY deptno, ename
/
------ ------ ------ ------------- ---------- ----
CLARK 10 2450 2450 2450 1
KING 5000 7450 7450 2
MILLER 1300 8750 8750 3
ADAMS 20 1100 9850 1100 1
FORD 3000 12850 4100 2
JONES 2975 15825 7075 3
SCOTT 3000 18825 10075 4
SMITH 800 19625 10875 5
ALLEN 30 1600 21225 1600 1
BLAKE 2850 24075 4450 2
JAMES 950 25025 5400 3
MARTIN 1250 26275 6650 4
TURNER 1500 27775 8150 5
WARD 1250 29025 9400 6
这个例子展示了如何对整个查询计算动态和。通过SUM(sal) OVER (ORDER BY deptno, ename)来使用排序好的结果集完成计算。
更进一步，我们能够对每一个部门计算动态和，当开始下一个部门时总和重新开始计算。PARTITION BY deptno in that SUM(sal) 用于完成这个目的。在查询中，Partition从句用于将数据分割成多组。
ROW_NUMBER()函数用于对每组数据行根据我们的排序标准指定序列号，将加一个“Seq”列，用来显示这个位置。执行计划表明，整个查询只用3个consistent get就完成了，效率很高，而用标准SQL甚至PL/SQL根本不可能。
TOP-N查询
我们如何按照某些字段获取前几条记录呢？
在可以使用这些分析函数之前，这类问题非常难以解答。
然而，遇到使用困难的人们通常认为TOP-N查询仍有一些问题。设计报表时有一些地方需要注意。考虑一下这个似乎合情理的需求吧：我想按部门得到工资前三名的营业代表。
这个需求的问题在于模糊性，因为会存在重复值，可能有四个人都得到了相同的工资，我们该怎么办呢？
例1
将营业代表按工资从多到少排序，给出前三行。如果某部门中少于三人，则给出少于三个记录。
set autotrace on explain
break on deptno skip 1
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
---------- ---------- ---------- ----------
10 KING 5000 1
CLARK 2450 2
MILLER 1300 3
20 SCOTT 3000 1
FORD 3000 2
JONES 2975 3
30 BLAKE 2850 1
ALLEN 1600 2
TURNER 1500 3
9 rows selected.
Execution Plan
--------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 VIEW
2 1 WINDOW (SORT)
3 2 TABLE ACCESS (FULL) OF 'EMP'