mysql常用窗口函数

语法：（窗口函数） over (partition by  分组字段名 order by 排序字段名)

1、专用窗口函数rank, dense_rank, row_number 

rank函数,结果是:1,1,1,4

dense_rank函数,结果是:1,1,1,2-----常用

row_number函数,结果是:1,2,3,4 

如：①源数据

②代码：

SELECT 

dense_rank(score) over(PARTITION BY 班级 ORDER BY score desc) rank

FROM 学生表

③结果：

2、聚合函数作为窗口函数sum(),avg(),count(),max(),min(),无partition by为累计

如：①源数据

②代码：

select *,

sum(score) over (order by 学号 asc) as current_sum,

avg(score) over (order by 学号 asc) as current_avg 

 from学生表

③结果

3、LAG函数:返回前几次数据（oracle）

语法：LAG(字段名, 第几次)

LAG(login_time, 1)---1表示偏移量，返回该值前一次值

需求：LAG函数返回每个用户的上一次登录时间

实现：

SELECT

  user_id,

  login_time,

LAG(login_time, 1) OVER (PARTITION BY user_id ORDER BY login_time asc) AS prev_login_time

FROM

  login_table

---------------------------------------------------------------

id    time

id1   4日 

id1   5日

id1   6日

lag(time,1)----4日 

            5日

4、lead偏移量:返回后几次数据

语法：lead(字段名，获取下一行的第几行，下一行值不存在时返回的默认值) 

可用于下一个日期累计收入，计算增长率

1. 需求：获取每个员工下一个月薪水工资
2. 实现：

select userid，lead(salary，1,0) over （partition by userid order by salary desc） as next_salary from start

5、nth_value:返回第几行值

①语法：

nth_value(字段名,N) over(partition by 字段名 order by 字段名 desc)

            ↓    ↓   ↓

根据哪列值返回  返回的行数            用于分组列      

②示例：

#根据列exam_id进行分组，列diff降序排序，取每个分组的第2个值

nth_value(diff,2) over(partition by exam_id order by diff desc) 

nth_value(TIMESTAMPDIFF(minute,start_time,end_time),2) over(partition by exam_id order by TIMESTAMPDIFF(minute,start_time,end_time) desc)