over()分析函数_over函数

这个函数长这个样子：OVER(PARTITION BY… ORDER BY…)

顾名思义，PARTITION 中文是分割的意思，ORDER 是排序的意思，所以翻译一下就是先把一组数据按照制定的字段进行分割成各种组，然后组内按照某个字段排序。
在使用 row_number() over()函数时候，over()里头的分组以及排序的执行晚于 where 、group by、 早于order by 的执行。
伪列ROWNUM
常用的分析函数如下所列：

 - row_number() over(partition by ... order by ...)		--分组排序
 - rank() over(partition by ... order by ...)		--分组排序
 - dense_rank() over(partition by ... order by ...)		--分组排序
 - count() over(partition by ... order by ...)		--分组计数
 - max() over(partition by ... order by ...) 		--分组取最大值
 - min() over(partition by    ... order by ...)		--分组取最小值
 - sum() over(partition by ... order by ...)		--分组求和
 - avg() over(partition by ... order by ...)		--分组取平均值
 - first_value() over(partition by ... order by ...)		--取分组第一条
 - last_value() over(partition by ... order by ...)			--取分组最后一条
 - lag() over(partition by ... order by ...)		--取出同一字段的前N行的数据
 - lead() over(partition by ... order by ...)		--取出同一字段的前N行的数据
1
2
3
4
5
6
7
8
9
10
11
12

1，辨析分组排序：伪列rownum()、row_number() over()、rank() over()、dense_rank() over
创建表：

create table testdata (
id int,ename varchar2(20),sal number(10),code varchar2(20));
1
2

插入数据：

insert into testdata values(1,'a001',100,'aaaaa');
insert into testdata values(2,'a001',500,'aaaaa');
insert into testdata values(3,'a001',200,'bbbbb');
insert into testdata values(4,'a002',400,'ccccc');
insert into testdata values(5,'a002',300,'aaaaa');
insert into testdata values(6,'a003',200,'ccccc');
insert into testdata values(7,'a003',800,'ddddd');
insert into testdata values(8,'a002',400,'ccccc');
insert into testdata values(9,'a003',100,'aaaaa');
insert into testdata values(10,'a003',200,'ddddd');
commit;
1
2
3
4
5
6
7
8
9
10
11

数据测试：

select id,ename,sal,code,rownum ,--伪列
	row_number() over(partition by ename order by sal) row_number,
	rank() over(partition by ename order by sal) rank_over,
	dense_rank() over(partition by ename order by sal) dense_rank
from testdata
1
2
3
4
5

结果：

注意：
–id列为编号列
–伪劣rownum为数据插入时自动生成的列
–row_number() 按分组排序方式依次生成序号，有两个第一名时，只取一个
–rank()是跳跃排序，有两个第二名时接下来就是第四名
–dense_rank()是连续排序，有两个第二名时仍然跟着第三名

2，分组运算

- count() over() 分组计数

select id ,ename, sal ,code ,
	count(1) over(partition by code ) count_over,	--计数汇总
	count(1) over(partition by code order by id) count_order_by		--分步计数
from testdata;
1
2
3
4

显示结果：

• max() over() 、min() over() 分组取最大值、最小值

select id ,ename, sal ,code ,
	max(sal) over(partition by code ) max_over,		--取分组内数据的最大值
	max(sal) over(partition by code order by sal asc) max_order_by	--分步取最大值
from testdata;
1
2
3
4

显示结果：

• sum() over（）分组求和

select id ,ename, sal ,code ,
	sum(sal) over(partition by ename ) sum_over,	--分组数据求和
	sum(sal) over(partition by ename order by id) sum_order_by	--分步求和
from testdata;
1
2
3
4

显示结果：

avg() over() 分组取平均

select id ,ename, sal ,code ,
	avg(sal) over(partition by code ) avg_over,		--分组内数据平均
	avg(sal) over(partition by code order by id) avg_order_by	--分步取平均
from testdata;
1
2
3
4

显示结果：

3，lag()和lead()统计函数可以在一次查询中取出同一字段的前N行的数据和后N行的值。这种操作可以使用对相同表的表连接来实现，不过使用LAG和 LEAD有更高的效率。Lag函数为Lag(exp,N,defval)，defval是当该函数无值可用的情况下返回的值。Lead函数的用法类似。

select id ,ename, sal ,code ,
	lead(sal,1,0) over(partition by ename order by sal asc) leads,
	lag(sal,1,0) over(partition by ename order by sal asc) lags
from testdata
1
2
3
4

显示结果：

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

下面通过几个例子来说明其应用。

1、统计某商店的营业额。
date sale
1 20
2 15
3 14
4 18
5 30

规则：按天统计：每天都统计前面几天的总额，得到的结果：
1

select day,sale,sum(sale) over (order by day asc ) as 连续求和,sum(sale) over() as 总和 from t_temp;
2、统计各班成绩第一名的同学信息
NAME CLASS S
----- ----- ----------------------
fda 1 80
ffd 1 78
dss 1 95
cfe 2 74
gds 2 92
gf 3 99
ddd 3 99
adf 3 45
asdf 3 55

3dd     3      78
1

select * from
(
select name,class,s,rank()over(partition by class order by s desc) mm from t2
)
where mm=1
得到结果：
NAME CLASS S MM
----- ----- ---------------------- ----------------------
dss 1 95 1
gds 2 92 1
gf 3 99 1
ddd 3 99 1
注意：
1.在求第一名成绩的时候，不能用row_number()，因为如果同班有两个并列第一，row_number()只返回一个结果
2.rank()和dense_rank()的区别是：
–rank()是跳跃排序，有两个第二名时接下来就是第四名
–dense_rank()l是连续排序，有两个第二名时仍然跟着第三名

3.分类统计 (并显示信息)
A B C
– -- ----------------------
m a 2
n a 3
m a 2
n b 2
n b 1
x b 3
x b 2
x b 4
h b 3
select a,c,sum©over(partition by a) from t2
得到结果：
A B C SUM©OVER(PARTITIONBYA)

---

h b 3 3
m a 2 4
m a 2 4
n a 3 6
n b 2 6
n b 1 6
x b 3 9
x b 2 9
x b 4 9
如果用sum，group by 则只能得到
A SUM©

---

h 3
m 4
n 6
x 9

无法得到B列值
————————————————

原文链接：
https://blog.csdn.net/guobinhui/article/details/80965191
https://blog.csdn.net/fygkchina/article/details/80521550
https://blog.csdn.net/fxz_2010/article/details/83907152