MySQL窗口函数

一、概述

窗口函数的使用场景，在每组内排名，比如

• 排名问题：每个部门按照业绩来排名
• topN问题：找出每个部门排名前N的员工进行奖励
  OLAP函数（online analytical processing，联机分析处理），可以对数据库数据进行实时分析处理

‹窗口函数› over (partition by ‹用于分组的列名›  order by ‹用于排序的列名›)

<窗口函数>的位置，可以放以下两种函数：

1. 专用窗口函数，rank, dense_rank, row_number
2. 聚合函数，sum，avg, count, max, min

因为olap是对where或者group by子句处理后的结果进行操作，所以，原则上只能写在select子句中

窗口函数具备group by和order by 的功能，为什么还要使用？
因为，group by 分组汇总后改变了表的行数，一行只有一个类别。而partition by 和rank函数 不会减少原表中的行数

之所以叫“窗口”，是因为partition by后分组的结果称为“窗口”，表示“范围”

二、如何使用

窗口函数具有的功能：

1. 同时分组和排序
2. 不减少原表的行数

2.1 专用窗口函数

1. rank

班级表	在每个班级内，按照成绩排名，得到
	

得到的sql语句为

select *，
	（
		rank() over (partituion by  班级 order by 成绩 desc)  as ranking
	）
	from 班级表

2. 专用窗口函数rank, dense_rank, row_number的区别

• rank函数：这个例子中是5位，5位，5位，8位，也就是如果有并列名次的行，会占用下一名次的位置。
• dense_rank： 5位，5位，5位，6位，也就是如果有并列名次的行，不占用下一名次的位置。
• row_number：5位，6位，7位，8位，也就是不考虑并列名次的情况。比如前3名是并列的名次，排名是正常的1，2，3，4。

3. 窗口函数的移动平均
   用聚合函数进行举例

select *,
	(
		avg(成绩) over (order by 学号 rows 2 preceding) as current_avg
		)
		from 班级表

在公司业绩名单排名中，可以通过移动平均，直观地查看到与相邻名次业绩的平均、求和等统计数据。

三、 常见窗口函数

• 排名函数：row_number()，rank()，dense_rank()
• 聚合函数：max()，min()，count()，sum()，avg()，median()
• 向前向后取值：lag(field,n,default)，lead(field,n,default)
• 百分位：percent_rank()
• 取值函数：first_value()，last_value()，nth_value(expr, n)
• 分箱函数：ntile(n)

参考

SQL高级功能-窗口函数及经典面试题 - 钮钴禄.r的文章 - 知乎

常用的窗口函数