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

版本 8.4

9.0
8.0

8.0 日语

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

14.20.2 窗口函数概念和语法

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

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 节“窗口函数框架规范”。