0%

sql开窗函数

发表于 分类于

文章字数：269,阅读全文大约需要1分钟

作用

为前面的函数提供数据（分区排序，然后对每个分区都进行前面函数的操作）

查出每门功课的第一名

1
2
3
4
select * from(
    select *, ROW_NUMBER() over(partition by coursename order by mark desc) ranks from stuMark
    ) sc where sc.ranks=1
)
  • partition by coursename将数据根据coursename进行分区，使各个学科的成绩分开
  • ROW_NUMBER()给每个分区的成绩单独加上行号
  • where sc.ranks=1选取行号为1即第一名的成绩数据

排名

1
select RANK() over(order by studentScore desc) studentRank, * from studentScore
  • 用over向RANK函数指定数据内容，及通过分数排序的学生成绩

根据班级求总分

1
select t.name,t.class,t.sroce,sum(t.sroce) over(partition by t.class order by t.sroce desc) mm from T2_TEMP t;
  • 通过over(partition by...将数据分区
  • sum计算每个分区的总和

找出每个班级第一名和最后一名

1
2
select t.name,t.class,t.sroce,first_value(t.sroce) over(partition by t.class order by t.sroce desc) mm from T2_TEMP t;
select t.name,t.class,t.sroce,last_value(t.sroce) over(partition by t.class order by t.sroce desc) mm from T2_TEMP t;
  • 使用over分区
  • first_value和last_value找出分区的第一名和最后一名

其它

1
2
3
4
5
6
7
8
9
count() over(partition by ... order by ...)：求分组后的总数。
　　max() over(partition by ... order by ...)：求分组后的最大值。
　　min() over(partition by ... order by ...)：求分组后的最小值。
　　avg() over(partition by ... order by ...)：求分组后的平均值。
　　lag() over(partition by ... order by ...)：取出前n行数据。　　

　　lead() over(partition by ... order by ...)：取出后n行数据。

　　ratio_to_report() over(partition by ... order by ...)：Ratio_to_report() 括号中就是分子，over() 括号中就是分母。