关于 sql 中的 over() 函数

关于sql中的 row_number()、over()、rank()、dense_rank() 函数，还是挺常见的，而且看起来高级。

萌新理解

另一种聚合函数，不需要绑定 group by 就能把聚合和非聚合的函数展示在一起。真香。
设存在 t 表数据如下：

name	class	score
a	1	44
b	1	17
c	1	23
d	2	29
e	2	19

求每个班成绩排序：

select * ,row_number() over (partition by class order by score) rn from t;

name	class	score	rn
a	1	17	1
b	1	23	2
c	1	44	3
d	2	19	1
e	2	29	2

其中：
row_number()    —— 生成行号
partition by class  —— 根据 class 分组
order by score    —— 根据 score 排序，默认升序
over()        —— 定义规则
rn          —— as rn

如果存在相同分数，row_number()也一定要分个高下的。这时该怎么办呢？

select * ,rank() over (partition by class order by socre desc) rn from t;

然后这里的 rank() 是跳跃排序，获取的rn不连续。
如当同时存在两个100分的第1名时，顺位下一个99分则会获得第3名。
如果需要连续排序，则需要使用 dense_rank() 。

然而 over() 并非同 row_number()、rank() 锁死的，他还可以搭配于其他函数。

select * ,sum(score) over (partition by class order by socre desc) s from t;

name	class	score	s
a	1	17	84
b	1	23	84
c	1	44	84
d	2	19	48
e	2	29	48

那你sum()都行，我avg()不乐意了，我们也得行！

求大于平均分的数据：

select *
from ( select * ,avg(score) over (partition by class) avgr 
       from t )
where score > avgr;

参考文章：

https://blog.csdn.net/zimiao552147572/article/details/88427210
https://blog.csdn.net/yilulvxing/article/details/85098273