MySQL系列之如何正确的使用窗口函数（基于8.0版本）

前言

各位，博主开始敲黑板了，有没有不了解、或者完全没听说过窗口函数的盆友？文末有个投票，可以参与一下哦~

MySQL数据库从8.0开始支持窗口函数了，它是一种强大的数据分析工具，旨在帮助你快速获得场景数据。在正式介绍这类函数前，博主还是解释一下为什么这么称呼这类函数为

“窗口”

函数。窗口——一个数据记录的集合，也就是你的数据操作范围只限于这个数据集，再无其他。它与group by类似， 但是最大的区别是窗口函数会为每个查询行生成一个结果（

add column

）。

恭喜你，有这个认知后，博主可以正式介绍它了，请紧随博主，以防迷路。

窗口函数必学必会

既然窗口函数是服务于数据分析的，那么先来看看它长什么样，有句话说得好：

“没吃过猪肉，还没见过猪跑么”。

当我们Get一个新知时，也要怀着同样的预期和先行一步的姿态去对待它。那咱们先看看它的语法结构吧。

1. 基本语法

1.1 匿名窗口

SELECT<窗口函数名>over(partitionby<分组列名>orderby<排序列名>)FROM`你的表名`

1.2 显式窗口

SELECT<窗口函数名>OVER win
FROM`你的表名` 
WINDOW win AS(partitionby<分组列名>orderby<排序列名>)

其中，窗口函数名必须指定，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-普通聚合：
这是一个普通聚合函数写法（来自官网）：

SELECT 
     country,SUM(profit)AS country_profit
FROM sales
GROUPBY country
ORDERBY country;

示例2-窗口函数：
这是一个转为窗口函数写法（来自官网）：

SELECTyear, country, product, profit,SUM(profit)OVER()AS total_profit,SUM(profit)OVER(PARTITIONBY country)AS country_profit
FROM sales
ORDERBY country,year, product, profit;

是不是很简单？

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(ORDERBY val);

示例2：

SELECTtime, 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(PARTITIONBY subject ORDERBYtimeROWSUNBOUNDEDPRECEDING);

示例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(ORDERBY t);

示例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(ORDERBY val);

示例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(ORDERBY val);

结语

窗口函数的主要作用是对查询结果集中的行进行分组、排序，并在每个分组内进行聚合、排名、计算等操作，但不会改变原始查询结果的行数或顺序。‌ 窗口函数主要用于数据分析场景，其最大的特点是输入值是从SELECT语句结果集中的一行或多行的

“窗口”

中获取的‌。窗口函数的具体应用场景包括：

• 分组排序‌：可以对数据进行分组排序，求和、求平均值、计数等‌；
• 排名计算‌：计算分组内的排名或累积求和等‌；
• 数据分析‌：提供强大的数据分析支持，如计算同比/环比增长率等‌；

走过的、路过的盆友们，点点赞，收收藏，并加以指导，以备不时之需哈~

精彩回放

MySQL系列之数据导入导出
MySQL系列之索引入门（上）
MySQL系列之索引入门（下）