Oracle数据库over函数的使用

转自:  https://blog.csdn.net/a1065423444/article/details/75635611

over()函数写法over(partition by expr2 order by expr3)，根据expr2对结果进行分区，在各分区内按照expr3进行排序； 

over函数不能单独使用，需要与row_number()，rank()和dense_rank，lag()和lead(),sum()等配合使用。

分区partiton by 与 group by的区别
group by会将结果集按照指定字段进行聚合，结果集会缩减，在统计部门人数，平均工资时会用到；
partition by会对结果集按照指定字段分层排列，结果集不会缩减，如将公司所有人按照部门进行分区，会发现结果集中同一部门的人会连续排列。

根据配合使用的函数情况的不同，分层次介绍：
- row_number()，rank()，dense_rank()
- lag()与lead()
- sum()

1、与row_number()，rank()，dense_rank()的使用
这三个函数与over连用时order by 不能丢失，不写partition by时默认结果集为一整个分区。
row_number()与over(partition exp2 order by exp3)配合使用可以对分区根据exp1进行排序，此排序是从1开始，即使遇到相同的数据，也区分开来排序。

SELECT e.employee_id,
e.department_id,
row_number() over(PARTITION BY e.department_id ORDER BY e.salary),
e.salary
FROM employees e;
1
2
3
4
5

rank()与over(partition exp2 order by exp3)配合使用可以对分区根据exp3进行可并列排序，此排序是从1开始，遇到相同的数据，会排成同一名，排名可能不连续。

SELECT e.employee_id,
e.department_id,
rank() over(PARTITION BY e.department_id ORDER BY e.salary),
e.salary
FROM employees e;
1
2
3
4
5
6

dense_rank()表示每一条数据在所属分区的等级

SELECT e.employee_id,
e.department_id,
dense_rank() over(PARTITION BY e.department_id ORDER BY e.salary),
e.salary
FROM employees e;
1
2
3
4
5

2、与lag()与lead()的使用
这两个函数与over连用时order by 不能丢失，不写partition by时默认结果集为一整个分区。
lag(expr1) over(partiton by expr2 order by expr3)
在分区内获取前一行数据的expr1字段的值，第一行为空（第一行无前一行）

SELECT e.employee_id,
e.department_id,
lag(e.department_id) over(PARTITION BY e.department_id ORDER BY e.salary)
FROM employees e;
1
2
3
4

lead(expr1) over(partiton by expr2 order by expr3)
在分区内获取后一行数据的expr1字段的值，最后一行为空（最后一行无后一行）

SELECT e.employee_id,
e.department_id,
lead(e.department_id) over(PARTITION BY e.department_id ORDER BY e.salary)
FROM employees e;
1
2
3
4

在此作用不太明显，但是若配合decode等来使用，会有不一样的效果，如可以将分区内重复的结果数据置为空。
结果如下：

代码如下：

SELECT decode(department, NULL, t.department_id, NULL) department_id,
decode(department, NULL, d.department_name, NULL) department_name,
decode(department, NULL, t.employee, NULL) employee,
decode(department, NULL, t.avg_s, NULL) avg_sal,
t.last_name,
t.salary,
t.job_id
FROM departments d,
(SELECT e.department_id,
e.last_name,
e.salary,
e.job_id,
avgs.avg_s,
avgs.employee,
lag(e.department_id) over(PARTITION BY e.department_id ORDER BY e.last_name) department --用于判断的别名
FROM employees e,
(SELECT e2.department_id, --部门工资平均值与部门人数
trunc(AVG(e2.salary), 2) avg_s,
COUNT(*) employee
FROM employees e2
GROUP BY e2.department_id) avgs
WHERE e.department_id = avgs.department_id) t
WHERE d.department_id = t.department_id;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
3、与sum()的使用
sum()与over连用时order by 可以不写，不写partition by时默认结果集为一整个分区；需要特别注意的是，此处的sum不是求整个分区的和，而是按照order by expr3字段进行分层后，求出分区中小于等于自身所在分层等级的和，即连续求和。
sum(expr1) over(partition by expr2 order by expr3)

不写order by字段，默认整个分区属于同一层
多个sum() over()连用时，分区只按照顺序出现的第一个order by进行排序；每个sum()计算的值，按自身的order by进行分层后计算，类似于使用一个子查询进行计算。

SELECT e.employee_id,
e.department_id,
e.salary,
SUM(e.salary) over(PARTITION BY e.department_id),
SUM(e.salary) over(PARTITION BY e.department_id ORDER BY e.salary)
FROM employees e
WHERE e.department_id = 100;
1
2
3
4
5
6
7

此时按照的是第二个sum+over的order by e.salary字段对分区进行的排序。（第一个sum+over无order by 字段）
对于第一个sum+over，默认为所有成员在同一层，所以第四列的值全为分区员工工资总和。

SELECT e.employee_id,
e.job_id,
e.salary,
SUM(e.salary) over(PARTITION BY e.department_id ORDER BY e.salary),
SUM(e.salary) over(PARTITION BY e.department_id ORDER BY e.job_id)
FROM employees e
WHERE e.department_id = 100;
1
2
3
4
5
6
7

此处结果集按照第一个sum+over的order by e.salary进行排序，第五列的结果是按照e.job_id进行分层后连续求和得到的，第一层job_id=FI_ACCOUNT,共五个，所以那五行的第五列结果都为第一层的和；job_id=FI_MGR属于按job_id分层的第二层，所以计算结果为第一层+第二层的连续求和。
---------------------
作者：kaiyeyou
来源：CSDN
原文：https://blog.csdn.net/a1065423444/article/details/75635611
版权声明：本文为博主原创文章，转载请附上博文链接！