mytecdb.com

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 子句

参考资料:

https://dev.mysql.com/doc/refman/8.0/en/window-function-descriptions.html

文章评论

0条评论