窗口函数

概述：

窗口函数和聚合函数类似之处在于它也是对一组数据进行分析；但是，窗口函数不是将一组数据汇总为单个结果；而是针对查询中的每一行数据，基于和它相关的一组数据计算出一个结果。

窗口函数在其他数据库中也叫做分析函数，或者联机分析处理（OLAP）函数。

 

定义：

窗口函数与其它函数的语法区别主要在于over子句，语法：

window_function (expr) over (
  partition by ...
  order by ...
  frame_clause
)

 

其中window_function是窗口函数的名称；expr是参数，有些函数不需要参数；over子句包含三个选项：分区（partition  by）、排序（order by）、串口大小（frame_clause）。

 

1、分区选项

partition by 选项用于将数据行拆分成多个分区（组），窗口函数基于每一行数据所在的组进行计算并返回结果，它的作用类似于group 不要分组。如果省略了partition by，所有的数据作为一个组进行计算。

以下示例按照不同的部门分别统计员工的月薪合计：

select name 姓名,salary 月薪, dep 部门
,sum(salary) over (
  partition by dep 
) as 部门月薪合计
from employee 
;

 

注：sql标准要求partition by之后只能使用字段名，不过MySQL允许指定表达式。另外，我们也可以在partition by之后指定多个分组字段，例如同时按照部门和性别进行分组分析；

 

2、排序选项

over子句中的order by选项用于指定分区内的排序方式，与order by子句的作用类似，通常用于数据的排名分析。

注：order by选项用于指定分区内数据的排序，排序字段数据相同的行是对等行（peer）。如果省略order by，分区内的数据不进行排序，不按照固定顺序处理，而且所有数据都是对等行。

 

3、窗口选项

frame_clause选项用于在当前分区内制定一个计算窗口，也就是一个当前行相关的数据子集。

指定了窗口之后，分析函数不在基于分区进行计算，而是基于窗口内的数据进行计算。窗口会随着当前处理的数据行而移动，例如：

  定义一个从分区开始到当前数据行结束的窗口，可以计算截止到每一行的累计总值；

  定义一个从当前行之前N行数据到当前行之后N行数据的窗口，可以计算移动平均值；

 

具体来说，窗口大小的常用选项如下：

{rows | RANGE} frame_start 
{rows | RANGE} between frame_start and frame_end 

 

其中，ROWS表示以行为单位指定窗口的偏移量，range表以数值（例如30分钟）为单位指定窗口的偏移量。frame_start和frame_end分别表示窗口的开始行和结束行，他们的可能取值如下：

CURRENT ROW
UNBOUNDED PRECEDING
UNBOUNDED FOLLOWING
expr PRECEDING
expr FOLLOWING

 

frame_start和frame_end的具体意义如下：

CURRENT ROW：对于ROWS方式，代表了当前行；对于RANGE，代表了当前行的所有对等行。

UNBOUNDED PRECEDING：代表了分区中的第一行。

UNBOUNDEN FOLLOWING：代表了分区中的最后一行。

expr PRECEDING：对于 ROWS 方式，代表了当前行之前的第 expr 行；对于 RANGE，代表了等于当前行的值减去 expr 的所有行；如果当前行的值为 NULL，代表了当前行的所有对等行。

expr FOLLOWING:对于 ROWS 方式，代表了当前行之后的第 expr 行；对于 RANGE，代表了等于当前行的值加上 expr 的所有行；如果当前行的值为 NULL，代表了当前行的所有对等行。

如果只有 frame_start，默认以当前行作为窗口的结束。如果同时指定了两者，frame_start 不能晚于 frame_end，例如 BETWEEN 1 FOLLOWING AND 1 PRECEDING 就是一个无效的窗口。下图可以方便我们理解这些选项的含义：

 CURRENT ROW表示当前正在处理的行；其他的行可以使用相对当前行的位置表示。需要注意，窗口的大小不会超出当前分区的范围。

下示例按照部门统计员工的累计月薪值：

SELECT d.dept_name "部门名称", e.emp_name "姓名", e.salary "月薪",
       sum(salary) OVER (
         PARTITION BY e.dept_id
         ORDER BY e.emp_id
         ROWS UNBOUNDED PRECEDING
       ) AS "部门累计月薪"
FROM employee e
JOIN department d ON (e.dept_id = d.dept_id);

 

  其中，partition by 选项表示按照部门进行分区，order by 选项表示通过工号进行排序；窗口子句ROWS UNBOUNDED PRECEDING指定窗口从分区的第一行开始，默认到当前行结束；因此 SUM 函数计算的是部门内累计到当前行员工为止的月薪合计。

 

二·、命名窗口

窗口函数的over子句除了直接定义三种选项之外，还可以使用一个预定义的窗口变量进行定义。窗口变量使用window子句进行定义，语法位于having和order by之间。

