mysql滑动窗口案例

获取学科最高分

SELECT  DISTINCT name,subject,MAX(score) OVER (PARTITION by subject) as '此学科最高分数' from scores;

获取学科的报名人数

select DISTINCT subject,count(name) over (partition by subject) as '报名此学科的人数' from scores; 

求学科总分

SELECT DISTINCT subject,SUM(score) over (partition by subject) as '此学科总分' from scores;

求每次累加分数

select score,sum(score) over (order by score) as '累加分数' from scores; 

当前分数+前一个分数+后一个分数

select score,sum(score) over (ORDER BY score rows between 1 preceding and 1 following) as '累加分数' from scores;

ROW_NUMBER() 对成绩进行排序（如果两个分数一样，那仍然是一个第一，一个第二）

select name,subject,score,ROW_NUMBER() over (order by score DESC) as '排名' from scores;

rank() 排序，并列时下面的排名间隔一个开始排即1，1，3

select name,subject,score,RANK() over(order by score desc) as '排名' from scores;

DENSE_RANK() 并列时不出现跳过排名的情况即1，1，2

select name,subject,score,DENSE_RANK() over(ORDER BY score desc) as '排名' from scores;

NTILE(N) 将结果集划分为指定数量的组，并为每个组分配一个编号，例如将下列数分成四组

select name,subject,score,NTILE(4) over(order by score desc) as '组' from scores;

LAG(expr[,N[,default]]) 处理时间序列数据或相邻行的值，它允许检索前一行的值，并将其与当前行的值进行比较或计算差异三个参数的意义：column 列名、offset 向前的偏移量、 default_value如果向前偏移的行不存在，就取这个默认值 例比较相邻两个排名的分数差。

select name,subject,score,abs(score-LAG(score,1,score) over(order by score desc)) as '分值差' from scores; 

LEAD(expr[,N[,default]]) 与LAG() 相反相后偏移。

select name,subject,score,abs(score-LEAD(score,1,score) over(order by score desc)) as '分值差' from scores;