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

version 8.4

8.0 current

8.0 Japanese

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

14.20.2 窗口函数概念和语法

本节描述了如何使用窗口函数。示例使用了与讨论GROUPING()函数的讨论中相同的销售信息数据集：

Press CTRL+C to copy
 
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 |
+------+---------+------------+--------+

窗口函数对查询行集执行聚合操作。然而，而聚合操作将查询行集组合到单个结果行中，窗口函数则为每个查询行产生结果：

对函数评估的行称为当前行。
与当前行相关的查询行，用于函数评估的窗口组成当前行的窗口。

例如，使用销售信息表，这两个查询执行聚合操作，产生所有行的单个全局和每个国家的和：

Press CTRL+C to copy
 
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()，但这次作为窗口函数：

Press CTRL+C to copy
 
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子句的存在或缺失：

Press CTRL+C to copy
 
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子句是必需的：

Press CTRL+C to copy
 
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列）来illustrateomit和包括ORDER BY的区别：

Press CTRL+C to copy
 
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子句有两种形式：

Press CTRL+C to copy
 
over_clause:
    {OVER (window_spec) | OVER window_name}

这两种形式都定义了窗口函数如何处理查询行。它们的区别在于窗口是否在OVER子句中直接定义，还是由对一个名窗口的引用在查询中定义：

在第一个情况下，窗口规范出现在OVER子句中，位于括号之间。
在第二种情况下，window_name是名窗口规范的名称，该规范由查询中的WINDOW子句定义。详见第14.20.4节，“Named Windows”。

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

Press CTRL+C to copy
 
window_spec:
    [window_name] [partition_clause] [order_clause] [frame_clause]

如果OVER()为空，窗口由所有查询行组成，窗口函数使用所有行计算结果。否则，括号中的子句确定了计算函数结果的查询行和如何分区和排序：

window_name：名窗口的名称，该窗口由查询中的WINDOW子句定义。如果window_name在OVER子句中出现，完全定义了窗口。如果分区、排序或框架子句也给出，它们修改了名窗口的解释。详见第14.20.4节，“Named Windows”。
partition_clause：PARTITION BY子句指示如何将查询行分区。窗口函数的结果对于给定的行是基于包含该行的分区的行。如果PARTITION BY省略，所有查询行组成一个分区。

Note

窗口函数的分区与表分区不同。关于表分区的信息，请见第26章，《分区》。

partition_clause的语法如下：
```
Press CTRL+C to copy
 
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的语法如下：
```
Press CTRL+C to copy
 
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