MySQL8.0版本如何正确的使用窗口函数详解_数据库_开发者

前言

mysql数据库从8.0开始支持窗口函数了，它是一种强大的数据分析工具，旨在帮助你快速获得场景数据。在正式介绍这类函数前，博主还是解释一下为什么这么称呼这类函数为“窗口”函数。窗口——一个数据记录的集合，也就是你的数据操作范围只限于这个数据集，再无其他。它与group by类似，但是最大的区别是窗口函数会为每个查询行生成一个结果（add column）。

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

MySQL8.0版本如何正确的使用窗口函数详解

窗口函数必学必会

既然窗口函数是服务于数据分析的，那么先来看看它长什么样，有句话说得好：“没吃过猪肉，还没见过猪跑么”。当我们Get一个新知时，也要怀着同样的预期和先行一步的姿态去对待它。那咱们先看看它的语法结构吧。

1. 基本语法

1.1 匿名窗口

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

1.2 显式窗口

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

其中，窗口函数名必须指定，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()	返GQDoM回一个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子句）：

# 添加GQDoMover子句
SUM([DISTINCT] expr) [over_clause]

示例1-普通聚合：这是一个普通聚合函数写法（来自官网）：

SELECT 
     jscountry, 
     SUM(profit) AS country_profit
FROM sales
GROUP BY country
ORDER BY country;

MySQL8.0版本如何正确的使用窗口函数详解

示例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;

MySQL8.0版本如何正确的使用窗口函数详解

是不是很简单？

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);

MySQL8.0版本如何正确的使用窗口函数详解

示例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 UNBOUNDEjsD PRECEDING);

MySQL8.0版本如何正确的使用窗口函数详解

示例3：

SELECT
    t, val,
    LAG(val)        OVER w AS 'lag',
    LEAD(val)       OVER w AS 'lead',
    val - LAG(val)  OVER w ASwww.devze.com 'lag diff',
    val - LEAD(val) OVER w AS 'lead diff'
FROM series
WINDOW w AS (ORDER BY t);

MySQL8.0版本如何正确的使用窗口函数详解

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

MySQL8.0版本如何正确的使用窗口函数详解

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

MySQL8.0版本如何正确的使用窗口函数详解