Mysql8.0新特性——窗口函数_mysql8.0窗口函数-优快云博客

本文链接：https://blog.youkuaiyun.com/weixin_63944437/article/details/143258432

1. 简介

窗口函数(window functions)，也被称为 “开窗函数”，也叫 OLAP函数 （Online Anallytical Processing，联机分析处理），可对数据库数据进行实时分析处理。

从版本8.0开始，MySQL支持 窗口函数 。窗口函数允许您以新的，更简单的方式解决查询问题，并具有更好的性能。

2. 示例

假设我们有一个 sales表，里面有 员工姓名 、年份、销售额 三个属性：

-- 创建 sales 表
CREATE TABLE sales(
    sales_employee VARCHAR(50) NOT NULL,
    year INT NOT NULL,
    sale DECIMAL(14,2) NOT NULL,
    PRIMARY KEY(sales_employee,year)
);

 -- 初始化数据
INSERT INTO sales(sales_employee,year,sale)
VALUES('Bob',2016,100),
      ('Bob',2017,150),
      ('Bob',2018,200),
      ('Alice',2016,150),
      ('Alice',2017,100),
      ('Alice',2018,200),
       ('John',2016,200),
      ('John',2017,150),
      ('John',2018,250);

+----------------+-------------+--------+
| sales_employee | 			year   | sale   |
+----------------+-------------+--------+
| Alice          |        2016 | 150.00 |
| Alice          |        2017 | 100.00 |
| Alice          |        2018 | 200.00 |
| Bob            |        2016 | 100.00 |
| Bob            |        2017 | 150.00 |
| Bob            |        2018 | 200.00 |
| John           |        2016 | 200.00 |
| John           |        2017 | 150.00 |
| John           |        2018 | 250.00 |
+----------------+-------------+--------+
9 rows in set (0.01 sec)

理解窗口函数可能更容易从聚合函数开始。 聚合函数将来自多行的数据汇总到单个结果行中。例如，以下 SUM() 函数返回所有员工的总销售额：

SELECT SUM(sale) FROM sales;

+-----------+
| SUM(sale) |
+-----------+
|   1500.00 |
+-----------+
1 row in set (0.01 sec)

如果想得到每一年的总销售额，就需要使用 GROUP BY语句。GROUP BY子句允许您将聚合函数应用于行的子集。

SELECT fiscal_year, SUM(sale) FROM sales GROUP BY fiscal_year;

+-------------+-----------+
|      year   | SUM(sale) |
+-------------+-----------+
|        2016 |    450.00 |
|        2017 |    400.00 |
|        2018 |    650.00 |
+-------------+-----------+
3 rows in set (0.01 sec)

从以上SUM()、GROUP BY两个聚合函数的示例中，我们可以发现：聚合函数都会减少查询返回的行数。与带有GROUP BY子句的聚合函数一样，窗口函数也对行的子集进行操作，但它们不会减少查询返回的行数。例如，以下查询返回每个员工的销售额，以及所有年份计算的员工总销售额：

SELECT 
    year, 
    sales_employee,
    sale,
    SUM(sale) OVER (PARTITION BY fiscal_year) total_sales
FROM
    sales;

+-------------+----------------+--------+-------------+
|     year    | sales_employee | sale   | total_sales |
+-------------+----------------+--------+-------------+
|        2016 | Alice          | 150.00 |      450.00 |
|        2016 | Bob            | 100.00 |      450.00 |
|        2016 | John           | 200.00 |      450.00 |
|        2017 | Alice          | 100.00 |      400.00 |
|        2017 | Bob            | 150.00 |      400.00 |
|        2017 | John           | 150.00 |      400.00 |
|        2018 | Alice          | 200.00 |      650.00 |
|        2018 | Bob            | 200.00 |      650.00 |
|        2018 | John           | 250.00 |      650.00 |
+-------------+----------------+--------+-------------+
9 rows in set (0.02 sec)

在此示例中，SUM()函数用作窗口函数，函数对由OVER子句内容定义的一组行进行操作。SUM()应用函数的一组行称为窗口。

3. 窗口函数语法

调用窗口函数的一般语法如下：

window_function_name(expression) 
    OVER (
        [partition_defintion]
        [order_definition]
        [frame_definition]
    )

解释：

首先，指定窗口函数名称，后跟表达式（也可是属性名）。
其次，指定OVER具有三个可能元素的子句： 分区定义 ， 顺序定义 和 帧定义 。

OVER子句后面的开括号和右括号是强制性的，即使没有表达式！例如：

window_function_name(expression) OVER()

3.1. partition_defintion

将partition_clause行分成块或分区。不同的分区由分区边界分隔。窗口函数在分区内执行，并在跨越分区边界时重新初始化。

partition_clause语法如下所示：

PARTITION BY <expression>[{,<expression>...}]

您可以在PARTITION BY子句中指定一个或多个表达式。多个表达式用逗号分隔。

3.2. order_definition

ORDER BY子句指定行在分区中的排序方式。可以在多个键上的分区内对数据进行排序，每个键由表达式指定。多个表达式也用逗号分隔。

order_by_clause语法如下所示：

ORDER BY <expression> [ASC|DESC], [{,<expression>...}]

所有窗口函数都支持 ORDER BY 子句。但是，仅对ORDER BY顺序敏感的窗口函数使用子句才有意义！

3.3. frame_definition

相对于当前行定义帧，这允许帧根据其分区内当前行的位置在分区内移动。帧单位指定当前行和帧行之间的关系类型。它可以是ROWS或RANGE。当前行和帧行的偏移量是行号，如果帧单位是ROWS行值，则行值是帧单位RANGE。

帧是当前分区的子集。要定义子集，请使用 frame 子句，如下所示：

frame_unit {<frame_start>|<frame_between>}

frame_start和frame_between定义帧边界。

将frame_start包含下列之一：

UNBOUNDED PRECEDING：frame从分区的第一行开始。
N PRECEDING：第一个当前行之前的物理N行。N可以是文字数字或计算结果的表达式。
CURRENT ROW：当前计算的行。

frame_between如下：

BETWEEN frame_boundary_1 AND frame_boundary_2

frame_boundary_1和frame_boundary_2可各自含有下列之一：

frame_start：如前所述。
UNBOUNDED FOLLOWING：框架结束于分区的最后一行。
N FOLLOWING：当前行之后的物理N行。

如果frame_definition在OVER子句中未指定，则MySQL默认使用以下帧：

RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

4. MySQL窗口函数列表

下表显示了MySQL中的窗口函数：

名称	描述
CUME_DIST	计算一组值中值的累积分布。
DENSE_RANK	根据ORDER BY子句为其分区中的每一行分配一个排名。它为具有相同值的行分配相同的排名。如果两行或更多行具有相同的等级，则排序值序列中将没有间隙。
FIRST_VALUE	返回指定表达式相对于窗口框架中第一行的值。
LAG	返回分区中当前行之前的第N行的值。如果不存在前一行，则返回NULL。
LAST_VALUE	返回指定表达式相对于窗口框架中最后一行的值。
LEAD	返回分区中当前行之后的第N行的值。如果不存在后续行，则返回NULL。
NTH_VALUE	返回窗口框架第N行的参数值
NTILE	将每个窗口分区的行分配到指定数量的已排名组中。
PERCENT_RANK	计算分区或结果集中行的百分位数
RANK	与DENSE_RANK（）函数类似，只是当两行或更多行具有相同的排名时，排序值序列中存在间隙。
ROW_NUMBER	为其分区中的每一行分配一个连续整数