本节描述了如何使用窗口函数。示例使用与在GROUPING()函数的讨论中相同的销售信息数据集，参见第 14.19.2 节，“GROUP BY 修饰符”：This section describes how to use window functions. Examples use the same sales information data set as found in the discussion of the GROUPING() function in Section 14.19.2, “GROUP BY Modifiers”:

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

窗口函数对一组查询行执行类似聚合的操作。然而，与将查询行分组为单个结果行的聚合操作不同，窗口函数会为每个查询行生成一个结果：A window function performs an aggregate-like operation on a set of query rows. However, whereas an aggregate operation groups query rows into a single result row, a window function produces a result for each query row:

例如，使用销售信息表，这两个查询执行聚合操作，产生所有行作为一个整体的全局总和，以及按国家分组的总和：For example, using the sales information table, these two queries perform aggregate operations that produce a single global sum for all rows taken as a group, and sums grouped per country:

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()，但这一次作为窗口函数：By contrast, window operations do not collapse groups of query rows to a single output row. Instead, they produce a result for each row. Like the preceding queries, the following query uses SUM(), but this time as a window function:

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子句来表示，该子句指定如何将查询行划分为组，以便由窗口函数处理：Each window operation in the query is signified by inclusion of an OVER clause that specifies how to partition query rows into groups for processing by the window function:

窗口函数仅允许在选择列表和ORDER BY子句中使用。查询结果行是从FROM子句确定的，在WHERE、GROUP BY和HAVING处理之后，而窗口函数的执行发生在ORDER BY、LIMIT和SELECT DISTINCT之前。Window functions are permitted only in the select list and ORDER BY clause. Query result rows are determined from the FROM clause, after WHERE, GROUP BY, and HAVING processing, and windowing execution occurs before ORDER BY, LIMIT, and SELECT DISTINCT.

OVER子句允许用于许多聚合函数，因此这些函数可以作为窗口函数或非窗口函数使用，这取决于是否存在OVER子句：The OVER clause is permitted for many aggregate functions, which therefore can be used as window or nonwindow functions, depending on whether the OVER clause is present or absent:

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 节，“聚合函数说明”。For details about each aggregate function, see Section 14.19.1, “Aggregate Function Descriptions”.

MySQL 还支持仅作为窗口函数使用的非聚合函数。对于这些函数，OVER 子句是必需的：MySQL also supports nonaggregate functions that are used only as window functions. For these, the OVER clause is mandatory:

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

有关每个非聚合函数的详细信息，请参见 第 14.20.1 节，“窗口函数描述”。For details about each nonaggregate function, see Section 14.20.1, “Window Function Descriptions”.

作为这些非聚合窗口函数之一的示例，此查询使用了 ROW_NUMBER()，它生成每个分区内每行的行号。在此情况下，每个国家的行都会被编号。默认情况下，分区行是无序的，行号是非确定性的。要对分区行进行排序，请在窗口定义中包含 ORDER BY 子句。该查询使用无序和有序分区（row_num1 和 row_num2 列）来说明省略和包含 ORDER BY 的区别：As an example of one of those nonaggregate window functions, this query uses ROW_NUMBER(), which produces the row number of each row within its partition. In this case, rows are numbered per country. By default, partition rows are unordered and row numbering is nondeterministic. To sort partition rows, include an ORDER BY clause within the window definition. The query uses unordered and ordered partitions (the row_num1 and row_num2 columns) to illustrate the difference between omitting and including 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 子句有两种形式：As mentioned previously, to use a window function (or treat an aggregate function as a window function), include an OVER clause following the function call. The OVER clause has two forms:

over_clause:
    {OVER (window_spec) | OVER window_name}

这两种形式都定义了窗口函数应如何处理查询行。它们的不同之处在于窗口是直接在 OVER 子句中定义，还是通过引用查询中其他地方定义的命名窗口提供：Both forms define how the window function should process query rows. They differ in whether the window is defined directly in the OVER clause, or supplied by a reference to a named window defined elsewhere in the query:

对于 OVER (window_spec) 语法，窗口规范有几个部分，都是可选的：For OVER (window_spec) syntax, the window specification has several parts, all optional:

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

如果 OVER() 为空，窗口包括所有查询行，窗口函数使用所有行计算结果。否则，圆括号内的子句决定使用哪些查询行来计算函数结果，以及它们如何进行分区和排序：If OVER() is empty, the window consists of all query rows and the window function computes a result using all rows. Otherwise, the clauses present within the parentheses determine which query rows are used to compute the function result and how they are partitioned and ordered: