Oracle中的分析函数

Oracle中的分析函数功能强大，非常方便，因此要重点掌握。现在SQL Server，MySQL也都提供了类似的函数。

1，基本语法

我们先来看一个简单的例子，对分析函数有个大概的认识：查看员工工资在部门中的排名。

select deptno, empno, ename,  sal,
rank() over (partition by deptno order by sal desc) as rank_value
from emp

由上例我们可以把分区函数分为3个部分：
a, 分析函数名 – 在上例中就是rank函数，rank函数返回排名。
b, 分区子句 – partition by deptno，表明在每个部门内部做排序，部门之间互不影响。
c, 排序子句 – order by sal desc，按照工资倒序。
d, 开窗子句 – 开窗子句用于更复杂的使用，后面再介绍。

以上4个部分，只有分析函数名是必须的，其他部分视情况可以不要。下面我们具体分析每个部分的用法。
一个重点是：分析函数永远在SQL中的from，where，group by,和having运行之后形成的数据集上运行，唯一的例外是order by。

select ..., rank() over (partition...order by ...)
from
where
group by
having -- 分析函数在from where groupby having之后运行
order by -- order by永远是最后运行

2, 分析函数

Oracle提供了20多个分析函数，其中常用的是：
排名函数 – rank, dense_rank, row_number
统计函数 – sum, count, avg, min, max
LAG和LEAD函数 – 返回当前行的前x行和后x行的某字段值。

a, rank, dense_rank和row_number

select dep_id, emp_id, salary,
rank() over (order by salary desc) as dep_rank，
dense_rank() over (order by salary desc) as dep_dense_rank,
row_number() over (order by salary desc) as camp_row_rank,
from employee_luyan

rank()和dense_rank()相同之处是：当两个员工工资相同时，排名相同。例如两位员工工资都为2w时，排名都是2。不同之处是，对于工资为1.6万的员工，rank()返回4，而dense_rank()返回3，也就是说dense_rank()排名更紧凑。
row_number的特点是不管salary是否有重复值，排名始终递增。

b，统计函数

返回所有员工数据，并返回其所在部门的工资总和，因此同一部门员工的dep_sal字段值是一样的。
select dep_id, emp_id, salary
sum(salary) over (partition by dep_id) as dep_sal
from employee_1

3, 分区子句

分区子句很容易理解，即在将数据分区，在分区内部使用分析函数。例如上例中，我们就在每个部门内部给员工工资排序，部门互相不影响。

4, 排序子句

排序子句用于指定数据的排序方式，例如给工资排序时，使用正序还是倒序。需要注意的是，排序方式会明显地影响任何分析函数的结果，原因是排序子句会默认加上一个开窗子句。

--再看一次上文中sum函数的使用：
select dep_id, emp_id, salary
sum(salary) over (partition by dep_id) as dep_sal_sum
from employee_1
--由于统计部门工资总和，不需要对部门员工工资排序。因此上例中没有使用order by子句。
select dep_id, emp_id, salary,
sum(salary) over (partition by dep_id order by salary desc) as dep_sal_sum
from employee_1
--上面两个query结果有很大不同。由于加入了order by子句，同一部门员工的dep_sal——sum不再相同，而是按salary排序后从部门最高工资到该员工工资的累计总额。

排序子句的默认开窗子句是：
range between unbounded preceding and current row
接下来我们对开窗子句做描述。

5, 开窗子句

要使用开窗子句，则必须使用排序子句。开窗子句用于对当前分区中的数据做进一步灵活的限定，例如在部门内按倒序排列员工工资，且显示当前员工之前(含当前员工)的工资累计总额。也就是我们在排序子句的讲解中使用过的例子。排序子句隐含的开窗函数：range between unbounded preceding and current row 就表达了“范围包含前面所有记录，以及当前记录”的含义。

开窗子句具体的语法是：
rows/range between {start_point} and {end_point}
rows/range代表开窗子句的范围是行数rows，还是一个计算出来的范围range。
start_point和end_point的值可以是：
a, unbounded 或 {数字/表达式} preceding – 向前所有数据或向前的行数是数字或表达式计算的结果。注意数字/表达式不可为负。
b, unbounded {数字/表达式} following – 向后
c, current row

--统计同一部门中，比当前员工入职早的员工中，最低工资是多少：
select dep_id, emp_id, salary, start_date,
min(salary) over (partition by dep_id order by start_date range between unbounded preceding and current row) as sal_rank
from employee_1

select dep_id, emp_id, salary, start_date,
min(salary) over (partition by dep_id order by start_date range unbounded preceding) as sal_rank
from employee_1 -- 这条语句和上面的等价

--统计同一部门中，比当前员工入职之前100天和之后100天内入职的员工中，最低工资是多少：
SELECT dep_id, emp_id, salary,
min(salary) OVER (PARTITION BY dep_id order by start_date range between 100 preceding and 100 following) min_100_sal
FROM employee_1 -- 由这里可以看出使用range时，数字100和start_date直接做了加减运算。这里对应上文提出的：range的含义不是前后xx行数据，而是和order by子句运算后得出的查询条件。

6，LAG和LEAD函数
上例中我们学习了用开窗函数控制查询数据的范围。但必须使用min，avg这样的统计函数。如果我们希望取得字段本身的值，应该怎么做呢？

--查询同一部门中，在当前员工之前入职的那个员工的工资，和在当前员工之后入职的那个员工的工资。这里可知不能使用min，max等统计函数。
SELECT dep_id, emp_id, salary, start_date, 
lag(salary, 2, 0) OVER (PARTITION BY dep_id order by start_date) previous_sal,
lead(salary, 2, 0) OVER (PARTITION BY dep_id order by start_date ) follow_sal
FROM employee_1
ORDER BY dep_id, start_date
-- lag(salary, 1, 0)  salary代表返回的是工资，1表示前1个入职的员工(如果是2则表示前面那个员工再之前入职的那个员工)，0表示当超出数值范围时，用0补齐。