12.21.2 窗口函数概念和语法

# 12.21.2 窗口函数概念和语法

本节介绍如何使用窗口函数。示例使用与讨论中相同的销售信息数据集分组（）作用于第 12.20.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 |
+---------+----------------+

相比之下，窗口操作不会将查询行组折叠为单个输出行。相反，它们为每一行生成一个结果。与前面的查询一样，以下查询使用和（），但这次作为窗口函数：

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 节，“聚合函数描述”.

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

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

有关每个非聚合函数的详细信息，请参阅第 12.21.1 节，“窗口功能说明”.

作为这些非聚合窗口函数之一的示例，此查询使用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_spec:
    [window_name] [partition_clause] [order_clause] [frame_clause]

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

窗口名称: 由 a 定义的窗口的名称窗户查询中其他地方的子句。如果*窗口名称*单独出现在超过子句，它完全定义了窗口。如果还给出了分区、排序或框架子句，它们会修改命名窗口的解释。有关详细信息，请参阅第 12.21.4 节，“命名窗口”.
分区子句：一种分区方式子句指示如何将查询行分组。给定行的窗口函数结果基于包含该行的分区的行。如果分区方式省略，则有一个由所有查询行组成的分区。

笔记

窗口函数的分区不同于表分区。有关表分区的信息，请参阅第 24 章，分区.

*分区子句*有这样的语法：
```
partition_clause:
    PARTITION BY expr [, expr] ...
```
标准 SQL 要求分区方式后面只能跟列名。MySQL 扩展是允许表达式，而不仅仅是列名。例如，如果一个表包含一个时间戳列名为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 节，“窗口函数框架规范”.

← 12.21.1 窗口功能说明 12.21.3 窗函数框架规范 →