共计 5633 个字符，预计需要花费 15 分钟才能阅读完成。
Oracle 提供了一些功能很强大的分析函数，使用这些函数可以完成可能需要存储过程来实现的需求。
分析函数计算基于一组数据行的聚合值，它们不同于聚合函数的是，它们为每一组返回多行结果。分析函数是除 ORDER BY 子句之外，在查询语句中最后执行的。所有的 join 和所有的 WHERE ,GROUP BY 和 HAVING 子句都在分析函数之前执行。所以分析函数只能出现在 select 或 ORDER BY 子句中。
下图为 11.2 版本官方文档中给出的语法示意图：
下面简单介绍一下各个部分：
analytic_function
指定分析函数的名字，后面列出了所有的分析函数
arguments
分析函数可以有 0 到 3 个参数。参数可以是任何数值类型或可以隐式转换为数值类型的其他非数值类型。
analytic_clause
用 OVER analytic_clause 表明函数操作的是一个查询结果集。如果想过滤基于分析函数的查询结果，需要使用嵌套子查询。
query_partition_clause
用 PARTITION BY 子句来把查询结果集基于一个或多个 value_expr 分组。如果省略，分析函数把所有行当作一组。
order_by_clause
用 order_by_claus 指定在一组中数据如何排序。
ASC(default)|DESC
NULLS FIRST(default in DESC)|NULLS LAST(default in ASC)
windowing_clause
部分分析函数允许使用 windowing_clause 子句。
只有当指定了 order_by_clause 后才能指定这个子句。
ROWS 指定使用物理行的 window
RANGE 指定使用逻辑偏移的 window
详细信息请参考：http://docs.oracle.com/cd/E11882_01/server.112/e41084/functions004.htm#i81407
下面为所有的分析函数，带 * 号的函数允许使用 windowing_clause：
AVG *
CORR *
COUNT *
COVAR_POP *
COVAR_SAMP *
CUME_DIST
DENSE_RANK
FIRST
FIRST_VALUE *
LAG
LAST
LAST_VALUE *
LEAD
LISTAGG
MAX *
MEDIAN
MIN *
NTH_VALUE *
NTILE
PERCENT_RANK
PERCENTILE_CONT
PERCENTILE_DISC
RANK
RATIO_TO_REPORT
REGR_ (Linear Regression) Functions *
ROW_NUMBER
STDDEV *
STDDEV_POP *
STDDEV_SAMP *
SUM *
VAR_POP *
VAR_SAMP *
VARIANCE *
以 AVG 为例介绍分析函数的使用：
AVG 也是一个聚合函数：
scott@TEST>select avg(sal) from emp; AVG(SAL) ---------- 2073.21429作为分析函数的例子：
eg1：单独使用
scott@TEST>select deptno,ename,hiredate,sal,avg(sal) over() avg from emp; DEPTNO ENAME HIREDATE SAL AVG---------- ------------------------------ ------------------- ---------- ---------- 20 SMITH 1980-12-17 00:00:00 800 2073.21429 30 ALLEN 1981-02-20 00:00:00 1600 2073.21429 30 WARD 1981-02-22 00:00:00 1250 2073.21429 20 JONES 1981-04-02 00:00:00 2975 2073.21429 30 MARTIN 1981-09-28 00:00:00 1250 2073.21429 30 BLAKE 1981-05-01 00:00:00 2850 2073.21429 10 CLARK 1981-06-09 00:00:00 2450 2073.21429 20 SCOTT 1987-04-19 00:00:00 3000 2073.21429 10 KING 1981-11-17 00:00:00 5000 2073.21429 30 TURNER 1981-09-08 00:00:00 1500 2073.21429 20 ADAMS 1987-05-23 00:00:00 1100 2073.21429 30 JAMES 1981-12-03 00:00:00 950 2073.21429 20 FORD 1981-12-03 00:00:00 3000 2073.21429 10 MILLER 1982-01-23 00:00:00 1300 2073.21429从输出可以看出函数计算出了整体的平均值，并输出到每一行
eg2：使用 query_partition_clause
scott@TEST>select deptno,ename,hiredate,sal,avg(sal) over(partition by deptno) avg from emp; DEPTNO ENAME HIREDATE SAL AVG---------- ------------------------------ ------------------- ---------- ---------- 10 CLARK 1981-06-09 00:00:00 2450 2916.66667 10 KING 1981-11-17 00:00:00 5000 2916.66667 10 MILLER 1982-01-23 00:00:00 1300 2916.66667 20 JONES 1981-04-02 00:00:00 2975 2175 20 FORD 1981-12-03 00:00:00 3000 2175 20 ADAMS 1987-05-23 00:00:00 1100 2175 20 SMITH 1980-12-17 00:00:00 800 2175 20 SCOTT 1987-04-19 00:00:00 3000 2175 30 WARD 1981-02-22 00:00:00 1250 1566.66667 30 TURNER 1981-09-08 00:00:00 1500 1566.66667 30 ALLEN 1981-02-20 00:00:00 1600 1566.66667 30 JAMES 1981-12-03 00:00:00 950 1566.66667 30 BLAKE 1981-05-01 00:00:00 2850 1566.66667 30 MARTIN 1981-09-28 00:00:00 1250 1566.66667 scott@TEST>select deptno,avg(sal) from emp group by deptno; DEPTNO AVG(SAL) ---------- ---------- 30 1566.66667 20 2175 10 2916.66667从输出可以看出，AVG 计算出了每个部门的平均值，并输出到对应的行。
eg3：使用 order_by_clause
scott@TEST>select deptno,ename,hiredate,sal,avg(sal) over(partition by deptno order by sal) avg from emp; DEPTNO ENAME HIREDATE SAL AVG---------- ------------------------------ ------------------- ---------- ---------- 10 MILLER 1982-01-23 00:00:00 1300 1300 10 CLARK 1981-06-09 00:00:00 2450 1875 10 KING 1981-11-17 00:00:00 5000 2916.66667 20 SMITH 1980-12-17 00:00:00 800 800 20 ADAMS 1987-05-23 00:00:00 1100 950 20 JONES 1981-04-02 00:00:00 2975 1625 20 SCOTT 1987-04-19 00:00:00 3000 2175 20 FORD 1981-12-03 00:00:00 3000 2175 30 JAMES 1981-12-03 00:00:00 950 950 30 MARTIN 1981-09-28 00:00:00 1250 1150 30 WARD 1981-02-22 00:00:00 1250 1150 30 TURNER 1981-09-08 00:00:00 1500 1237.5 30 ALLEN 1981-02-20 00:00:00 1600 1310 30 BLAKE 1981-05-01 00:00:00 2850 1566.66667从输出结果可以看出，每个部门的行都按 sal 做了升序排序。
eg4：使用 windowing_clause
scott@TEST>select deptno,ename,hiredate,sal,avg(sal) over(partition by deptno order by sal rows BETWEEN 1 PRECEDING AND 1 FOLLOWING) avg from emp; DEPTNO ENAME HIREDATE SAL AVG---------- ------------------------------ ------------------- ---------- ---------- 10 MILLER 1982-01-23 00:00:00 1300 1875 10 CLARK 1981-06-09 00:00:00 2450 2916.66667 10 KING 1981-11-17 00:00:00 5000 3725 20 SMITH 1980-12-17 00:00:00 800 950 20 ADAMS 1987-05-23 00:00:00 1100 1625 20 JONES 1981-04-02 00:00:00 2975 2358.33333 20 SCOTT 1987-04-19 00:00:00 3000 2991.66667 20 FORD 1981-12-03 00:00:00 3000 3000 30 JAMES 1981-12-03 00:00:00 950 1100 30 MARTIN 1981-09-28 00:00:00 1250 1150 30 WARD 1981-02-22 00:00:00 1250 1333.33333 30 TURNER 1981-09-08 00:00:00 1500 1450 30 ALLEN 1981-02-20 00:00:00 1600 1983.33333 30 BLAKE 1981-05-01 00:00:00 2850 2225从输出的结果可以看出，分析函数对每一组中的每一行的输出结果是把它自己与它的上一行和下一行这三行求平均值。
分析函数太多，这里就不一一介绍功能了，有兴趣的同学可以点开上面的连接，去查看对应的功能。
更多 Oracle 相关信息见Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx?tid=12
本文永久更新链接地址：http://www.linuxidc.com/Linux/2017-04/143100.htm
