《MySQL》MySQL8 新特性之窗口功能个人技术分享

概述
OVER 子句的语法
命名窗口
窗口函数
疑问

概述

窗口功能对一组查询行执行类似聚合的操作。聚合操作将查询行集分组为单个结果行，而窗口则为每个查询行生成一个结果

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

例如，对销售信息表执行以下两个聚合查询：

#所有行作为一个分组计算所有行的总和
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 |
+------+---------+------------+--------+--------------+----------------+

第一个 OVER 子句为空，它将整个查询行集视为单个分区。
第二个 OVER 子句按国家划分行，每个分区（每个国家）生成一个总和。

仅查询列表和 ORDER BY 子句中允许使用窗口功能

OVER 子句的语法

over_clause:
{OVER window_name | OVER (window_spec)}
window_spec:
[window_name] [partition_clause] [order_clause] [frame_clause]
partition_clause:
PARTITION BY expr [, expr] ...
order_clause:
ORDER BY expr [ASC|DESC] [, expr [ASC|DESC]] ...

OVER window_name 引用其他地方定义的窗口的名称
OVER (window_spec) 直接在 OVER 子句中定义窗口

window_spec 的几个部分都是可选，如果 window_spec 为空，则窗口由所有查询行组成，窗口函数使用所有行计算结果。否则，括号内的子句确定使用哪些查询行来计算函数结果以及如何对它们进行分区和排序
- window_name
  
  由其他位置的 WINDOW 子句定义的窗口的名称。如果在 OVER() 子句的括号中单独出现 window_name，它将完全定义窗口。如果还提供了 partition_clause order_clause 或 frame_clause 子句，它们将修改命名窗口。
- partition_clause
  
  表示如何将查询行集划分为组。如果省略了分区，则由所有查询行集组成的单个分区
  
  标准 SQL 只允许 PARTITION BY 后跟列名，MySQL 允许表达式，例如 PARTITION BY HOUR(column_name)
- order_clause
  
  ORDER BY 子句指示如何对每个分区中的行进行排序。在排序上具有相同位置的分区行被视为对等行。如果省略 ORDER BY，则分区行是无序的，所有分区行都是对等的

命名窗口

语法：

WINDOW window_name AS (window_spec)
[, window_name AS (window_spec)] ...
window_spec:
[window_name] [partition_clause] [order_clause] [frame_clause]

window_spec 允许为空，则窗口由所有行组成

命名窗口的定义可以使用 window_name 引用其他窗口名称，允许向前或向后引用，但不允许循环

命名窗口用于替代多个 OVER 子句定义同一窗口，可以定义一次窗口，给它一个名称，使用 OVER 子句引用窗口名称：

SELECT
    val,
    ROW_NUMBER() OVER (ORDER BY val) AS 'row_number',
    RANK() OVER (ORDER BY val) AS 'rank',
    DENSE_RANK() OVER (ORDER BY val) AS 'dense_rank'
FROM numbers;
SELECT
    val,
    ROW_NUMBER() OVER w AS 'row_number',
    RANK() OVER w AS 'rank',
    DENSE_RANK() OVER w AS 'dense_rank'
FROM numbers
    WINDOW w AS (ORDER BY val);

窗口函数

大部分聚合函数可以结合 OVER 子句作为窗口函数使用。

以下是一些仅用作窗口函数的非聚合函数，他们必须结合 OVER 子句使用

排名

带间隙
```
DENSE_RANK() over_clause
```
没有间隙
```
RANK() over_clause
```

返回当前行在分区中的排名。对等行被认为是有联系的，并获得相同的排名。

此函数应与 ORDER BY 一起使用，以便将分区行按所需的顺序排序；否则所有行都是对等行，排名都是 1

排名百分比

PERCENT_RANK() over_clause

返回分区中小于当前行的值（不包括最高值）的百分比。返回值的范围从 0 到 1，表示行相对排名，根据公式 （rank- 1）/（rows- 1） 计算，rank 表示当前行的排名，rows 表示分区内的总行数

此函数应与 ORDER BY 一起使用，以便将分区行按所需的顺序排序；否则所有行都是对等行

累积分布

CUME_DIST() over_clause

累积分布：排名在当前行之前的行数（包括当前行）/分区内的总行数

此函数应与 ORDER BY 一起使用，以便将分区行按所需的顺序排序；否则所有行都是对等行，值为 N/N=1，其中 N 是分区内的总行数

前后比较

LAG(expr [,N [,default]]) over_clause

返回分区内先于当前行 N 行的行的 expr 值。如果没有这样的行，则返回默认值。例如，如果 N 为3，则前两行的返回值为默认值。如果缺省 N 或默认值，则默认值分别为 1 和 NULL

LEAD(expr [,N [,default]]) over_clause

返回分区内跟随当前行之后 N 行的行的 expr 值。如果没有这样的行，则返回默认值。例如，如果 N 为3，则最后两行的返回值为默认值。如果缺省 N 或默认值，则默认值分别为 1 和 NULL

行号

ROW_NUMBER() over_clause

返回当前行在分区中的编号。行数从 1 到分区总行数。

ORDER BY 影响行的编号顺序。没有 ORDER BY ，行编号是不确定的

疑问

有个奇怪的现象无法理解：引用使用 ORDER BY 子句定义的窗口时，带括号和不带括号，count(*) 的统计结果不一样，使用其他子句定义窗口时无此现象

mysql> SELECT id,sort,
    -> count(*) over w as count1,
    -> count(*) over(w) as count2,
    -> row_number() over w as num1,
    -> row_number() over(w) as num2,
    -> rank() over w as rank1,
    -> rank() over(w) as rank2
    -> FROM `blog_tag` window w as (order by sort) limit 10;
+----+------+--------+--------+------+------+-------+-------+
| id | sort | count1 | count2 | num1 | num2 | rank1 | rank2 |
+----+------+--------+--------+------+------+-------+-------+
| 12 |    0 |      2 |     46 |    1 |    1 |     1 |     1 |
| 35 |    0 |      2 |     46 |    2 |    2 |     1 |     1 |
| 17 |    1 |      4 |     46 |    3 |    3 |     3 |     3 |
| 46 |    1 |      4 |     46 |    4 |    4 |     3 |     3 |
| 16 |    2 |      6 |     46 |    5 |    5 |     5 |     5 |
| 44 |    2 |      6 |     46 |    6 |    6 |     5 |     5 |
| 11 |    3 |      8 |     46 |    7 |    7 |     7 |     7 |
| 45 |    3 |      8 |     46 |    8 |    8 |     7 |     7 |
| 23 |    4 |     10 |     46 |    9 |    9 |     9 |     9 |
| 36 |    4 |     10 |     46 |   10 |   10 |     9 |     9 |
+----+------+--------+--------+------+------+-------+-------+