学习Oracle分析函数(AnalyticFunctions)-创新互联

Oracle提供了一些功能很强大的分析函数，使用这些函数可以完成可能需要存储过程来实现的需求。

成都创新互联长期为近千家客户提供的网站建设服务，团队从业经验10年，关注不同地域、不同群体，并针对不同对象提供差异化的产品和服务；打造开放共赢平台，与合作伙伴共同营造健康的互联网生态环境。为福贡企业提供专业的成都网站建设、网站建设，福贡网站改版等技术服务。拥有十载丰富建站经验和众多成功案例,为您定制开发。

分析函数计算基于一组数据行的聚合值，它们不同于聚合函数的是，它们为每一组返回多行结果。分析函数是除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

从输出的结果可以看出，分析函数对每一组中的每一行的输出结果是把它自己与它的上一行和下一行这三行求平均值。

分析函数太多，这里就不一一介绍功能了，有兴趣的同学可以点开上面的连接，去查看对应的功能。

另外有需要云服务器可以了解下创新互联cdcxhl.cn，海内外云服务器15元起步，三天无理由+7*72小时售后在线，公司持有idc许可证，提供“云服务器、裸金属服务器、高防服务器、香港服务器、美国服务器、虚拟主机、免备案服务器”等云主机租用服务以及企业上云的综合解决方案，具有“安全稳定、简单易用、服务可用性高、性价比高”等特点与优势，专为企业上云打造定制，能够满足用户丰富、多元化的应用场景需求。

文章名称：学习Oracle分析函数(AnalyticFunctions)-创新互联
本文链接：http://cdiso.cn/article/ceoesh.html