窗口函数的概念和语法
本节介绍如何使用窗口功能。示例使用与GROUPING()在“按修饰符分组”中的功能讨论中所发现的相同的销售信息数据集：
mysql>SELECT *FROM salesORDER 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_profitFROM sales; +-------------- + | total_profit | +-------------- + | 7535 | +-------------- + mysql>SELECT country, SUM(profit)AS country_profitFROM salesGROUP BY countryORDER BY country; +--------- +---------------- + | country | country_profit | +--------- +---------------- + | Finland | 1610 | | India | 1350 | | USA | 4575 | +--------- +---------------- +
相比之下，窗口操作不会将查询行组折叠为单个输出行。相反，它们为每一行产生一个结果。与前面的查询类似，以下查询使用SUM()，但是这次用作窗口函数：
mysql>SELECT year, country, product, profit, SUM(profit)OVER ()AS total_profit, SUM(profit)OVER (PARTITION BY country)AS country_profitFROM salesORDER 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子句后WHERE，GROUP BY以及HAVING处理和开窗执行之前发生ORDER BY，LIMIT和SELECT DISTINCT。
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()
有关每个聚合函数的详细信息，请参见“聚合函数（GROUP BY）”。
MySQL还支持仅用作窗口函数的非聚合函数。对于这些，该OVER子句是强制性的：
CUME_DIST() DENSE_RANK() FIRST_VALUE() LAG() LAST_VALUE() LEAD() NTH_VALUE() NTILE() PERCENT_RANK() RANK() ROW_NUMBER()
有关每个非集合函数的详细信息，请参见“窗口函数优化”。
作为这些非聚合窗口函数之一的示例，此查询使用ROW_NUMBER()，它会生成其分区内每一行的行号。在这种情况下，行按国家/地区编号。默认情况下，分区行是无序的，行编号是不确定的。要对分区行进行排序，请ORDER BY在窗口定义中包含一个子句。该查询使用无序和有序分区（the row_num1和row_num2 columns）来说明省略和包括之间的区别ORDER BY：
mysql>SELECT year, country, product, profit, ROW_NUMBER()OVER (PARTITION BY country)AS row_num1, ROW_NUMBER()OVER (PARTITION BY countryORDER BY year, product)AS row_num2FROM 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子句有两种形式：
over_clause:
{OVER (window_spec) | OVER window_name}
两种形式都定义了窗口函数应如何处理查询行。它们的区别在于窗口是直接在OVER子句中定义，还是由对查询中其他位置定义的命名窗口的引用提供：
- 在第一种情况下，窗口规范直接出现在
OVER括号之间的子句中。 - 在第二种情况下，
window_name是由WINDOW查询中其他子句定义的窗口规范的名称。有关详细信息，请参见“命名为Windows”。
对于语法，窗口规范包括几个部分，所有部分都是可选的：OVER(window_spec)
window_spec:
[window_name] [partition_clause] [order_clause] [frame_clause]
如果OVER()为空，则窗口由所有查询行组成，并且窗口函数使用所有行来计算结果。否则，括号中的子句将确定哪些查询行用于计算函数结果以及它们的划分和排序方式：
window_name：由WINDOW查询中其他子句定义的窗口的名称。如果window_name单独出现在OVER子句中，则它将完全定义窗口。如果还给出了分区，排序或框架子句，则它们会修改命名窗口的解释。有关详细信息，请参见“命名为Windows”。partition_clause：PARTITION BY子句指示如何将查询行分为几组。给定行的窗口函数结果基于包含该行的分区的行。如果PARTITION BY省略，则存在一个由所有查询行组成的分区。注意
窗口函数的分区不同于表分区。有关表分区的信息，请参见分区。
partition_clause具有以下语法：partition_clause:PARTITION BY expr [, expr] ...标准SQL仅需
PARTITION BY后跟列名。MySQL扩展是允许表达式，而不仅仅是列名。例如，如果一个表包含一个TIMESTAMP名为的列ts，则标准SQL允许，PARTITION BY ts但PARTITION BY HOUR(ts)MySQL 不允许。order_clause：ORDER BY子句指示如何对每个分区中的行进行排序。根据ORDER BY子句相等的分区行被视为对等行。如果ORDER BY省略，则分区行是无序的，没有暗含的处理顺序，并且所有分区行都是对等的。order_clause具有以下语法：order_clause:ORDER BY expr [ASC |DESC ] [, expr [ASC |DESC ]] ...每个
ORDER BY表达式可选地可以跟随ASC或DESC指示排序方向。默认为ASC未指定方向。NULL值首先按升序排序，最后按降序排序。一个
ORDER BY单独的分区中的一个窗口定义适用。要对整个结果集进行排序，请ORDER BY在查询顶级添加一个。frame_clause：框架是当前分区的子集，并且框架子句指定如何定义子集。frame子句有许多子句。有关详细信息，请参见“窗口功能框架规范”。