分析函数三：基本语法

分析函数语法：
Function_name(, , ...)
over
()

一般来说至少有4个部分
用参数、分区子句、排序子句和一个开窗子句调用

  4    SUM(sal) over
  5      (PARTITION BY deptno
  6       ORDER BY ename) department_total,

● SUM就是函数名
● (SAL)是分析函数的参数，每个函数有0~3个参数。参数可以是表达式
● over是关键字，用于标识分析函数。否则查询分析器不能区别SUM()聚集函数和SUM()分析函数。跟在OVER关键字之后的子句描述了该分析函数将其执行的数据片段



1.FUNCTION 子句


等级函数

制表函数
SUM
MIN
MAX

使用 SUM(sal) over
        () running_total, 得到该组的总和，而不是连续的总和
开窗函数与制表函数的关键不同之处在于在OVER语句上缺少一个

LAG LEAD。这些函数允许在结果集中向前或向后检索值，为了避免数据的自连接，他们是非常有用的。

统计函数
VAR_POP、VAR_SAMP和STDEV_POP


2.PARTITION 子句
Partition By子句将一个简单的结果集分为N组。
分析函数分别用于每一组中，例如，在前面示范累计函数SAL时，按DEPTNO进行划分，当DEPTNO在结果集发生了改变时，则将累计的SAL复位为0，并且总和重新开始计算。
如果省略了分区子句，则全部的结果集被看作一个单一的组。
例子中，为了获得全部结果集的一个连续总和，使用了SUM(SAL),而不用分区子句。

PARTITION BY expression <, expression><,expression>

3.Order By 子句
ORDER BY 子句语法
ORDER BY expression ,
NULLS FIRST和NULLS LAST子句允许大家指定NULLS是出现在开始还是最后

ORDER BY 子句指定数据是如何存储在每个组(分区)内的，这明确地影响了任何分析函数的结果。在有ORDER BY子句存在时，对分析函数进行了不同的计算。



SELECT deptno, ename, sal, AVG(sal) over()
    FROM emp;

1    20    SMITH        800.00    2073.21428571429
2    30    ALLEN        1600.00    2073.21428571429
3    30    WARD        1250.00    2073.21428571429
4    20    JONES        2975.00    2073.21428571429
5    30    MARTIN    1250.00    2073.21428571429
6    30    BLAKE        2850.00    2073.21428571429
7    10    CLARK        2450.00    2073.21428571429
8    20    SCOTT        3000.00    2073.21428571429
9    10    KING        5000.00    2073.21428571429
10    30    TURNER    1500.00    2073.21428571429
11    20    ADAMS        1100.00    2073.21428571429
12    30    JAMES        950.00    2073.21428571429
13    20    FORD        3000.00    2073.21428571429
14    10    MILLER    1300.00    2073.21428571429

SELECT deptno, ename, sal, AVG(sal) over(ORDER BY ename)
    FROM emp;

1    20    ADAMS        1100.00    1100
2    30    ALLEN        1600.00    1350
3    30    BLAKE        2850.00    1850
4    10    CLARK        2450.00    2000
5    20    FORD        3000.00    2200
6    30    JAMES        950.00    1991.66666666667
7    20    JONES        2975.00    2132.14285714286
8    10    KING        5000.00    2490.625
9    30    MARTIN    1250.00    2352.77777777778
10    10    MILLER    1300.00    2247.5
11    20    SCOTT        3000.00    2315.90909090909
12    20    SMITH        800.00    2189.58333333333
13    30    TURNER    1500.00    2136.53846153846
14    30    WARD        1250.00    2073.21428571429

SELECT ename, deptno, sal,
  AVG(sal) over (ORDER BY deptno) sum_ename_deptno
FROM emp;
1    CLARK        10    2450.00    2916.66666666667
2    KING        10    5000.00    2916.66666666667
3    MILLER    10    1300.00    2916.66666666667
4    SMITH        20    800.00    2453.125
5    ADAMS        20    1100.00    2453.125
6    FORD        20    3000.00    2453.125
7    SCOTT        20    3000.00    2453.125
8    JONES        20    2975.00    2453.125
9    ALLEN        30    1600.00    2073.21428571429
10    BLAKE        30    2850.00    2073.21428571429
11    MARTIN    30    1250.00    2073.21428571429
12    JAMES        30    950.00    2073.21428571429
13    TURNER    30    1500.00    2073.21428571429
14    WARD        30    1250.00    2073.21428571429


SELECT ename, deptno, sal,
  AVG(sal) over (PARTITION BY deptno ORDER BY deptno) sum_ename_deptno
FROM emp;

1    CLARK        10    2450.00    2916.66666666667
2    KING        10    5000.00    2916.66666666667
3    MILLER    10    1300.00    2916.66666666667
4    SMITH        20    800.00    2175
5    ADAMS        20    1100.00    2175
6    FORD        20    3000.00    2175
7    SCOTT        20    3000.00    2175
8    JONES        20    2975.00    2175
9    ALLEN        30    1600.00    1566.66666666667
10    BLAKE        30    2850.00    1566.66666666667
11    MARTIN    30    1250.00    1566.66666666667
12    JAMES        30    950.00    1566.66666666667
13    TURNER    30    1500.00    1566.66666666667
14    WARD        30    1250.00    1566.66666666667

在没有ORDER BY子句时，在全部组上计算平均值，每一行给一个同样的值(在此被用作reporting函数)。用带有ORDER BY的AVG()时，每一行平均值是那一行与前面所有行的平均值。