SQL之开窗函数

开窗函数

需要注意的是就目前的数据库来说，Mysql是不支持开窗函数的

over()

over（order by salary） 按照salary排序进行累计，order by是个默认的开窗函数
over（partition by deptno）按照部门分区
over（partition by deptno order by salary） 按照每个部门分区之后排序

开窗的窗口范围

1.over（order by salary range between 5 preceding and 5 following）：窗口范围为当前行数据幅度减5加5后的范围内的

样例

–sum(s)over(order by s range between 2 preceding and 2 following) 表示加2或2的范围内的求和

select name,class,s, sum(s)over(order by s range between 2 preceding and 2 following) mm from t2
name      class     s         mm
adf        3        45        45  --45加2减2即43到47，但是s在这个范围内只有45
asdf       3        55        55
cfe        2        74        74
3dd        3        78        158 --78在76到80范围内有78，80，求和得158
fda        1        80        158
gds        2        92        92
ffd        1        95        190
dss        1        95        190
ddd        3        99        198
gf         3        99        198

2.over（order by salary rows between 5 preceding and 5 following）：窗口范围为当前行前后各移动5行。

sum(s)over(order by s rows between 2 preceding and 2 following)表示在上下两行之间的范围内

select name,class,s, sum(s)over(order by s rows between 2 preceding and 2 following) mm from t2
 name      class    s          mm
adf        3        45        174  （45+55+74=174） 45上面没有只有下面的两个
asdf       3        55        252   （45+55+74+78=252） 55 上面只有一个45下面有两个 74，78
cfe        2        74        332    （74+55+45+78+80=332） 74上面两个45，55下面两个78，80
3dd        3        78        379    （78+74+55+80+92=379） 78 上面两个55，74下面两个80，92
fda        1        80        419
gds        2        92        440
ffd        1        95        461
dss        1        95        480
ddd        3        99        388
gf         3        99        293

与over函数结合的几个函数介绍

row_number()over()、rank()over()和dense_rank()over()函数的使用

row_number()over()

查询每个班的第一名的成绩
1 SELECT * FROM (select t.name,t.class,t.sroce,rank() over(partition by t.class order by t.sroce desc) mm from T2_TEMP t) where mm = 1;
结果为：
1 得到的结果是:
2 dss        1        95        1
3 ffd        1        95        1
4 gds        2        92        1
5 gf         3        99        1
6 ddd        3        99        1

rank()和dense_rank()可以将所有的都查找出来，rank可以将并列第一名的都查找出来；rank()和dense_rank()区别：rank()是跳跃排序，有两个第二名时接下来就是第四名。

　求班级成绩排名：
1 select t.name,t.class,t.sroce,rank() over(partition by t.class order by t.sroce desc) mm from T2_TEMP t;

查询结果：

dss        1        95        1
ffd        1        95        1
fda        1        80        3
gds        2        92        1
cfe        2        74        2
gf         3        99        1
ddd        3        99        1
3dd        3        78        3
asdf       3        55        4
adf        3        45        5
dense_rank()l是连续排序，有两个第二名时仍然跟着第三名
1 select t.name,t.class,t.sroce,dense_rank() over(partition by t.class order by t.sroce desc) mm from T2_TEMP t;

查询结果：

dss        1        95        1
ffd        1        95        1
fda        1        80        2 
gds        2        92        1
cfe        2        74        2
gf         3        99        1
ddd        3        99        1
3dd        3        78        2
asdf       3        55        3
adf        3        45        4

sum()over（）的使用

select name,class,s, sum(s)over(partition by class order by s desc) mm from t2 --根据班级进行分数求和
dss        1        95        190  --由于两个95都是第一名，所以累加时是两个第一名的相加
ffd        1        95        190
fda        1        80        270  --第一名加上第二名的
gds        2        92        92
cfe        2        74        166
gf         3        99        198
ddd        3        99        198
3dd        3        78        276
asdf       3        55        331
adf        3        45        376