MySQL 窗口函数：MySQL 窗口函数基本指南

摘要：在本教程中，您将了解 MySQL 窗口函数及其在解决分析查询挑战方面的有用应用。

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

假设我们有一个sales表，它存储按员工和会计年度划分的销售额：

CREATE TABLE sales(
    sales_employee VARCHAR(50) NOT NULL,
    fiscal_year INT NOT NULL,
    sale DECIMAL(14,2) NOT NULL,
    PRIMARY KEY(sales_employee,fiscal_year)
);

INSERT INTO sales(sales_employee,fiscal_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);

SELECT * FROM sales;
Code language: SQL (Structured Query Language) (sql)

从聚合函数开始可能更容易理解窗口函数。

聚合函数将多行数据汇总到单个结果行中。例如，以下SUM()函数返回记录年份中所有员工的总销售额：

SELECT 
    SUM(sale)
FROM
    sales;Code language: SQL (Structured Query Language) (sql)

GROUP BY子句允许您将聚合函数应用于行的子集。例如，您可能想按会计年度计算总销售额：

SELECT 
    fiscal_year, 
    SUM(sale)
FROM
    sales
GROUP BY 
    fiscal_year;Code language: SQL (Structured Query Language) (sql)

MySQL Window Function - vs SUM with GROUP BY

在这两个示例中，聚合函数都会减少查询返回的行数。

与带有GROUP BY子句的聚合函数一样，窗口函数也对行的子集进行操作，但它们不会减少查询返回的行数。

例如，以下查询返回每个员工的销售额以及按会计年度计算的员工总销售额：

SELECT 
    fiscal_year, 
    sales_employee,
    sale,
    SUM(sale) OVER (PARTITION BY fiscal_year) total_sales
FROM
    sales;Code language: SQL (Structured Query Language) (sql)

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

SUM()窗口函数不仅像在使用GROUP BY子句的查询中那样报告按会计年度的总销售额，而且还报告每行的结果，而不是返回的总行数。

请注意，窗口函数是在所有JOIN 、 WHERE 、 GROUP BY和HAVING子句之后以及ORDER BY 、 LIMIT和SELECT DISTINCT之前对结果集执行的。

窗口函数语法

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

window_function_name(expression) OVER ( 
   [partition_defintion]
   [order_definition]
   [frame_definition]
)Code language: SQL (Structured Query Language) (sql)

在这个语法中：

首先，指定窗口函数名称，后跟表达式。
其次，指定OVER子句，它具有三个可能的元素：分区定义、顺序定义和框架定义。

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

window_function_name(expression) OVER()Code language: SQL (Structured Query Language) (sql)

`partition_clause`语法

partition_clause将行分成块或分区。两个分区由分区边界分隔开。

窗口函数在分区内执行，并在跨越分区边界时重新初始化。

partition_clause语法如下所示：

PARTITION BY <expression>[{,<expression>...}]Code language: SQL (Structured Query Language) (sql)

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

`order_by_clause`语法

order_by_clause具有以下语法：

ORDER BY <expression> [ASC|DESC], [{,<expression>...}]Code language: SQL (Structured Query Language) (sql)

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

与PARTITION BY子句类似，所有窗口函数也支持ORDER BY子句。但是，只有对顺序敏感的窗口函数使用ORDER BY子句才有意义。

`frame_clause`语法

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

frame_unit {<frame_start>|<frame_between>}Code language: SQL (Structured Query Language) (sql)

框架是相对于当前行定义的，这允许框架根据当前行在其分区内的位置在分区内移动。

帧单位指定当前行和帧行之间的关系类型。它可以是ROWS或RANGE 。如果帧单位是 ROWS ，则当前行和帧行的偏移量是行号，如果帧单位是ROWS ，则当前行和帧行的偏移量是RANGE值。

frame_start和frame_between定义帧边界。

frame_start包含以下内容之一：

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

frame_between如下：

BETWEEN frame_boundary_1 AND frame_boundary_2   Code language: SQL (Structured Query Language) (sql)

frame_boundary_1和frame_boundary_2可以各自包含以下内容之一：

frame_start ：如前所述。
UNBOUNDED FOLLOWING ：帧在分区中的最后一行结束。
N FOLLOWING ：当前行之后的物理 N 行。

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

RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROWCode language: SQL (Structured Query Language) (sql)

mysql window functions - frame clause bound

MySQL 窗口函数列表

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

姓名	描述
CUME_DIST	计算一组值中某个值的累积分布。
DENSE_RANK	根据`ORDER BY`子句为其分区内的每一行分配排名。它为具有相同值的行分配相同的排名。如果两行或多行具有相同的排名，则排名值的序列中不会有间隙。
第一个值	返回相对于窗口框架中第一行的指定表达式的值。
落后	返回分区中当前行之前的第 N 行的值。如果不存在前一行，则返回 NULL。
LAST_VALUE	返回相对于窗口框架中最后一行的指定表达式的值。
带领	返回分区中当前行之后的第 N 行的值。如果不存在后续行，则返回 NULL。
NTH_VALUE	返回窗口框架第 N 行的参数值
NTILE	将每个窗口分区的行分配到指定数量的排名组中。
PERCENT_RANK	计算分区或结果集中的行的百分位数排名
秩	与`DENSE_RANK()`函数类似，不同之处在于，当两行或更多行具有相同排名时，排名值的序列中存在间隙。
ROW_NUMBER	为其分区内的每一行分配一个连续整数

在本教程中，您了解了 MySQL 窗口函数及其语法。在接下来的教程中，您将更详细地了解每个窗口函数及其应用程序。

窗口函数语法

partition_clause语法

order_by_clause语法

frame_clause语法

MySQL 窗口函数列表

`partition_clause`语法

`order_by_clause`语法

`frame_clause`语法