MySQL8.0窗口函数

窗口函数简介

MySQL被Orcale收购了之后，MySQL8.0继承了Orcale的窗口函数（又称：开窗函数），这个功能在大多商业数据库如Orcale和PostgreSQL中早已支持。
MySQL的窗口函数也称为OLAP函数，OLAP是Online Analytical Processing的简称，意思是对数据库进行实时分析处理，也叫分析函数。

窗口函数与聚合函数的区别

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

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;

查询每个用户的订单总额

select *,sum(amount)over(partition by user_no) from order_tab;

窗口函数分类

函数	描述
cume_dist()	计算一组值中一个值的累计分布
dense_rank()	根据该order by子句为分区中的每一行分配一个排名。它将相同的排名分配给具有相等值的行。如果两行或者更多行具有相同的排名，则排名值序列中将没有间隙。
rank()	与dense_rank()函数相似，不同之处在于当两行或更多行具有相同的排名时，排名值序列中存在间隙。
row_number()	为分区中的每一行分配一个顺序整数排名
first_value	返回相对于窗口框架第一行的指定表达式的值。
log()	返回分区中当前行之前的第N行的值。如果不存在前一行，则返回NULL
last_value()	返回对于窗口框架中最后一行的指定表达式的值。
lead()	返回分区中当前行之后的第N行的值。如果不存在后续行，则返回NULL。
nth_value()	从窗口框架的第N行返回参数的值。
ntle()	将每个窗口分区的行分配到指定数量的排名组中。
percent_rank()	计算分区或结果集中行的百分数排名。

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]] [<窗口分区>])

参数：

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

练习：

示例1. 查询每个用户订单金额最高的前三个订单

SELECT
	*
FROM
	(
		SELECT
			*, row_number () over (
				PARTITION BY user_no
				ORDER BY
					amount DESC
			) max_amount
		FROM
			order_tab
	) t
WHERE
	t.max_amount <= 3

通过使用row_number()函数对分组后的订单金额进行倒序排序，取前三个即可。

示例2. 示例：使用三个不同的序号函数，查询不同用户的订单中，按照订单金额进行排序，显示出相应的排名

SELECT
	*, row_number () over (
		PARTITION BY user_no
		ORDER BY
			amount DESC
	) rank1,
	rank () over (
		PARTITION BY user_no
		ORDER BY
			amount DESC
	) rank2,
	dense_rank () over (
		PARTITION BY user_no
		ORDER BY
			amount DESC
	) rank3
FROM
	order_tab

可以直观的看出三种排序函数的区别。

示例3. 查询大于当前订单金额的订单占剩余订单的比例

percent_rank():: 分组内小于当前rank值的行数 / 分组内除当前rank值所在记录外的行数，计算方法：（当前rank - 1）/ (rows - 1)
其中，rank是rank()函数产生的序号，rows是窗口的记录总行数。

SELECT
	*, rank () over (
		PARTITION BY user_no
		ORDER BY
			amount
	) rank1,
	percent_rank () over (
		PARTITION BY user_no
		ORDER BY
			amount
	) percent_rank1
FROM
	order_tab

示例4. 示例：查询大于等于当前订单金额的订单比例

cume_dist(): 分组内小于等于当前rank值的行数 / 分组内总行数，计算方法： rank / rows

SELECT
	*, rank () over (
		PARTITION BY user_no
		ORDER BY
			amount DESC
	) rank1,
	cume_dist () over (
		PARTITION BY user_no
		ORDER BY
			amount DESC
	) cume1
FROM
	order_tab

示例5. 查询上一个订单距离当前订单的间隔天数

lag(n,m): 分区内(当前行n)往前数的第m行。
lead(n,m):分区内(当前行n)往后数第m行。
date_diff(n,m):计算两个日期之间的天数。

SELECT
	*, datediff(create_date, last_date)
FROM
	(
		SELECT
			*, lag (create_date, 1) over (
				PARTITION BY user_no
				ORDER BY
					create_date
			) last_date
		FROM
			order_tab
	) d

示例6.查询截止到当前订单，按照日期排序第一个订单和最后一个订单的订单金额

first_value():分区中的第一个值
last_value():分区中的根据排序参数的最后一个值

SELECT
	*, first_value (amount) over (
		PARTITION BY user_no
		ORDER BY
			create_date
	) value1,
	last_value (amount) over (
		PARTITION BY user_no
		ORDER BY
			create_date
	) value2
FROM
	order_tab

示例7. 每个用户订单中显示金额排名第二和第三的订单金额

nth_value(expr,n): 返回分组中第N个expr的值，expr可以是表达式，也可以是列名。

SELECT
	*, nth_value (amount, 2) over (
		PARTITION BY user_no
		ORDER BY
			amount
	) secoud_amount,
	nth_value (amount, 3) over (
		PARTITION BY user_no
		ORDER BY
			amount
	) third_amount
FROM
	order_tab

示例8. 将每个用户的订单按照订单金额分成3组

ntile(expr): 函数将有序数据集划分为 expr 指示的若干桶，并为每一行分配适当的桶号。

SELECT
	*, ntile (3) over (
		PARTITION BY user_no
		ORDER BY
			create_date
	)
FROM
	order_tab

相关内容

MySQL8.0官网文档：https://dev.mysql.com/doc/refman/8.0/en/window-functions-usage.html

=========================================================

人生得意须尽欢，莫使金樽空对月！
__一个热爱说唱的程序员。

=========================================================