SQL-窗口函数进阶用法：ntile(),percent_rank(),cume_dist()

窗口函数基础用法回顾

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