MySQL :: MySQL 9.0 参考手册 :: 14.20.2 窗口函数概念和语法

版本 9.0

8.4 当前
8.0

8.0 日语

MySQL 9.0 参考手册 / ... / 窗口函数概念和语法

14.20.2 窗口函数概念和语法

本节介绍如何使用窗口函数。示例使用与 GROUPING() 函数讨论中相同的销售信息数据集，该函数在第 14.19.2 节，“GROUP BY 修饰符” 中进行了解释。

mysql> SELECT * FROM sales ORDER BY country, year, product;
+------+---------+------------+--------+
| year | country | product    | profit |
+------+---------+------------+--------+
| 2000 | Finland | Computer   |   1500 |
| 2000 | Finland | Phone      |    100 |
| 2001 | Finland | Phone      |     10 |
| 2000 | India   | Calculator |     75 |
| 2000 | India   | Calculator |     75 |
| 2000 | India   | Computer   |   1200 |
| 2000 | USA     | Calculator |     75 |
| 2000 | USA     | Computer   |   1500 |
| 2001 | USA     | Calculator |     50 |
| 2001 | USA     | Computer   |   1500 |
| 2001 | USA     | Computer   |   1200 |
| 2001 | USA     | TV         |    150 |
| 2001 | USA     | TV         |    100 |
+------+---------+------------+--------+

窗口函数对查询行集执行类似聚合的操作。但是，聚合操作将查询行分组到单个结果行中，而窗口函数为每行产生一个结果。

执行函数评估的行称为当前行。
与当前行相关的查询行，函数评估在其上执行，构成了当前行的窗口。

例如，使用销售信息表，这两个查询执行聚合操作，这些操作为所有行作为组进行聚合生成单个全局总和，以及按国家/地区分组的总和。

mysql> SELECT SUM(profit) AS total_profit
       FROM sales;
+--------------+
| total_profit |
+--------------+
|         7535 |
+--------------+
mysql> SELECT country, SUM(profit) AS country_profit
       FROM sales
       GROUP BY country
       ORDER BY country;
+---------+----------------+
| country | country_profit |
+---------+----------------+
| Finland |           1610 |
| India   |           1350 |
| USA     |           4575 |
+---------+----------------+

相反，窗口操作不会将查询行组折叠为单个输出行。相反，它们为每行产生一个结果。与前面的查询类似，以下查询使用 SUM()，但这次作为窗口函数。

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 |
+------+---------+------------+--------+--------------+----------------+

查询中的每个窗口操作都通过包含 OVER 子句来表示，该子句指定如何将查询行划分为组，以供窗口函数处理。

第一个 OVER 子句为空，它将整个查询行集视为单个分区。因此，窗口函数生成全局总和，但为每行生成。
第二个 OVER 子句按国家/地区对行进行分区，为每个分区（按国家/地区）生成总和。该函数为每个分区行生成此总和。

窗口函数仅允许出现在选择列表和 ORDER BY 子句中。查询结果行是根据 FROM 子句确定的，在 WHERE、GROUP BY 和 HAVING 处理之后，窗口执行发生在 ORDER BY、LIMIT 和 SELECT DISTINCT 之前。

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

有关每个聚合函数的详细信息，请参阅第 14.19.1 节，“聚合函数描述”。

MySQL 还支持仅用作窗口函数的非聚合函数。对于这些函数，OVER 子句是必需的。

CUME_DIST()
DENSE_RANK()
FIRST_VALUE()
LAG()
LAST_VALUE()
LEAD()
NTH_VALUE()
NTILE()
PERCENT_RANK()
RANK()
ROW_NUMBER()

有关每个非聚合函数的详细信息，请参阅第 14.20.1 节，“窗口函数描述”。

作为这些非聚合窗口函数之一的示例，此查询使用 ROW_NUMBER()，它生成每个行在其分区内的行号。在本例中，行按国家/地区编号。默认情况下，分区行是无序的，行编号是非确定性的。要对分区行进行排序，请在窗口定义中包含 ORDER BY 子句。查询使用无序和有序分区（row_num1 和 row_num2 列）来说明省略和包含 ORDER BY 之间的区别。

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 |
+------+---------+------------+--------+----------+----------+

如前所述，要使用窗口函数（或将聚合函数视为窗口函数），请在函数调用之后包含 OVER 子句。 OVER 子句有两种形式。

over_clause:
    {OVER (window_spec) | OVER window_name}

这两种形式都定义了窗口函数应该如何处理查询行。它们的区别在于窗口是在 OVER 子句中直接定义，还是通过对查询中其他地方定义的命名窗口的引用提供。

在第一种情况下，窗口规范直接出现在 OVER 子句中，括号之间。
在第二种情况下，window_name 是查询中其他地方的 WINDOW 子句定义的窗口规范的名称。有关详细信息，请参阅第 14.20.4 节，“命名窗口”。

对于 OVER (window_spec) 语法，窗口规范包含多个部分，所有部分都是可选的。

window_spec:
    [window_name] [partition_clause] [order_clause] [frame_clause]

如果 OVER() 为空，则窗口包含所有查询行，并且窗口函数使用所有行计算结果。否则，括号内出现的子句将确定哪些查询行用于计算函数结果，以及如何对其进行分区和排序。

window_name: 查询中其他地方的 WINDOW 子句定义的窗口的名称。如果 window_name 单独出现在 OVER 子句中，则它完全定义了窗口。如果还给出了分区、排序或框架子句，则它们会修改对命名窗口的解释。有关详细信息，请参阅第 14.20.4 节，“命名窗口”。
partition_clause: PARTITION BY 子句指示如何将查询行划分为组。给定行的窗口函数结果基于包含该行的分区的行。如果省略 PARTITION BY，则只有一个分区，包含所有查询行。

注意

窗口函数的分区与表分区不同。有关表分区的详细信息，请参阅第 26 章，分区。

partition_clause 具有以下语法
```
partition_clause:
    PARTITION BY expr [, expr] ...
```
标准 SQL 要求 PARTITION BY 后面只能跟列名。MySQL 的扩展允许使用表达式，而不仅仅是列名。例如，如果一个表包含一个名为 ts 的 TIMESTAMP 列，标准 SQL 允许 PARTITION BY ts 但不允许 PARTITION BY HOUR(ts)，而 MySQL 允许两者。
order_clause: 一个 ORDER BY 子句表示如何对每个分区中的行进行排序。根据 ORDER BY 子句相等的分区行被视为同行。如果省略 ORDER BY，则分区行无序，没有隐含的处理顺序，所有分区行都是同行。
order_clause 的语法如下
```
order_clause:
    ORDER BY expr [ASC|DESC] [, expr [ASC|DESC]] ...
```
每个 ORDER BY 表达式可以选择性地后跟 ASC 或 DESC 来指示排序方向。如果未指定方向，则默认为 ASC。对于升序排序，NULL 值排在最前面；对于降序排序，NULL 值排在最后面。
窗口定义中的 ORDER BY 适用于单个分区内。要对整个结果集进行排序，请在查询顶层包含 ORDER BY。
frame_clause: 帧是当前分区的子集，帧子句指定如何定义子集。帧子句本身有很多子句。有关详细信息，请参阅第 14.20.3 节，“窗口函数帧规范”。