开窗函数 –over()

三个学习性职责:种种人有例外交部次官数的战表,总结出各类人的万丈成绩。

本条难题应有依然绝对简便易行,其实就用聚合函数就好了。

select id,name,max(score) from Student group by id,name order by name

上边这种情景只适用id 和name是种种对应的,不然查询出来的数据是不许确的。

例如 : 1 张三 100

           2 张三 90

          查询出来的结果

          两条音讯都会输出。

幸免这种景象,能够选拔开窗函数。

私家知道正是,开窗函数和聚合函数成效是倒转的。

聚合函数,将多行数据统一成风姿浪漫行数据;而开窗函数则是将意气风发行数据拆分成多行。

开窗函数可以满足上述难点,同事也得以知足别的标题。举例:求每种班最高成绩学子的音信。

剖判:各样人学号一定是莫衷一是的,名字也许有重名,最大复杂的意况是,每种班最高成绩或然不断四个。

        即便后续行使起来的办法,那么是不能够满意必要的。

        使用开窗函数就会很好的缓和那个难题。

–每种班级的实际绩效率先的学习者
–学子表中国国投息如下
a 1 80
b 1 78
c 1 95
d 2 74
e 2 92
f 3 99
g 3 99
h 3 45
i 3 55
j 3 78

询问结果如下:
c 1 95 1
e 2 92 1
f 3 99 1
g 3 99 1

SQL查询语句如下:
select *
from
(
select name,class,s,rank()over(partition by class order by s desc) mm
from t2

) as t
where t.mm=1

 

心得:
rank()跳跃排序,有多少个第二名时后面跟着的是第四名
dense_rank() 一而再接二连三排序,有几个第二名时依旧跟着第三名

over()开窗函数: 在使用聚合函数后,会将多行形成意气风发行,
而开窗函数是将意气风发行成为多行;
再者在动用聚合函数后,要是要来得任何的列必得将列参加到group by中,
而采取开窗函数后,能够不接纳group by,直接将具有新闻展现出来。

开窗函数适用于在每生龙活虎行的末尾一列增添聚合函数的结果。

常用开窗函数:
1.为每条数据体现聚合消息.(聚合函数() over())
2.为每条数据提供分组的聚合函数结出(聚合函数() over(partition by 字段) as
小名) –根据字段分组,分组后开展测算
3.与排名函数一齐利用(row number() over(order by 字段) as 别称)

常用深入分析函数:(最常用的应当是1.2.3 的排序)
1、row_number() over(partition by … order by …)
2、rank() over(partition by … order by …)
3、dense_rank() over(partition by … order by …)
4、count() over(partition by … order by …)
5、max() over(partition by … order by …)
6、min() over(partition by … order by …)
7、sum() over(partition by … order by …)
8、avg() over(partition by … order by …)
9、first_value() over(partition by … order by …)
10、last_value() over(partition by … order by …)
11、lag() over(partition by … order by …)
12、lead() over(partition by … order by …)
lag 和lead 可以获取结果集中,按自然排序所排列的方今进的前后相邻若干offset
的某部行的某部列(不用结果集的自关系);
lag ,lead 分别是上前,向后;
lag 和lead
有多少个参数,第4个参数是列名,第贰个参数是偏移的offset,第八个参数是
超过记录窗口时的暗许值)

发表评论

电子邮件地址不会被公开。 必填项已用*标注