MySQL :: MySQL 8.3 Reference Manual :: 14.20.2 Window Function Concepts and Syntax

version 8.3

8.0 current

8.0 Japanese

MySQL 8.3 Reference Manual / ... / Window Function Concepts and Syntax

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是查询中的命名窗口规范的名称。有关详细信息，请参阅第 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，则只有一个包含所有查询行的分区。

Note

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

partition_clause 的语法如下：
```
partition_clause:
    PARTITION BY expr [, expr] ...
```
标准 SQL 要求 PARTITION BY 后面只能跟列名。MySQL 的一个扩展是允许表达式，而不仅仅是列名。例如，如果一个表包含一个名为 TIMESTAMP 列 ts，标准 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 值在升序排序时排在最前面，在降序排序时排在最后面。

窗口定义中的 ORDER BY 只应用于单个分区。要对整个结果集进行排序，请在查询的顶级添加 ORDER BY。
frame_clause：框架是当前分区的子集，框架子句指定如何定义该子集。框架子句有许多自己的子子句。有关详细信息，请参阅第 14.20.3 节，“窗口函数框架规范”。

PREV HOME UP NEXT