- MySQL从8.0版本才开始支持窗口函数,因而很多MySQL教材并没有对窗口函数进行详细介绍,然而窗口函数功能强大,可以解决很多使用普通查询语句很难实现的复杂的单表查询问题。
一、相关概念
- 窗口:满足某种条件的记录集合,分为静态窗口(集合内记录数量固定)和滑动窗口(集合内数量不固定);
- 窗口函数:在窗口的每条记录上执行的函数。对于每条记录,窗口函数均产生一个输出。
二、语法格式
SELECT 字段1, 字段2, 窗口函数() OVER (窗口设置语句) | 窗口别名 #
FROM 表1
WHERE 条件表达式
[WINNDOW 窗口别名 AS (窗口设置语句)] #使用窗口别名时,需要
例:
SELECT device_id, university, gpa
FROM
(
SELECT *, RANK() OVER w AS rk
FROM user_profile
WINDOW w AS (PARTITION BY university ORDER BY gpa)
) AS t
WHERE rk = 1;
窗口设置语句:
例:PARTITION BY 字段1 ORDER BY 字段2 [ROWS BETWEEN 起始行 AND 结束行]
关键字:
PARTION BY 字段1
:根据字段1进行分组
ORDER BY 字段2
:根据字段儿进行排序
ROWS BETWEEN 起始行 AND 结束行
:窗口大小设置,用于滑动窗口大小设置
“起始行”和“结束行”从以下关键字中选择:
CURRENT ROW
:当前行,一般和其他关键字一起使用
UNBOUNDED PRECEDING
:分组内的第一行
UNBOUNDED FOLLOWING
:分组内的最后一行
n PRECEDING
:分组内当前行往前n行的那一行
n FOLLOWING
:分组内当前行往后n行的那一行
三、窗口函数
专用窗口函数:
序号函数:ROW_NUMBER()、RANK()、DENSE_RANK()
分布函数:PERCENT_RANK()、CUME_DIST()
前后函数:LAG()、LEAD()
头尾函数:FIRST_VAL()、LAST_VAL()
其他: NTH_VALUE()、NFILE()
聚合函数:
SUM()、AVG()、MAX()、MIN()、COUNT()
等
当聚合函数作为窗口函数,且窗口设置语句中无frame语句即ROWS BETWEEN frame_start AND frame_end
时,默认窗口范围是当前行以上的所有行。