Oracle分析函数

Oracle从8.1.6开始提供分析函数，分析函数用于计算基于组的某种聚合值，它和聚合函数的不同之处是
对于每个组返回多行，而聚合函数对于每个组只返回一行。

一、开窗函数
开窗函数指定了分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的变化而变化。

例如：
1)over（order by salary）按照salary排序进行累计，order by是个默认的开窗函数
2)over（partition by department_id）按照部门分区
3)over（order by salary range between 50 preceding and 150 following）
每行对应的数据窗口是之前行幅度值不超过50，之后行幅度值不超过150
4)over（order by salary rows between 50 preceding and 150 following）
每行对应的数据窗口是之前50行，之后150行
5)over（order by salary rows between unbounded preceding and unbounded following）
每行对应的数据窗口是从第一行到最后一行，等效：
6) over（order by salary range between unbounded preceding and unbounded following）
其中：
第一行是unbounded preceding
当前行是current row
最后一行是unbounded following
二、分析函数的概念
分析函数是在整个SQL查询结束后(SQL语句中的ORDER BY的执行比较特殊)再进行的操作, 也就是说
SQL语句中的ORDER BY也会影响分析函数的执行结果。

分析函数中包含三个分析子句:分组(Partition By), 排序(Order By), 窗口(Window)
当省略窗口子句时:
1) 如果存在Order By则默认的窗口是unbounded preceding and current row
2) 如果同时省略Order By则默认的窗口是unbounded preceding and unbounded following
如果省略分组,则把全部记录当成一个组
a) 如果SQL语句中的Order By满足分析函数分析时要求的排序,那么SQL语句中的排序将先执行,分析
函数分析时就不必再排序
b) 如果SQL语句中的Order By不满足分析函数分析时要求的排序，那么SQL语句中的排序将最后在分
析函数分析结束后执行排序
三、分析函数
1）AVG
功能描述：用于计算一个组和数据窗口内表达式的平均值。

例：
计算员工表中每个员工的平均薪水报告，该平均值由当前员工和与之具有相同经理的前一个和后一个三者的平均数得来；
SELECT manager_id,
last_name,
hire_date,
salary,
AVG(salary) OVER(PARTITION BY manager_id ORDER BY hire_date ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) as c_mavg
FROM employees;
2）COUNT
功能描述：对一组内发生的事情进行累积计数，如果指定*或一些非空常数，count将对所有行计数，如果指定一个表达式，count返回表达式非空赋值的计数，当有相同值出现时，这些相等的值都会被纳入被计算的值；可以使用DISTINCT来记录去掉一组中完全相同的数据后出现的行数。

例：
SELECT last_name,
salary,
COUNT(*) OVER() AS cnt1,
COUNT(*) OVER(ORDER BY salary) AS cnt2,--相当于小于等于当前行的SALARY值的所有行数
COUNT(*) OVER(ORDER BY salary RANGE BETWEEN 50 PRECEDING AND 150 FOLLOWING) AS cnt3
FROM employees;
3）ROW_NUMBER
功能描述：返回有序组中一行的偏移量，从而可用于按特定标准排序的行号。

例：
返回每个员工再在每个部门中按员工号排序后的顺序号
SELECT department_id,
last_name,
employee_id,
ROW_NUMBER() OVER(PARTITION BY department_id ORDER BY employee_id) AS emp_id FROM employees
WHERE department_id < 50;
4）SUM
功能描述：该函数计算组中表达式的累积和。

例：
返回同一部门经理的员工工资累积值
SELECT manager_id,
salary,
SUM(salary) OVER(PARTITION BY manager_id ORDER BY salary RANGE UNBOUNDED PRECEDING) l_csum
FROM employees
WHERE manager_id in (101, 103, 108);
5）MAX
功能描述：在一个组中的数据窗口中查找表达式的最大值。

例：返回当前行所在部门的最大薪水值
SELECT department_id,
last_name,
salary,
MAX(salary) OVER(PARTITION BY department_id) AS dept_max
FROM employees
WHERE department_id in (10, 20, 30);
6）MIN
功能描述：在一个组中的数据窗口中查找表达式的最小值。

例：返回当前行所在部门的最小薪水值
SELECT department_id,
last_name,
salary,
MIN(salary) OVER(PARTITION BY department_id) AS dept_min
FROM employees
WHERE department_id in (10, 20, 30);
7）LAST_VALUE
功能描述：返回组中数据窗口的最后一个值。

例：
SELECT department_id,
last_name,
salary,
LAST_VALUE(last_name) OVER(PARTITION BY department_id ORDER BY salary ASC) AS lowest_sal
FROM employees
WHERE department_id in (20, 30);
8）FIRST_VALUE
功能描述：返回组中数据窗口的第一个值。

例：
SELECT department_id,
salary,
FIRST_VALUE(last_name) OVER(PARTITION BY department_id ORDER BY salary ASC) AS lowest_sal
FROM employees
WHERE department_id in (20, 30);
9)LAG
功能描述：可以访问结果集中当前行之前N行，LAG(exp,N,defval)，defval是当该函数无值可用的情况下返回的值。

例：
SELECT last_name,
hire_date,
salary,
LAG(salary, 2, 0) OVER(ORDER BY hire_date) AS prev_sal
FROM employees
WHERE job_id = 'PU_CLERK';
10）LEAD
功能描述：LEAD与LAG相反，LEAD可以访问组中当前行之后的行。

例：
SELECT last_name,
hire_date,
LEAD(hire_date, 1,0) OVER(ORDER BY hire_date) AS "NextHired"
FROM employees
WHERE department_id = 30;
Lag和Lead分析函数可以在同一次查询中取出同一字段的前N行的数据(Lag)和后N行的数据(Lead)作为独立的列。

这种操作可以代替表的自联接，并且LAG和LEAD有更高的效率。