php连接mysql8函数库,MySQL 8.0 窗口函数(window functions)

一、窗口函数简介

窗口函数(window functions)是数据库的标准功能之一，主流的数据库比如Oracle，PostgreSQL都支持窗口函数功能，MySQL 直到 8.0 版本才开始支持窗口函数。

窗口函数，简单来说就是对于一个查询SQL，将其结果集按指定的规则进行分区，每个分区可以看作是一个窗口，分区内的每一行，根据其所属分区内的行数据进行函数计算，获取计算结果，作为该行的窗口函数结果值。

窗口函数与group聚合查询类似，都是对一组(分区)记录进行计算，区别在于group对一组记录计算后返回一条记录作为结果，而窗口函数对一组记录计算后，这组记录中每条数据都会对应一个结果。

来看一个例子：

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

+------+---------+------------+--------+--------------+----------------+

| year | country | product | profit | total_profit | country_profit |

+------+---------+------------+--------+--------------+----------------+

| 2000 | Finland | Computer | 1500 | 7535 | 1610 |

| 2000 | Finland | Phone | 100 | 7535 | 1610 |

| 2001 | Finland | Phone | 10 | 7535 | 1610 |

| 2000 | India | Calculator | 75 | 7535 | 1350 |

| 2000 | India | Calculator | 75 | 7535 | 1350 |

| 2000 | India | Computer | 1200 | 7535 | 1350 |

| 2000 | USA | Calculator | 75 | 7535 | 4575 |

| 2000 | USA | Computer | 1500 | 7535 | 4575 |

| 2001 | USA | Calculator | 50 | 7535 | 4575 |

| 2001 | USA | Computer | 1200 | 7535 | 4575 |

| 2001 | USA | Computer | 1500 | 7535 | 4575 |

| 2001 | USA | TV | 100 | 7535 | 4575 |

| 2001 | USA | TV | 150 | 7535 | 4575 |

+------+---------+------------+--------+--------------+----------------+

查询SQL通过 OVER 子句来标记窗口，OVER 子句中的内容可以指定窗口分区的方法。

上述例子中，第一个 OVER 子句中的参数为空，则将整个查询结果集作为一个单一的分区，分区内的每条记录的窗口函数计算结果为整个分区内的字段值求和。

第二个 OVER子 句使用 country 字段作为参数，对查询结果集进行分区，按照 country 分区，分区内求和，作为该分区内每条记录的窗口函数计算结果。

二、窗口函数语法

over_clause:

{OVER (window_spec) | OVER window_name}

window_spec:

[window_name] [partition_clause] [order_clause] [frame_clause]

partition_clause:

PARTITION BY expr [, expr] ...

order_clause:

ORDER BY expr [ASC|DESC] [, expr [ASC|DESC]] ...

如果 OVER() 子句里面的内容为空，则窗口大小为整个查询的结果集，使用结果集中的所有记录计算结果。

如果 OVER() 子句里面的内容不为空，则使用里面指定的窗口分区规则、排序规则对分区内的记录进行分区和排序。

partition_clause，指定如何对查询结果集进行分区，窗口函数基于分区内的记录进行计算，如果没有指定 partition_clause 子句，则对整个查询结果集数据进行计算。SQL标准要求 PARTITION BY 后面只能跟字段名，MySQL扩展支持表达式，比如ts字段为TIMESTAMP类型，可以这样使用PARTITION BY HOUR(ts)。

order_clause，可选，后面可以跟 ASC 或者 DESC 指定排序方向。order by 子句对分区内的记录进行排序。

frame_clause，表示当前分区的一个子集，frame_clause 指定了定义当前分区子集的方法。

三、命名的窗口

窗口函数的窗口能够使用指定的名称来定义，然后可以在 OVER 子句中使用指定的名称来引用窗口。

举个例子，未使用命名窗口的窗口函数如下：

SELECT

val,

ROW_NUMBER() OVER (ORDER BY val) AS 'row_number',

RANK() OVER (ORDER BY val) AS 'rank',

DENSE_RANK() OVER (ORDER BY val) AS 'dense_rank'

FROM numbers;

使用命名窗口改写，如下：

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

使用命名的窗口，SQL更加清晰、简洁，更容易测试窗口的定义，如果要修改窗口的定义，只需要修改WINDOW子句，而不必每个OVER子句都修改。

