MySQL8.0开窗函数

作为一名数据分析师，日常工作中经常会遇到类似这样的需求：
怎么样得到各部门工资排名前N名员工列表?查找各部门每人工资占部门总工资的百分比？累计求和如何计算？
对于这样的需求，使用传统的SQL实现起来比较困难。这类需求都有一个共同的特点，需要在单表中满足某些条件的结果集内部做一些函数操作，不是简单的表连接，也不是简单的聚合可以实现的，通常费了大半天时间写出来一堆长长的晦涩难懂的SQL，且性能低下，难以维护。要解决此类问题，最方便的就是使用开窗函数。

开窗函数简介

MySQL从8.0开始支持开窗函数，这个功能在大多商业数据库如Oracle和PostgreSQL中早已支持，也叫分析函数。

什么是开窗

它可以理解为记录集合，开窗函数也就是在满足某种条件的记录集合上执行的特殊函数。对于每条记录都要在此窗口内执行函数，有的函数随着记录不同，窗口大小都是固定的，这种属于静态窗口；有的函数则相反，不同的记录对应着不同的窗口，这种动态变化的窗口叫滑动窗口。开窗函数的本质还是聚合运算，只不过它更具灵活性，它对数据的每一行，都使用与该行相关的行进行计算并返回计算结果。

开窗函数和普通聚合函数的区别

• 聚合函数是将多条记录聚合为一条；而开窗函数是每条记录都会执行，有几条记录执行完还是几条。
• 聚合函数也可以用于开窗函数中。

create table order_tab(
	order_id int,
	user_no varchar(3),
	amount int,
	create_date date
);

insert into order_tab values
(1,'001',100,'2019-01-01'),
(2,'001',300,'2019-01-02'),
(3,'001',500,'2019-01-02'),
(4,'001',800,'2019-01-03'),
(5,'001',900,'2019-01-04'),
(6,'002',500,'2019-01-03'),
(7,'002',600,'2019-01-04'),
(8,'002',300,'2019-01-10'),
(9,'002',800,'2019-01-16'),
(10,'002',800,'2019-01-22');

select * from order_tab;
+----------+---------+--------+-------------+
| order_id | user_no | amount | create_date |
+----------+---------+--------+-------------+
|        1 | 001     |    100 | 2019-01-01  |
|        2 | 001     |    300 | 2019-01-02  |
|        3 | 001     |    500 | 2019-01-02  |
|        4 | 001     |    800 | 2019-01-03  |
|        5 | 001     |    900 | 2019-01-04  |
|        6 | 002     |    500 | 2019-01-03  |
|        7 | 002     |    600 | 2019-01-04  |
|        8 | 002     |    300 | 2019-01-10  |
|        9 | 002     |    800 | 2019-01-16  |
|       10 | 002     |    800 | 2019-01-22  |
+----------+---------+--------+-------------+

示例：查询每个用户的订单总金额

select *,sum(amount) over(partition by user_no) sum_amount 
from order_tab;
+----------+---------+--------+-------------+------------+
| order_id | user_no | amount | create_date | sum_amount |
+----------+---------+--------+-------------+------------+
|        1 | 001     |    100 | 2019-01-01  |       2600 |
|        2 | 001     |    300 | 2019-01-02  |       2600 |
|        3 | 001     |    500 | 2019-01-02  |       2600 |
|        4 | 001     |    800 | 2019-01-03  |       2600 |
|        5 | 001     |    900 | 2019-01-04  |       2600 |
|        6 | 002     |    500 | 2019-01-03  |       3000 |
|        7 | 002     |    600 | 2019-01-04  |       3000 |
|        8 | 002     |    300 | 2019-01-10  |       3000 |
|        9 | 002     |    800 | 2019-01-16  |       3000 |
|       10 | 002     |    800 | 2019-01-22  |       3000 |
+----------+---------+--------+-------------+------------+

我们知道聚合函数对一组值执行计算并返回一个值，但有时候一组数据只返回一个结果值并不能满足需求。
示例：查询每个用户按时间顺序的累计订单金额

select *,sum(amount) over(partition by user_no order by create_date) sum_amount
from order_tab;
+----------+---------+--------+-------------+------------+
| order_id | user_no | amount | create_date | sum_amount |
+----------+---------+--------+-------------+------------+
|        1 | 001     |    100 | 2019-01-01  |        100 |
|        2 | 001     |    300 | 2019-01-02  |        900 |
|        3 | 001     |    500 | 2019-01-02  |        900 |
|        4 | 001     |    800 | 2019-01-03  |       1700 |
|        5 | 001     |    900 | 2019-01-04  |       2600 |
|        6 | 002     |    500 | 2019-01-03  |        500 |
|        7 | 002     |    600 | 2019-01-04  |       1100 |
|        8 | 002     |    300 | 2019-01-10  |       1400 |
|        9 | 002     |    800 | 2019-01-16  |       2200 |
|       10 | 002     |    800 | 2019-01-22  |       3000 |
+----------+---------+--------+-------------+------------+

开窗函数分类

按照函数功能不同，MySQL支持的开窗函数分为如下几类：

• 序号函数：row_number() / rank() / dense_rank()
• 分布函数：percent_rank() / cume_dist()
• 前后函数：lag() / lead()
• 头尾函数：first_value() / last_value()
• 其他函数：nth_value() / nfile()

开窗函数使用

语法：

开窗函数名([<字段名>]) over([partition by <分组字段>] [order by <排序字段> [desc]] [<窗口分区>])

开窗函数的一个概念是当前行，当前行属于某个窗口，窗口由
over关键字用来指定函数执行的窗口范围，如果后面括号中什么都不写，则意味着窗口包含满足where条件的所有行，开窗函数基于所有行进行计算；如果不为空，则有三个参数来设置窗口：

• partition by子句：按照指定字段进行分区，两个分区由边界分隔，开窗函数在不同的分区内分别执行，在跨越分区边界时重新初始化。
• order by子句：按照指定字段进行排序，开窗函数将按照排序后的记录顺序进行编号。可以和partition by子句配合使用，也可以单独使用。
• frame子句：当前分区的一个子集，用来定义子集的规则，通常用来作为滑动窗口使用。

示例：查询每个用户按下单时间顺序，前一行和后一行记录的平均订单金额

select *,avg(amount) over(partition by user_no order by create_date rows between