window-functions-usage.md

### 12.21.2 窗口函数概念和语法

[](<>)

本节介绍如何使用窗口函数。示例使用与讨论中相同的销售信息数据集[`分组（）`](miscellaneous-functions.html#function_grouping)作用于[第 12.20.2 节，“GROUP BY 修饰符”](group-by-modifiers.html)：

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

相比之下，窗口操作不会将查询行组折叠为单个输出行。相反，它们为每一行生成一个结果。与前面的查询一样，以下查询使用[`和（）`](aggregate-functions.html#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 |
+------+---------+------------+--------+--------------+----------------+
```

[](<>)

查询中的每个窗口操作都通过包含一个`超过`子句，指定如何将查询行划分为组以供窗口函数处理：

-   首先`超过`子句为空，它将整个查询行集视为单个分区。窗口函数因此产生一个全局和，但对每一行都这样做。

-   第二`超过`子句按国家划分行，产生每个分区（每个国家）的总和。该函数为每个分区行生成此总和。

    仅在选择列表中允许使用窗口函数，并且`订购方式`条款。查询结果行由`从`子句，之后`在哪里`,`通过...分组`， 和`拥有`处理，并且窗口执行发生在之前`订购方式`,`限制`， 和`选择不同的`.

    这`超过`许多聚合函数都允许使用子句，因此可以将其用作窗口函数或非窗口函数，具体取决于是否`超过`从句存在或不存在：


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

有关每个聚合函数的详细信息，请参阅[第 12.20.1 节，“聚合函数描述”](aggregate-functions.html).

MySQL 还支持仅用作窗口函数的非聚合函数。对于这些，`超过`条款是强制性的：

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

有关每个非聚合函数的详细信息，请参阅[第 12.21.1 节，“窗口功能说明”](window-function-descriptions.html).

作为这些非聚合窗口函数之一的示例，此查询使用[`ROW_NUMBER()`](window-function-descriptions.html#function_row-number)，它产生其分区内每一行的行号。在这种情况下，行按国家/地区编号。默认情况下，分区行是无序的，行编号是不确定的。要对分区行进行排序，请包括`订购方式`窗口定义中的子句。该查询使用无序和有序分区（`row_num1`和`row_num2`列）来说明省略和包括之间的区别`订购方式`：

```
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_clause:
    {OVER (window_spec) | OVER window_name}
```

两种形式都定义了窗口函数应该如何处理查询行。它们的区别在于窗口是否直接在`超过`子句，或由对查询中其他地方定义的命名窗口的引用提供：

-   在第一种情况下，窗口规范直接出现在`超过`子句，括号之间。

-   在第二种情况下，*`窗口名称`*是由 a 定义的窗口规范的名称`窗户`查询中其他地方的子句。有关详细信息，请参阅[第 12.21.4 节，“命名窗口”](window-functions-named-windows.html).

    为了`超过 （*`窗口规格`*)`语法，窗口规范有几个部分，都是可选的：


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

如果`超过（）`为空，窗口由所有查询行组成，窗口函数使用所有行计算结果。否则，括号中的子句决定了使用哪些查询行来计算函数结果以及它们是如何分区和排序的：

-   *`窗口名称`*: 由 a 定义的窗口的名称`窗户`查询中其他地方的子句。如果*`窗口名称`*单独出现在`超过`子句，它完全定义了窗口。如果还给出了分区、排序或框架子句，它们会修改命名窗口的解释。有关详细信息，请参阅[第 12.21.4 节，“命名窗口”](window-functions-named-windows.html).

-   *`分区子句`*： 一种`分区方式`子句指示如何将查询行分组。给定行的窗口函数结果基于包含该行的分区的行。如果`分区方式`省略，则有一个由所有查询行组成的分区。

    [](<>)[](<>)

    笔记

    窗口函数的分区不同于表分区。有关表分区的信息，请参阅[第 24 章，*分区*](partitioning.html).

    *`分区子句`*有这样的语法：

    ```
    partition_clause:
        PARTITION BY expr [, expr] ...
    ```

    标准 SQL 要求`分区方式`后面只能跟列名。MySQL 扩展是允许表达式，而不仅仅是列名。例如，如果一个表包含一个[`时间戳`](datetime.html)列名为`ts`, 标准 SQL 许可`按 ts 分区`但不是`按小时（ts）分区`，而 MySQL 两者都允许。

-   *`order_clause`*： 一个`订购方式`子句指示如何对每个分区中的行进行排序。根据`订购方式`子句被认为是对等的。如果`订购方式`被省略，分区行是无序的，没有暗示处理顺序，并且所有分区行都是对等的。

    [](<>)[](<>)

    *`order_clause`*有这样的语法：

    ```
    order_clause:
        ORDER BY expr [ASC|DESC] [, expr [ASC|DESC]] ...
    ```

    每个`订购方式`表达式可选地可以跟随`ASC`要么`DESC`指示排序方向。默认是`ASC`如果没有指定方向。`空值`升序排序时值先排序，降序排序时最后排序。

    一个`订购方式`在窗口定义中适用于各个分区。要将结果集作为一个整体进行排序，请包括`订购方式`在查询顶层。

-   *`框架子句`*：框架是当前分区的子集，框架子句指定如何定义子集。框架子句有许多自己的子句。有关详细信息，请参阅[第 12.21.3 节，“窗口函数框架规范”](window-functions-frames.html).

    [](<>)