MySQL窗口函数

J.Q.HUO
MySQL数据库

最近接触到了sql语法中的窗口函数（window functions），于是写下该篇文章总结应用窗口函数可以解决哪些经典问题

MySQL窗口函数介绍

MySQL OLAP函数，也被称为窗口函数，可以对表格中的数据分组排序后进行函数化的处理
接下来列举一些常用的窗口函数

函数名称 功能
row_number() 将子分区中的行进行标号操作
rank() 根据order by后面的字段的进行排序标号操作，如果出现同值的情况，则排名顺延(比如第一名有3个，则最终排名为1,1,1,4)
dense_rank() 根据order by后面的字段的进行排序标号操作，如果出现同值的情况，则排名不顺延(比如第一名有3个，则最终排名为1,1,1,2)
count() 多用来分组计数

窗口函数语法注意事项

  • 窗口函数的作用对象是已经进行过group by和order by的数据表，则窗口函数应写在select语句之后，相当于多筛选出一个字段
  • 使用窗口函数中的排名函数，并不改变原数据表中的行数（虽然语法中有group by），本质上相当于对数据表的重新分组和对分组内数据的排序

经典应用

TopN类问题

leetcode 185 部门工资前三高的所有员工
leetcode 569 员工薪水中位数

TopN类问题的描述通常带有分组+排序的特点，如上题，求出部门工资前三高的所有员工，表中有多个部门，此时需要根据部门进行汇聚，前三高则是一个排名问题，此时使用窗口函数会非常好处理

TopN类问题基本模版

select <field_name> 
from (
    select 
    *,
    row_number() over(partition by <xxx> order by <xxx>) as 'ranking'
    from <table_name>
) as t
where ranking < N

注意模版中的子查询语句需要加别名

连续N类问题

该问题通常求表中连续出现N次的数字

leetcode 180 连续出现的数字

该类问题遵循的基本逻辑是，首先对目标列进行row_number()分组排序，则相同的数字都出现在同一个分组中，假设表中某个数字连续出现了N次，则该数字的id与排序号的差必定为定值。
在实现上为了防止id不连续，首先使用row_number()函数为表新创建一个id列，接着创建对目标列的分组排序，最后使用group by对唯一组合（id-num，num）进行汇总即可

“连续N”类问题代码模版

select distinct Num as 'ConsecutiveNums'
from(
    select 
    *,
    row_number() over (partition by num order by Id) as 'rownum',
    row_number() over (order by Id) as id2
    from Logs
) as t
group by (id2-num), Num # 这里注意必须要对两个 字段进行group by汇总操作，这是因为可能有其他不相同的数字也具有同样的(id-num)值，但(id-num, num)的组合是唯一的
having count(*) >= N

持续更新中ing