MySQL窗口函数

时间:2019-11-20 08:52:26  来源:igfitidea点击:

简介:在本教程中,您将了解MySQL窗口函数及其在解决分析查询难题中的有用应用程序。

从8.0版开始,MySQL已支持窗口功能。
窗口功能使您能够以新的,更轻松的方式并以更好的性能解决查询问题。

假设我们有一个销售表,其中存储了按员工和会计年度划分的销售额:

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;

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

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

SELECT 
    SUM(sale)
FROM
    sales;

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

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

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

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

例如,以下查询返回每个雇员的销售额以及该雇员按会计年度的总销售额:

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

在此示例中,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]
)

使用以下语法:

  • 首先,指定窗口函数名称,后跟一个表达式。

  • 其次,指定具有三个可能元素的OVER子句:分区定义,顺序定义和框架定义。

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

window_function_name(expression) OVER()

partition_clause语法

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

窗口功能在分区内执行,并在跨越分区边界时重新初始化。

partition_clause语法如下所示:

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

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

order_by_clause语法

order_by_clause具有以下语法:

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

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

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

frame_clause语法

框架是当前分区的子集。
要定义子集,请使用frame子句,如下所示:

frame_unit {<frame_start>|<frame_between>}

相对于当前行定义了一个框架,该框架允许框架根据当前行在其分区内的位置在分区内移动。

框架单位指定当前行和框架行之间的关系类型。
它可以是ROWS或RANGE。
如果帧单位是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

frame_boundary_1和frame_boundary_2可以分别包含以下之一:

  • frame_start:如前所述。

  • UNBOUNDED FOLLOWING:框架结束于分区的最后一行。

  • N跟随:当前行之后的实际N行。

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

RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

MySQL窗口功能列表

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

NameDescription
CUME_DISTCalculates the cumulative distribution of a value in a set of values.
DENSE_RANKAssigns a rank to every row within its partition based on the ORDER BY clause. It assigns the same rank to the rows with equal values. If two or more rows have the same rank, then there will be no gaps in the sequence of ranked values.
FIRST_VALUEReturns the value of the specified expression with respect to the first row in the window frame.
LAGReturns the value of the Nth row before the current row in a partition. It returns NULL if no preceding row exists.
LAST_VALUEReturns the value of the specified expression with respect to the last row in the window frame.
LEADReturns the value of the Nth row after the current row in a partition. It returns NULL if no subsequent row exists.
NTH_VALUEReturns value of argument from Nth row of the window frame
NTILEDistributes the rows for each window partition into a specified number of ranked groups.
PERCENT_RANKCalculates the percentile rank of a row in a partition or result set
RANKSimilar to the DENSE_RANK() function except that there are gaps in the sequence of ranked values when two or more rows have the same rank.
ROW_NUMBERAssigns a sequential integer to every row within its partition

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