12.21.2 Window Function Concepts and Syntax窗口函数概念和语法

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 12.20.2, “GROUP BY Modifiers”:示例使用与第12.20.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 |
+------+---------+------------+--------+

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

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:与前面的查询一样，下面的查询使用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 |
+------+---------+------------+--------+--------------+----------------+

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:查询中的每个窗口操作都通过包含OVER子句来表示，OVER子句指定如何将查询行划分为组以供窗口函数处理：

Window functions are permitted only in the select list and ORDER BY clause. 窗口函数只允许在SELECT列表和ORDER BY子句中使用。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.查询结果行由FROM子句确定，在WHERE、GROUP BY和HAVING处理之后，窗口执行在ORDER BY、LIMIT和SELECT DISTINCT之前发生。

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

For details about each aggregate function, see Section 12.20.1, “Aggregate Function Descriptions”.有关每个聚合函数的详细信息，请参阅第12.20.1节，“聚合函数描述”。

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

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

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

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. 作为这些非聚集窗口函数之一的示例，此查询使用ROW_NUMBER()，它生成其分区中每一行的行号。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. 要对分区行进行排序，请在窗口定义中包含ORDER BY子句。The query uses unordered and ordered partitions (the row_num1 and row_num2 columns) to illustrate the difference between omitting and including 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 |
+------+---------+------------+--------+----------+----------+

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. 如前所述，要使用窗口函数（或将聚合函数视为窗口函数），请在函数调用后包含OVER子句。The OVER clause has two forms:OVER子句有两种形式：

over_clause:
    {OVER (window_spec) | OVER window_name}

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子句中定义的，还是通过引用查询中其他地方定义的命名窗口提供的：

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

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

If OVER() is empty, the window consists of all query rows and the window function computes a result using all rows. 如果OVER()为空，则窗口由所有查询行组成，窗口函数使用所有行计算结果。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:否则，括号内的子句将确定哪些查询行用于计算函数结果，以及如何对它们进行分区和排序：