学习Oracle分析函数(Analytic Functions)

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

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

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

本文出自 “DBA Fighting!” 博客，请务必保留此出处http://hbxztc.blog.51cto.com/1587495/1918614

学习Oracle分析函数(Analytic Functions)

标签：oracle 分析函数 analytic functions