前言
各位，博主开始敲黑板了，有没有不了解、或者完全没听说过窗口函数的盆友？文末有个投票，可以参与一下哦~
MySQL数据库从8.0开始支持窗口函数了，它是一种强大的数据分析工具，旨在帮助你快速获得场景数据。在正式介绍这类函数前，博主还是解释一下为什么这么称呼这类函数为“窗口”函数。窗口——一个数据记录的集合，也就是你的数据操作范围只限于这个数据集，再无其他。它与group by类似， 但是最大的区别是窗口函数会为每个查询行生成一个结果（add column）。
恭喜你，有这个认知后，博主可以正式介绍它了，请紧随博主，以防迷路。
窗口函数必学必会
既然窗口函数是服务于数据分析的，那么先来看看它长什么样，有句话说得好：“没吃过猪肉，还没见过猪跑么”。当我们Get一个新知时，也要怀着同样的预期和先行一步的姿态去对待它。那咱们先看看它的语法结构吧。
1. 基本语法
1.1 匿名窗口
SELECT
<窗口函数名> over (partition by <分组列名> order by <排序列名>)
FROM `你的表名`
- 1
- 2
- 3
1.2 显式窗口
SELECT
<窗口函数名> OVER win
FROM `你的表名`
WINDOW win AS (partition by <分组列名> order by <排序列名>)
- 1
- 2
- 3
- 4
其中，窗口函数名必须指定，partition by（可选），order by（可选）。
2. 包括哪些
窗口函数主要包含两大类：常见的聚合函数（count、sum、avg等）和专用的窗口函数（比如排序等）。
2.1 聚合函数
大多数的聚合函数皆可用作窗口函数，通常与GROUP BY子句使用，将统计值分组到子集中。
| 聚合函数 | 用途说明 |
|---|---|
| AVG() | 返回平均值 |
| BIT_AND() | 按位 AND 运算，代表逻辑与 |
| BIT_OR() | 按位 OR 运算，代表逻辑或 |
| BIT_XOR() | 按位 XOR 运算，代表逻辑异或 |
| COUNT() | 返回行数 |
| COUNT(DISTINCT) | 返回去重后的行数 |
| GROUP_CONCAT() | 分组后，返回一个自动连接的字符串 |
| JSON_ARRAYAGG() | 返回一个json数组 |
| JSON_OBJECTAGG() | 返回一个json对象 |
| MAX() | 返回最大值 |
| MIN() | 返回最小值 |
| STD() | 返回整体标准偏差 |
| STDDEV() | 返回整体标准偏差 |
| STDDEV_POP() | 返回整体标准偏差 |
| STDDEV_SAMP() | 返回样本标准偏差 |
| SUM() | 返回总和 |
| VAR_POP() | 返回整体标准方差 |
| VAR_SAMP() | 返回样本方差 |
| VARIANCE() | 返回整体标准方差 |
提示：
除非另有说明，否则聚合函数会忽略NULL值。
如果在不包含GROUP BY子句的SQL中使用聚合函数，则相当于对所有行进行分组。对于数值参数，方差和标准偏差函数返回一个DOUBLE值。SUM（）和AVG（）函数如果为精确值参数（整数或DECIMAL）返回DECIMAL值，如果为近似值参数（FLOAT或DOUBLE）返回DOUBLE值。
如使一个聚合函数转换为一个窗口函数执行，需按如下格式执行（over子句）：
# 添加over子句
SUM([DISTINCT] expr) [over_clause]
- 1
- 2
示例1-普通聚合：
这是一个普通聚合函数写法（来自官网）：
SELECT
country,
SUM(profit) AS country_profit
FROM sales
GROUP BY country
ORDER BY country;
- 1
- 2
- 3
- 4
- 5
- 6
示例2-窗口函数：
这是一个转为窗口函数写法（来自官网）：
SELECT
year, country, product, profit,
SUM(profit) OVER() AS total_profit,
SUM(profit) OVER(PARTITION BY country) AS country_profit
FROM sales
ORDER BY country, year, product, profit;
- 1
- 2
- 3
- 4
- 5
- 6
是不是很简单？
2.2 专用窗口函数
我们已知窗口函数是对一个记录集执行类似聚合的操作。然而，虽然聚合操作将查询行分组为单个结果行，但窗口函数会为每个查询行生成一个结果。
| 窗口函数 | 用途说明 |
|---|---|
| ROW_NUMBER() | 为结果集中的每行记录分配唯一的连续整数序号 |
| RANK() | 为结果集中的每行记录分配一个排名 |
| DENSE_RANK() | 为结果集中的每行分配一个排名，但不会跳过相同的排名 |
| PERCENT_RANK() | 用于计算某行在结果集中的相对排名比，其值介于0-1间，表示相对位置 |
| CUME_DIST() | 用于计算某行在结果集中的累积分布值，其值介于0-1间，表示累计分布比例 |
| LAG(expr,n) | 返回当前行的前 n 行的expr值 |
| LEAD(expr,n) | 返回当前行的后 n 行的expr的值 |
| FIRST_VALUE(expr) | 返回第一个expr的值 |
| LAST_VALUE(expr) | 返回最后一个expr的值 |
| NTILE() | 返回当前行在其分区内的桶数 |
| NTH_VALUE() | 返回窗口内第N行的参数值 |
over_clause表示over子句。
某些窗口函数允许使用null_treation子句，该子句指定在计算结果时如何处理null值，本选项款为可选。它是SQL标准的一部分，但MySQL实现只允许RESPECT NULL（这也是默认值）。这意味着在计算结果时会考虑NULL值。
博主这里提供5个示例（来自官网）。请注意SQL中的OVER子句。
示例1：
SELECT
val,
ROW_NUMBER() OVER w AS 'row_number',
CUME_DIST() OVER w AS 'cume_dist',
PERCENT_RANK() OVER w AS 'percent_rank'
FROM numbers
WINDOW w AS (ORDER BY val);
- 1
- 2
- 3
- 4
- 5
- 6
- 7
示例2：
SELECT
time, subject, val,
FIRST_VALUE(val) OVER w AS 'first',
LAST_VALUE(val) OVER w AS 'last',
NTH_VALUE(val, 2) OVER w AS 'second',
NTH_VALUE(val, 4) OVER w AS 'fourth'
FROM observations
WINDOW w AS (PARTITION BY subject ORDER BY time ROWS UNBOUNDED PRECEDING);
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
示例3：
SELECT
t, val,
LAG(val) OVER w AS 'lag',
LEAD(val) OVER w AS 'lead',
val - LAG(val) OVER w AS 'lag diff',
val - LEAD(val) OVER w AS 'lead diff'
FROM series
WINDOW w AS (ORDER BY t);
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
示例4：
SELECT
val,
ROW_NUMBER() OVER w AS 'row_number',
NTILE(2) OVER w AS 'ntile2',
NTILE(4) OVER w AS 'ntile4'
FROM numbers
WINDOW w AS (ORDER BY val);
- 1
- 2
- 3
- 4
- 5
- 6
- 7
示例5：
SELECT
val,
ROW_NUMBER() OVER w AS 'row_number',
RANK() OVER w AS 'rank',
DENSE_RANK() OVER w AS 'dense_rank'
FROM numbers
WINDOW w AS (ORDER BY val);
- 1
- 2
- 3
- 4
- 5
- 6
- 7
结语
窗口函数的主要作用是对查询结果集中的行进行分组、排序，并在每个分组内进行聚合、排名、计算等操作，但不会改变原始查询结果的行数或顺序。 窗口函数主要用于数据分析场景，其最大的特点是输入值是从SELECT语句结果集中的一行或多行的“窗口”中获取的。窗口函数的具体应用场景包括：
- 分组排序：可以对数据进行分组排序，求和、求平均值、计数等；
- 排名计算：计算分组内的排名或累积求和等；
- 数据分析：提供强大的数据分析支持，如计算同比/环比增长率等；
走过的、路过的盆友们，点点赞，收收藏，并加以指导，以备不时之需哈~
精彩回放
MySQL系列之数据导入导出
MySQL系列之索引入门（上）
MySQL系列之索引入门（下）
评论记录：
回复评论：