窗口函数

1. 什么是窗口函数

窗口可以理解为记录集合，窗口函数是在满足某种条件的记录集合上执行的特殊函数。

• 静态窗口：随着记录不同，窗口大小都是固定的
• 动态窗口：不同的记录对应着不同的窗口

1.1 窗口函数和普通聚合函数

窗口函数	聚合函数
每条记录都会执行，有几条记录执行完还是几条	多条记录聚合为一条
每组保留多个值	每组保留一个值
	聚合函数可存在于窗口函数中

1.2 窗口函数语法

窗口函数的作用域位于over()子句中，窗口函数有3个元素：分区、排序和框架。

窗口函数的一般框架

<窗口函数>

OVER (

[PARTITION BY <列清单>] 

[ORDER BY <排序用清单列>] [ASC/DESC] 

(ROWS | RANGE)<范围条件>

)

默认情况下，用于计算的一组行（“窗口”）是整个数据集，可以使用ORDER BY子句进行排序。然后使用PARTITION BY子句将窗口缩小到数据集内的特定组。

• 函数子句：
  指明具体操作，如sum求和，first_value取第一个值；

• partition by子句：
  指明分区字段，如果没有，则将所有数据作为一个分区；

• order by子句：
  指明了每个分区排序的字段和方式,也是可选的，默认是按照表中的顺序；

• 窗口子句：
  指明相对当前记录的计算范围，可以向上（preceding），可以向下（following）,也可以使用between指明上下边界的值，没有的话默认为当前分区。

滑动窗口的范围指定方式有：基于行和基于范围

• 基于行：

  通常使用BETWEEN frame_start AND frame_end语法来表示行范围，frame_start和frame_end可以支持如下关键字，来确定不同的动态行记录
  。CURRENT ROW 边界是当前行，一般和其他范围关键字一起使用
  。UNBOUNDED PRECEDING 边界是分区中的第一行
  。UNBOUNDED FOLLOWING 边界是分区中的最后一行
  。expr PRECEDING 边界是当前行减去expr的值
  。expr FOLLOWING 边界是当前行加上expr的值

比如，下面都是合法的范围：

rows BETWEEN 1 PRECEDING AND 1 FOLLOWING 窗口范围是当前行、前一行、后一行一共三行记录。
rows UNBOUNDED FOLLOWING 窗口范围是当前行到分区中的最后一行。
rows BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING 窗口范围是当前分区中所有行，等同于不写。

• 基于范围：

和基于行类似，但有些范围不是直接可以用行数来表示的，比如希望窗口范围是一周前的订单开始，截止到当前行，则无法使用rows来直接表示，此时就可以使用范围来表示窗口：INTERVAL 7 DAY PRECEDING。Linux中常见的最近1分钟、5分钟负载是一个典型的应用场景。

有的函数不管有没有frame子句，它的窗口都是固定的，也就是静态窗口，这些函数包括如下：

• CUME_DIST()
• DENSE_RANK()
• LAG()
• LEAD()
• NTILE()
• PERCENT_RANK()
• RANK()
• ROW_NUMBER()

1. 分区函数

partition by语句定义，对当前计算的窗口函数进行限制，如果没有指定partition by子句，窗口就没有限制，即如果没有指定分区就是将整个查询结果当做一个分区。

2. 排序函数

排序元素定义计算的顺序，如果与分区有关，是对分区内排序，但对于不同的函数（排序窗口函数和聚合窗口函数）有点不同

3. 框架元素

框架是一个筛选器，对分区内的进一步的限制。适合于聚合函数、偏移函数等。

2. 窗口函数的类型

窗口函数有4种函数类型：排序函数、偏移函数、聚合函数和分布函数。

2.1 排序函数

• row_number()，rank()，dense_rank()
  不需要指定参数
• ntile()
  把窗口分区里的数据行分成数量大致相等的块，块的个数由输入的参数决定

2.2 偏移函数

• lead()，lag()
  偏移量相对于当前的行
• first_value()，last_value()，nth_value()
  偏移量相对于窗口的开始和结尾

2.3 聚合函数

• sum()，count()，avg()，min()，max()

2.4 分布函数

• cume_dist()，percent_rank()