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行的某字段值。

select dep_id, emp_id, salary,
rank() over (order by salary desc) as camp_rank，
rank() over (partition by dep_id order by salary desc) as dep_rank，
dense_rank() over (partition by dep_id order by salary desc) as dep_dense_rank，
row_number() over (order by salary desc) as camp_row_rank -- row_number的特点是不管salary是否有重复值，排名始终递增。
from employee_1

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

查询同一部门中，在当前员工之前入职的那个员工的工资，和在当前员工之后入职的那个员工的工资：

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补齐。

3, 分区子句

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

4, 排序子句

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

下面query结果有很大不同，由于加入了order by子句，同一部门员工的dep_sal不再相同，而是按salary排序后到该员工的累计总额。
select dep_id, emp_id, salary,
sum(salary) over (partition by dep_id order by salary desc) as dep_sal
from employee_1

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

5, 开窗子句

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

开窗子句具体的语法是：
rows/range between {start_point} and {end_point}
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 -- 由这里可以看出，数字100和start_date直接做了加减运算。

本文地址：https://blog.csdn.net/OnlyQi/article/details/108985244