window_function(expr) OVER window_name
WINDOW window_name AS (PARTITION BY ... ORDER BY ... frame_clause)
WINDOW window_name AS (other_window_name)

 

如果查询中多个窗口函数的over子句相同，利用window子句定义一个窗口变量，然后再多个over子句中使用该变量可以简化查询语句。例如：

SELECT d.dept_name "部门名称", e.emp_name "姓名", e.salary "月薪",
       sum(salary) OVER w AS "部门累计月薪",
       count(*) OVER w AS "部门累计人数"
FROM employee e
JOIN department d ON (e.dept_id = d.dept_id)
WINDOW w AS (
     PARTITION BY e.dept_id
     ORDER BY e.emp_id
     ROWS UNBOUNDED PRECEDING
);

 

 

三、常用的窗口函数

常见的窗口函数可以分为以下几类：聚合窗口函数、排名窗口函数以及取值窗口函数。

窗口函数只能出现在 SELECT 列表和 ORDER BY 子句中，查询语句的处理顺序依次为 FROM、WHERE、GROUP BY、聚合函数、HAVING、窗口函数、SELECT DISTINCT、ORDER BY、LIMIT。

 

1、聚合窗口函数

常用的聚合函数，例如 AVG、SUM、COUNT 等，也可以作为窗口函数使用。上文我们已经列举了一些聚合窗口函数的示例，再来看一个使用 AVG 函数计算移动平均值的例子：

SELECT d.dept_name "部门名称", e.emp_name "姓名", e.salary "月薪",
       avg(salary) OVER (
         PARTITION BY e.dept_id
         ORDER BY e.salary
         ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
       ) AS "移动平均月薪"
FROM employee e
JOIN department d ON (e.dept_id = d.dept_id)
;

其中，PARTITION BY 选项表示按照部门进行分区；ORDER BY 选项表示按照月薪从低到高进行排序；窗口子句ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING 指定窗口从当前行的前一行开始，到当前行的下一行结束；因此该函数计算的是每个部门内员工与其前后各一个员工的平均月薪值。

 

移动平均值通常用于处理时间序列的数据。例如，厂家的温度检测器获取了每秒钟的温度，我们可以使用一下窗口计算前五分钟内的平均温度：

avg(temperature) OVER (ORDER BY ts RANGE BETWEEN interval '5 minute' PRECEDING AND CURRENT ROW)

 

 

2、排名窗口函数

排名窗口函数用于对数据进行分组排名，常见的排名窗口函数包括：

ROW_NUMBER：为分区中的每行数据分配一个序列号，序列号从 1 开始分配。

RANK：计算每行数据在其分区中的名次；如果存在名次相同的数据，后续的排名将会产生跳跃。

DENSE_RANK：计算每行数据在其分区中的名次；即使存在名次相同的数据，后续的排名也是连续的值。

PERCENT_RANK：以百分比的形式显示每行数据在其分区中的名次；如果存在名次相同的数据，后续的排名将会产生跳跃。

CUME_DIST：计算每行数据在其分区内的累积分布，也就是该行数据及其之前的数据的比率；取值范围大于 0 并且小于等于 1。

NTILE：将分区内的数据分为 N 等份，为每行数据计算其所在的位置。

 

排名窗口函数不支持动态的窗口大小（frame_clause），而是以当前分区作为分析的窗口。以下示例按照部门分组，并计算每个员工在其部门中的月薪排名，分别使用了 4 个不同的排名函数：

SELECT d.dept_name "部门名称", e.emp_name "姓名", e.salary "月薪",
       ROW_NUMBER() OVER (PARTITION BY e.dept_id ORDER BY e.salary DESC) AS "row_number",
       RANK() OVER (PARTITION BY e.dept_id ORDER BY e.salary DESC) AS "rank",
       DENSE_RANK() OVER (PARTITION BY e.dept_id ORDER BY e.salary DESC) AS "dense_rank",
       PERCENT_RANK() OVER (PARTITION BY e.dept_id ORDER BY e.salary DESC) AS "percent_rank"
FROM employee e
JOIN department d ON (e.dept_id = d.dept_id);

 

 

3、取值窗口函数

取值窗口函数用于返回指定位置上的数据。常见的取值窗口函数包括：

FIRST_VALUE，返回窗口内第一行的数据。

LAST_VALUE，返回窗口内最后一行的数据。

NTH_VALUE，返回窗口内第 N 行的数据。

LAG，返回分区中当前行之前的第 N 行的数据。

LEAD，返回分区中当前行之后第 N 行的数据。

其中，LAG 和 LEAD 函数不支持动态的窗口大小（frame_clause），而是以当前分区作为分析的窗口。