MySQL 窗口函数 _生活百科

1. 窗口函数概念和语法
窗口函数对一组查询行执行类似聚合的操作。然而，聚合操作将查询行分组到单个结果行，而窗口函数为每个查询行产生一个结果:

函数求值发生的行称为当前行
与发生函数求值的当前行相关的查询行组成了当前行的窗口

相比之下，窗口操作不会将一组查询行折叠到单个输出行。相反，它们为每一行生成一个结果。

SELECTmanufacturer, product, profit,SUM(profit) OVER() AS total_profit,SUM(profit) OVER(PARTITION BY manufacturer) AS manufacturer_profitFROM sales;

文章插图
查询中的每个窗口操作都通过包含一个 OVER 子句来表示，该子句指定如何将查询行划分为组以供窗口函数处理：

第一个 OVER 子句是空的，它将整个查询行集视为一个分区。窗口函数因此产生一个全局和，但对每一行都这样做。
第二个 OVER 子句按 manufacturer 划分行，产生每个分区（每个manufacturer）的总和。该函数为每个分区行生成此总和。

窗口函数只允许在查询列表和 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()

MySQL还支持只能作为窗口函数使用的非聚合函数。对于这些，OVER子句是必须的
CUME_DIST()DENSE_RANK()FIRST_VALUE()LAG()LAST_VALUE()LEAD()NTH_VALUE()NTILE()PERCENT_RANK()RANK()ROW_NUMBER()ROW_NUMBER() 它生成其分区内每一行的行号。默认情况下，分区行是无序的，行编号是不确定的。若要对分区行进行排序，请在窗口定义中包含一个ORDER BY子句。下面的示例中，查询使用无序分区和有序分区(row_num1和row_num2列)来说明省略和包含ORDER BY之间的区别:

SELECTmanufacturer, product, profit,ROW_NUMBER() OVER(PARTITION BY manufacturer) AS row_num1,ROW_NUMBER() OVER(PARTITION BY manufacturer ORDER BY profit) AS row_num2FROM sales;

文章插图
如前所述，要使用窗口函数（或将聚合函数视为窗口函数），需要在函数调用后包含OVER子句。OVER子句有两种形式:
over_clause:{OVER (window_spec) | OVER window_name}这两种形式都定义了窗口函数应该如何处理查询行。它们的区别在于窗口是直接在OVER子句中定义的，还是通过对查询中其他地方定义的命名窗口的引用提供的:

在第一种情况下，窗口规范直接出现在 OVER 子句中的括号之间。
在第二种情况下，window_name 是由查询中其他地方的 WINDOW 子句定义的窗口规范的名称。

对于 OVER (window_spec) 语法，窗口规范有几个部分，都是可选的：
window_spec:[window_name] [partition_clause] [order_clause] [frame_clause]如果 OVER() 为空，则窗口由所有查询行组成，窗口函数使用所有行计算结果。否则，括号中的子句决定了使用哪些查询行来计算函数结果，以及它们是如何分区和排序的：

window_name: 由查询中其他地方的window子句定义的窗口的名称。如果window_name单独出现在OVER子句中，则它完全定义了窗口。如果分区、排序或分帧子句也给出了，它们会修改被命名窗口的解释。
partition_clause: PARTITION BY 子句指示如何将查询行分组。给定行的窗口函数结果基于包含该行的分区的行。如果省略 PARTITION BY ，则有一个由所有查询行组成的分区。partition_clause:PARTITION BY expr [, expr] ...
order_clause: ORDER BY 子句指示如何对每个分区中的行进行排序。根据 ORDER BY 子句相等的分区行被视为对等。如果省略 ORDER BY，则分区行是无序的，没有隐含的处理顺序，并且所有分区行都是对等的。order_clause:ORDER BY expr [ASC|DESC] [, expr [ASC|DESC]] ...

每个ORDER BY表达式后面可以有选择地跟着ASC或DESC来表示排序方向。NULL 值首先进行升序排序，最后进行降序排序。
窗口定义中的 ORDER BY 适用于各个分区。要将结果集作为一个整体进行排序，请在查询顶层包含 ORDER BY 。