学习Oracle分析函数(Analytic Functions)

  1. 云栖社区>
  2. 博客>
  3. 正文

学习Oracle分析函数(Analytic Functions)

技术小胖子 2017-11-07 21:59:00 浏览327 评论0

摘要: Oracle提供了一些功能很强大的分析函数,使用这些函数可以完成可能需要存储过程来实现的需求。 分析函数计算基于一组数据行的聚合值,它们不同于聚合函数的是,它们为每一组返回多行结果。分析函数是除ORDER BY子句之外,在查询语句中最后执行的。

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

分析函数计算基于一组数据行的聚合值,它们不同于聚合函数的是,它们为每一组返回多行结果。分析函数是除ORDER BY子句之外,在查询语句中最后执行的。所有的join和所有的WHERE ,GROUP BY 和HAVING子句都在分析函数之前执行。所以分析函数只能出现在select或ORDER BY子句中。

下图为11.2版本官方文档中给出的语法示意图:

wKiom1j8YE-zfXLsAAGOKuDzgbs859.png下面简单介绍一下各个部分:

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也是一个聚合函数:

1
2
3
4
5
scott@TEST>select avg(sal) from emp;
 
  AVG(SAL)
----------
2073.21429

作为分析函数的例子:

eg1:单独使用

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
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

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

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



      本文转自hbxztc 51CTO博客,原文链接:http://blog.51cto.com/hbxztc/1918614,如需转载请自行联系原作者




【云栖快讯】一站式开发者服务,海量学习资源免费学  详情请点击

网友评论