四、非聚合类窗口函数

MySQL支持的非聚合类窗口函数如下：

CUME_DIST()

DENSE_RANK()

FIRST_VALUE() LAG()

LAST_VALUE()

LEAD() NTH_VALUE()

NTILE()

PERCENT_RANK()

RANK() ，

ROW_NUMBER()

举个例子，通过ROW_NUMBER()函数，计算每条记录在其所在分区内的行号。默认条件下，分区内的记录是未排序的，因此行号也是不确定的，可以使用 order by 子句对窗口函数定义的分区内的数据集进行排序。下面例子 row_num1 未排序，row_num2 排序。

mysql> SELECT

year, country, product, profit,

ROW_NUMBER() OVER(PARTITION BY country) AS row_num1,

ROW_NUMBER() OVER(PARTITION BY country ORDER BY year, product) AS row_num2

FROM sales;

+------+---------+------------+--------+----------+----------+

| year | country | product | profit | row_num1 | row_num2 |

+------+---------+------------+--------+----------+----------+

| 2000 | Finland | Computer | 1500 | 2 | 1 |

| 2000 | Finland | Phone | 100 | 1 | 2 |

| 2001 | Finland | Phone | 10 | 3 | 3 |

| 2000 | India | Calculator | 75 | 2 | 1 |

| 2000 | India | Calculator | 75 | 3 | 2 |

| 2000 | India | Computer | 1200 | 1 | 3 |

| 2000 | USA | Calculator | 75 | 5 | 1 |

| 2000 | USA | Computer | 1500 | 4 | 2 |

| 2001 | USA | Calculator | 50 | 2 | 3 |

| 2001 | USA | Computer | 1500 | 3 | 4 |

| 2001 | USA | Computer | 1200 | 7 | 5 |

| 2001 | USA | TV | 150 | 1 | 6 |

| 2001 | USA | TV | 100 | 6 | 7 |

+------+---------+------------+--------+----------+----------+

另外一个例子，CUME_DIST() 函数，计算分区内，小于等于当前值的记录数占分区内总记录数的比值，取值范围为0~1。示例如下：

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

+------+------------+--------------------+--------------+

| val | row_number | cume_dist | percent_rank |

+------+------------+--------------------+--------------+

| 1 | 1 | 0.2222222222222222 | 0 |

| 1 | 2 | 0.2222222222222222 | 0 |

| 2 | 3 | 0.3333333333333333 | 0.25 |

| 3 | 4 | 0.6666666666666666 | 0.375 |

| 3 | 5 | 0.6666666666666666 | 0.375 |

| 3 | 6 | 0.6666666666666666 | 0.375 |

| 4 | 7 | 0.8888888888888888 | 0.75 |

| 4 | 8 | 0.8888888888888888 | 0.75 |

| 5 | 9 | 1 | 1 |

+------+------------+--------------------+--------------+

五、聚合类窗口函数

窗口函数的 OVER 子句可以和许多聚合函数一起使用，这些聚合函数加上 OVER 子句，就是窗口函数，如果不加 OVER 子句，就是普通的聚合函数。支持窗口函数功能的聚合函数如下：

AVG()

BIT_AND()

BIT_OR()

BIT_XOR()

COUNT()

JSON_ARRAYAGG()

JSON_OBJECTAGG()

MAX()

MIN()

STDDEV_POP(), STDDEV(), STD()

STDDEV_SAMP()

SUM()

VAR_POP(), VARIANCE()

VAR_SAMP()

本文开头的第一个例子，就是使用SUM()聚合函数做窗口计算的，可以翻回去看一下。

六、窗口函数的使用限制

SQL标准加在窗口函数上的一个限制是不能用于update和delete语句来更新行记录。在update和delete语句的子查询中使用窗口函数是允许的。

不支持聚合窗口函数的 DISTINCT 语法

不支持嵌套的窗口函数

不支持依赖于当前行值的动态帧结束点

指定 GROUPS 帧单元能够被解析，但是会产生错误，仅支持 ROWS 和 RANGE

指定帧的 EXCLUDE 子句能够被解析，但是会产生错误

IGNORE NULLS 子句能够被解析，但是会产生错误，目前只支持 RESPECT NULLS 子句

FROM LAST 子句能够被解析，但是会产生错误，目前只支持 FROM FIRST 子句

参考资料：