MySQL窗口函数定义、用法（8.0+版本）

mysql英文官方参考手册文档链接（窗口函数）如下：https://dev.mysql.com/doc/refman/8.0/en/window-functions-usage.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 |
+---------+----------------+

相比之下，窗口操作不会将查询行组折叠为单个输出行。相反，它们为每一行生成一个结果。与前面的查询一样，下面的查询使用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 |
+------+---------+------------+--------+--------------+----------------+

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

• 第一个OVER子句是空的，它将整个查询行集视为一个单独的分区。因此，窗口函数产生一个全局和，但对每一行都是这样做的。
• 第二个OVER子句按国家对行进行分区，生成每个分区(每个国家)的总和。这个函数为每个分区行产生这个和。

窗口函数只允许在select列表和ORDER BY子句中使用。
查询结果行从from子句中确定，在WHERE、GROUP BY和HAVING处理之后，窗口执行在ORDER BY、LIMIT和SELECT DISTINCT之前。

许多聚合函数都允许OVER子句，因此可以将其用作窗口函数或非窗口函数，这取决于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()

这部分详细使用参考：https://dev.mysql.com/doc/refman/8.0/en/aggregate-functions.html

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

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

这部分的详细使用参考：https://dev.mysql.com/doc/refman/8.0/en/window-function-descriptions.html

作为这些非聚合窗口函数中的一个示例，该查询使用ROW_NUMBER()，它生成其分区中每一行的行号。在本例中，行按国家编号。默认情况下，分区行是无序的，行编号是不确定的。要对分区行进行排序，请在窗口定义中包含一个ORDER BY子句。查询使用无序分区和有序分区(row_num1和row_num2列)来说明省略ORDER BY和包含ORDER BY之间的区别：

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从句有两种形式：

over_clause:
    {OVER (window_spec) | OVER window_name}

这两种形式都定义了window函数应该如何处理查询行。它们的区别在于，窗口是直接在OVER子句中定义的，还是由查询中其他地方定义的指定窗口的引用提供的:

• 在第一种情况下，窗口规范直接出现在OVER子句的圆括号之间。
• 在第二种情况下，window_name是窗口规范的名称，该窗口规范是由查询中其他地方的window子句定义的。具体请参见12.21.4“名称为Windows”章节。

对于OVER (window_spec)语法，窗口规范有几个部分，都是可选的：

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

如果OVER()为空，则窗口由所有查询行组成，窗口函数使用所有行计算结果。否则，括号内的子句将确定哪些查询行用于计算函数结果，以及如何对它们进行分区和排序：

• window_name:由查询中其他地方的window子句定义的窗口名称。如果window_name单独出现在OVER子句中，则它完全定义了窗口。如果还给出了分区、排序或框架子句，它们会修改已命名窗口的解释。具体请参见12.21.4“名称为Windows”章节。
• partition_clause: PARTITION BY子句表示如何将查询行划分为组。对于给定行的窗口函数结果是基于包含该行的分区的行。如果省略PARTITION BY，则存在一个由所有查询行组成的单一分区。（窗口函数的分区不同于表分区，勿混淆）。分区可以这样写：

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

标准SQL只要求PARTITION BY后面跟着列名。MySQL扩展允许表达式，而不仅仅是列名。例如，如果一个表包含一个名为ts的TIMESTAMP列，标准SQL允许PARTITION BY ts，但不允许PARTITION BY HOUR(ts)，而MySQL允许这两种情况。

• order_clause: ORDER BY子句表示如何对每个分区中的行进行排序。根据ORDER BY子句相等的分区行被认为是对等的。如果省略ORDER BY，则分区行是无序的，没有隐含的处理顺序，所有分区行都是对等的。
  order by的语法形式如下：

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

每个ORDER BY表达式后面都可以跟着ASC或DESC，以指示排序方向。如果没有指定方向，则默认为ASC。NULL值首先进行升序排序，最后进行降序排序。窗口定义中的ORDER BY应用于各个分区。要对结果集进行整体排序，请在查询的顶层包含一个ORDER BY。

• frame_clause:帧是当前分区的子集，frame子句指定了如何定义该子集。框架条款有许多子条款。具体请参见12.21.3窗口函数框规范。