# Oracle分析函数总结(1) - 基本概念和语法

Analytic functions compute an aggregate value based on a group of rows. They differ from aggregate functions in that they return multiple rows for each group. The group of rows is called a window and is defined by the analytic_clause. For each row, a sliding
window of rows is defined. The window determines the range of rows used to perform the calculations for the current row. Window sizes can be based on either a physical number of rows or a logical interval such as time.

Analytic functions are the last set of operations performed in a query except for the final ORDER BY clause. All joins and all WHERE, GROUP BY, and HAVING clauses are completed before the analytic functions are processed. Therefore, analytic functions can appear
only in the select list or ORDER BY clause.

(很多分析函数也可以用作聚合函数，只是两种用法在语法上有所差异)

analytic_function([arguments])

OVER(

[partition_clause]

[order_by_clause[windowing_clause]]

)

1)分析函数

AVG *

CORR *

COUNT *

COVAR_POP *

COVAR_SAMP *

CUME_DIST

DENSE_RANK

FIRST

FIRST_VALUE *

LAG

LAST

LAST_VALUE *

LISTAGG

MAX *

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 *

2)PARTITION子句

3)ORDER BY子句
ORDER BY将添加一个默认的WINDOWING子句,这个窗口从分组的第一行开始,一直继续到当前行(值),

4)WINDOWING子句

ROWS表示一个物理窗口(行);RANGE表示一个逻辑窗口。

BETWEEN…AND: 指定一个范围,如果省略,只指定一个端点,它将作为起点,终点是当前行。

UNBOUNDED PRECEDING:表示窗口从当前分组的第一行开始。

UNBOUNDED FOLLOWING:表示窗口,结束于当前分组的最后一行。

CURRENT ROW:表示窗口开始/结束于当前行或者当前值。

value_expr PRECEDING/value_expr FOLLOWING:指定一个物理或者逻辑的偏移量。

tony@ORCL1> select empno,ename,sal,deptno  2  from emp order by deptno, sal;      EMPNO ENAME             SAL     DEPTNO---------- ---------- ---------- ----------      7934 MILLER           1300         10      7782 CLARK            2450         10      7839 KING             5000         10      7369 SMITH             800         20      7876 ADAMS            1100         20      7566 JONES            2975         20      7788 SCOTT            3000         20      7902 FORD             3000         20      7900 JAMES             950         30      7654 MARTIN           1250         30      7521 WARD             1250         30      7844 TURNER           1500         30      7499 ALLEN            1600         30      7698 BLAKE            2850         30 14 rows selected.

wmsys.wm_concat()函数可以用来进行字段合并，把多行的某一列数据合并成一行显示。

tony@ORCL1> column emp_list format a40tony@ORCL1> select deptno, wm_concat(ename) emp_list  2  from emp group by deptno;     DEPTNO EMP_LIST---------- ----------------------------------------        10 CLARK,MILLER,KING        20 SMITH,FORD,ADAMS,SCOTT,JONES        30 ALLEN,JAMES,TURNER,BLAKE,MARTIN,WARD

tony@ORCL1> select ename,deptno,  2  wm_concat(ename) over (partition by deptno) emp_list  3  from emp; ENAME          DEPTNO EMP_LIST---------- ---------- ----------------------------------------CLARK              10 CLARK,KING,MILLERKING               10 CLARK,KING,MILLERMILLER             10 CLARK,KING,MILLERJONES              20 JONES,FORD,ADAMS,SMITH,SCOTTFORD               20 JONES,FORD,ADAMS,SMITH,SCOTTADAMS              20 JONES,FORD,ADAMS,SMITH,SCOTTSMITH              20 JONES,FORD,ADAMS,SMITH,SCOTTSCOTT              20 JONES,FORD,ADAMS,SMITH,SCOTTWARD               30 WARD,TURNER,ALLEN,JAMES,BLAKE,MARTINTURNER             30 WARD,TURNER,ALLEN,JAMES,BLAKE,MARTINALLEN              30 WARD,TURNER,ALLEN,JAMES,BLAKE,MARTINJAMES              30 WARD,TURNER,ALLEN,JAMES,BLAKE,MARTINBLAKE              30 WARD,TURNER,ALLEN,JAMES,BLAKE,MARTINMARTIN             30 WARD,TURNER,ALLEN,JAMES,BLAKE,MARTIN 14 rows selected.

RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

tony@ORCL1> select ename,deptno,  2  wm_concat(ename) over (partition by deptno order by ename) emp_list  3  from emp; ENAME          DEPTNO EMP_LIST---------- ---------- ----------------------------------------CLARK              10 CLARKKING               10 CLARK,KINGMILLER             10 CLARK,KING,MILLERADAMS              20 ADAMSFORD               20 ADAMS,FORDJONES              20 ADAMS,FORD,JONESSCOTT              20 ADAMS,FORD,JONES,SCOTTSMITH              20 ADAMS,FORD,JONES,SCOTT,SMITHALLEN              30 ALLENBLAKE              30 ALLEN,BLAKEJAMES              30 ALLEN,BLAKE,JAMESMARTIN             30 ALLEN,BLAKE,JAMES,MARTINTURNER             30 ALLEN,BLAKE,JAMES,MARTIN,TURNERWARD               30 ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD 14 rows selected.

tony@ORCL1> column sal format $99999.99tony@ORCL1> column avgsal format$99999.99tony@ORCL1> column sumsal format $99999.99tony@ORCL1> select ename, sal, sum(sal) over() sumsal from emp; ENAME SAL SUMSAL---------- ---------- ----------SMITH$800.00  $29025.00ALLEN$1600.00  $29025.00WARD$1250.00  $29025.00JONES$2975.00  $29025.00MARTIN$1250.00  $29025.00BLAKE$2850.00  $29025.00CLARK$2450.00  $29025.00SCOTT$3000.00  $29025.00KING$5000.00  $29025.00TURNER$1500.00  $29025.00ADAMS$1100.00  $29025.00JAMES$950.00  $29025.00FORD$3000.00  $29025.00MILLER$1300.00  $29025.00 14 rows selected. 有order by子句没有开窗子句，窗口是 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. 即分组中的第一行到当前行(值). tony@ORCL1> select ename, sal, 2 sum(sal) over(order by ename) sumsal 3 from emp; ENAME SAL SUMSAL---------- ---------- ----------ADAMS$1100.00   $1100.00ALLEN$1600.00   $2700.00BLAKE$2850.00   $5550.00CLARK$2450.00   $8000.00FORD$3000.00  $11000.00JAMES$950.00  $11950.00JONES$2975.00  $14925.00KING$5000.00  $19925.00MARTIN$1250.00  $21175.00MILLER$1300.00  $22475.00SCOTT$3000.00  $25475.00SMITH$800.00  $26275.00TURNER$1500.00  $27775.00WARD$1250.00  $29025.00 14 rows selected. 如果order by指定的是salary，注意结果中相同salary值的行的结果。 原因在于默认的窗口子句RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW 是逻辑窗口，即第一行到等于当前值的所有行。 tony@ORCL1> select ename, sal, 2 sum(sal) over(order by sal) sumsal 3 from emp; ENAME SAL SUMSAL---------- ---------- ----------SMITH$800.00    $800.00JAMES$950.00   $1750.00ADAMS$1100.00   $2850.00WARD$1250.00   $5350.00MARTIN$1250.00   $5350.00MILLER$1300.00   $6650.00TURNER$1500.00   $8150.00ALLEN$1600.00   $9750.00CLARK$2450.00  $12200.00BLAKE$2850.00  $15050.00JONES$2975.00  $18025.00SCOTT$3000.00  $24025.00FORD$3000.00  $24025.00KING$5000.00  $29025.00 14 rows selected. 分部门计算薪水总和的结果。 tony@ORCL1> select ename, sal, deptno, 2 sum(sal) over(partition by deptno) sumsal 3 from emp; ENAME SAL DEPTNO SUMSAL---------- ---------- ---------- ----------CLARK$2450.00         10   $8750.00KING$5000.00         10   $8750.00MILLER$1300.00         10   $8750.00JONES$2975.00         20  $10875.00FORD$3000.00         20  $10875.00ADAMS$1100.00         20  $10875.00SMITH$800.00         20  $10875.00SCOTT$3000.00         20  $10875.00WARD$1250.00         30   $9400.00TURNER$1500.00         30   $9400.00ALLEN$1600.00         30   $9400.00JAMES$950.00         30   $9400.00BLAKE$2850.00         30   $9400.00MARTIN$1250.00         30   $9400.00 14 rows selected. 分部门计算薪水总和的结果。因为有order by子句， 加上了一个默认的窗口子句RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW tony@ORCL1> select ename, sal, deptno, 2 sum(sal) over(partition by deptno order by ename) sumsal 3 from emp; ENAME SAL DEPTNO SUMSAL---------- ---------- ---------- ----------CLARK$2450.00         10   $2450.00KING$5000.00         10   $7450.00MILLER$1300.00         10   $8750.00ADAMS$1100.00         20   $1100.00FORD$3000.00         20   $4100.00JONES$2975.00         20   $7075.00SCOTT$3000.00         20  $10075.00SMITH$800.00         20  $10875.00ALLEN$1600.00         30   $1600.00BLAKE$2850.00         30   $4450.00JAMES$950.00         30   $5400.00MARTIN$1250.00         30   $6650.00TURNER$1500.00         30   $8150.00WARD$1250.00         30   $9400.00 14 rows selected. 计算各部门的薪水平均值。 tony@ORCL1> select ename, sal, deptno, 2 avg(sal) over(partition by deptno) avgsal 3 from emp; ENAME SAL DEPTNO AVGSAL---------- ---------- ---------- ----------CLARK$2450.00         10   $2916.67KING$5000.00         10   $2916.67MILLER$1300.00         10   $2916.67JONES$2975.00         20   $2175.00FORD$3000.00         20   $2175.00ADAMS$1100.00         20   $2175.00SMITH$800.00         20   $2175.00SCOTT$3000.00         20   $2175.00WARD$1250.00         30   $1566.67TURNER$1500.00         30   $1566.67ALLEN$1600.00         30   $1566.67JAMES$950.00         30   $1566.67BLAKE$2850.00         30   $1566.67MARTIN$1250.00         30   $1566.67 14 rows selected. 下面再来看几个指定了开窗子句的例子。 按照名字排序，计算前面3行和本行的薪水总和。 tony@ORCL1> select ename, sal, 2 sum(sal) over(order by ename rows 3 preceding) sumsal 3 from emp; ENAME SAL SUMSAL---------- ---------- ----------ADAMS$1100.00   $1100.00ALLEN$1600.00   $2700.00BLAKE$2850.00   $5550.00CLARK$2450.00   $8000.00FORD$3000.00   $9900.00JAMES$950.00   $9250.00JONES$2975.00   $9375.00KING$5000.00  $11925.00MARTIN$1250.00  $10175.00MILLER$1300.00  $10525.00SCOTT$3000.00  $10550.00SMITH$800.00   $6350.00TURNER$1500.00   $6600.00WARD$1250.00   $6550.00 14 rows selected. 按照新水排序，计算>=(自己薪水-100) and <=自己薪水的所有人的薪水总和 tony@ORCL1> select ename, sal, 2 sum(sal) over(order by sal range 100 preceding) sumsal 3 from emp; ENAME SAL SUMSAL---------- ---------- ----------SMITH$800.00    $800.00JAMES$950.00    $950.00ADAMS$1100.00   $1100.00WARD$1250.00   $2500.00MARTIN$1250.00   $2500.00MILLER$1300.00   $3800.00TURNER$1500.00   $1500.00ALLEN$1600.00   $3100.00CLARK$2450.00   $2450.00BLAKE$2850.00   $2850.00JONES$2975.00   $2975.00SCOTT$3000.00   $8975.00FORD$3000.00   $8975.00KING$5000.00   $5000.00 14 rows selected. 按照新水排序，计算>=(自己薪水-100) and <=(自己薪水+200)的所有人的薪水总和 tony@ORCL1> select ename, sal, 2 sum(sal) over(order by sal range 3 between 100 preceding and 200 following) sumsal 4 from emp; ENAME SAL SUMSAL---------- ---------- ----------SMITH$800.00   $1750.00JAMES$950.00   $2050.00ADAMS$1100.00   $4900.00WARD$1250.00   $3800.00MARTIN$1250.00   $3800.00MILLER$1300.00   $5300.00TURNER$1500.00   $3100.00ALLEN$1600.00   $3100.00CLARK$2450.00   $2450.00BLAKE$2850.00  $11825.00JONES$2975.00   $8975.00SCOTT$3000.00   $8975.00FORD$3000.00   $8975.00KING$5000.00   \$5000.00 14 rows selected.

©️2019 CSDN 皮肤主题: 大白 设计师: CSDN官方博客