文档首页
MySQL 9.0 参考手册
相关文档 下载本手册
PDF (US Ltr) - 40.0Mb
PDF (A4) - 40.1Mb
手册页 (TGZ) - 258.2Kb
手册页 (Zip) - 365.3Kb
Info (Gzip) - 4.0Mb
Info (Zip) - 4.0Mb


MySQL 9.0 参考手册  /  ...  /  窗口函数概念和语法

14.20.2 窗口函数概念和语法

本节介绍如何使用窗口函数。示例使用与 GROUPING() 函数讨论中相同的销售信息数据集,该函数在 第 14.19.2 节,“GROUP BY 修饰符” 中进行了解释。

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 子句确定的,在 WHEREGROUP BYHAVING 处理之后,窗口执行发生在 ORDER BYLIMITSELECT 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_num1row_num2 列)来说明省略和包含 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 节,“命名窗口”

对于 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 节,“命名窗口”

  • partition_clause: PARTITION BY 子句指示如何将查询行划分为组。给定行的窗口函数结果基于包含该行的分区的行。如果省略 PARTITION BY,则只有一个分区,包含所有查询行。

    注意

    窗口函数的分区与表分区不同。有关表分区的详细信息,请参阅 第 26 章,分区

    partition_clause 具有以下语法

    Press CTRL+C to copy
    partition_clause: PARTITION BY expr [, expr] ...

    标准 SQL 要求 PARTITION BY 后面只能跟列名。MySQL 的扩展允许使用表达式,而不仅仅是列名。例如,如果一个表包含一个名为 tsTIMESTAMP 列,标准 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 表达式可以选择性地后跟 ASCDESC 来指示排序方向。如果未指定方向,则默认为 ASC。对于升序排序,NULL 值排在最前面;对于降序排序,NULL 值排在最后面。

    窗口定义中的 ORDER BY 适用于单个分区内。要对整个结果集进行排序,请在查询顶层包含 ORDER BY

  • frame_clause: 帧是当前分区的子集,帧子句指定如何定义子集。帧子句本身有很多子句。有关详细信息,请参阅 第 14.20.3 节,“窗口函数帧规范”