窗口函数简介
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
=========================================================
人生得意须尽欢,莫使金樽空对月!
__一个热爱说唱的程序员。
=========================================================