窗口函数基础用法回顾
SQL-窗口函数知识点及案例学习_格勒王的博客-CSDN博客窗口函数的初步介绍,学习如何使用窗口函数计算排序、推移和累加求和等操作https://blog.csdn.net/weixin_47198715/article/details/130829177下面介绍几种虽然不太常用,但是关键时刻可以发挥重要作用的窗口函数
1、分组:ntile(n)
现在有一个班级60名同学的数学测验分数,现在按照分数把同学分为10组,SQL如下
(帮助记忆:tile的英文单词是瓦片的意思,很形象吧)
select
student_id,
score,
ntile(10)over(order by score) as level
from table_name
2、排名占比:percent_rank()
现在有一份班级60名同学各科分数数据,现在计算每科排名前20%的同学
percent_rank计算的是(rank分组计数-1)/(总计数-1),从0-1分布
select
student_id,
subject,
score
from
(select
student_id,
score,
subject,
RANK() OVER ( PARTITION BY subject ORDER BY score DESC) AS rank ,
PERCENT_RANK() OVER ( PARTITION BY subject ORDER BY socre DESC) AS percent_rank
from table_name)
where percent_rank<=0.2
3、累计占比:
还是上述数据,现在想要计算得到每科,每个分数下,有多少同学的得分超过了该分数,也就是说在大于等于当前得分的同学数量累计占比
select
student_id,
subject,
score,
accumulate
from
(select
student_id,
score,
subject,
RANK() OVER ( PARTITION BY subject ORDER BY score DESC) AS rank ,
cume_dist() OVER ( PARTITION BY subject ORDER BY socre DESC) AS accumulate
from table_name)
对比理解percent_rank()和cume_dist()
得分 | 排名 | percent_rank 结果 | percent_rank 计算公式 | percent_rank 含义 |
120 | 1 | 0.0 | (1-1)/(6-1) | 该学生得分最高(含并列) |
120 | 1 | 0.0 | (1-1)/(6-1) | 该学生得分最高(含并列) |
120 | 1 | 0.0 | (1-1)/(6-1) | 该学生得分最高(含并列) |
119 | 4 | 0.6 | (4-1)/(6-1) | 该学生的排名在前60% |
118 | 5 | 0.8 | (5-1)/(6-1) | 该学生的排名在前80% |
110 | 6 | 1.0 | (6-1)/(6-1) | 该学生排名最末 |
得分 | 排名 | cume_dist 结果 | cume_dist 计算公式 | cume_dist 含义 |
120 | 1 | 0.5 | 3/6 | 有50%的学生得分达到了120分以上 |
120 | 1 | 0.5 | 3/6 | |
120 | 1 | 0.5 | 3/6 | |
119 | 4 | 0.666666667 | 4/6 | |
118 | 5 | 0.833333333 | 5/6 | |
110 | 6 | 1 | 6/6 |