开窗函数 –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
有三单参数,第一单参数是列名,第二只参数是偏移的offset,第三独参数是
超出记录窗口时之默认值)

相关文章