spark sql窗口函数

窗口函数是spark sql模块从1.4之后开始支持的，主要用于解决对一组数据进行操作，同时为每条数据返回单个结果，比如计算指定访问数据的均值、计算累进和或访问当前行之前行数据等，这些场景使用普通函数实现是比较困难的。
窗口函数计算的一组行，被称为Frame。每一个被处理的行都有一个唯一的frame相关联。

Spark SQL支持三类窗口函数：排名函数、分析函数和聚合函数。以下汇总了Spark SQL支持的排名函数和分析函数。对于聚合函数来说，普通的聚合函数都可以作为窗口聚合函数使用

类别	SQL	DataFrame	含义
排名函数	rank	rank	为相同组的数据计算排名，如果相同组中排序字段相同，当前行的排名值和前一行相同；如果相同组中排序字段不同，则当前行的排名值为该行在当前组中的行号；因此排名序列会出现间隙
排名函数	dense_rank	denseRank	为相同组内数据计算排名，如果相同组中排序字段相同，当前行的排名值和前一行相同；如果相同组中排序字段不同，则当前行的排名值为前一行排名值加1；排名序列不会出现间隙
排名函数	percent_rank	percentRank	该值的计算公式(组内排名-1)/(组内行数-1)，如果组内只有1行，则结果为0
排名函数	ntile	ntile	将组内数据排序然后按照指定的n切分成n个桶，该值为当前行的桶号(桶号从1开始)
排名函数	row_number	rowNumber	将组内数据排序后，该值为当前行在当前组内的从1开始的递增的唯一序号值
分析函数	cume_dist	cumeDist	该值的计算公式为：组内小于等于当前行值的行数/组内总行数
分析函数	lag	lag	用法：lag(input, [offset, [default]]),计算组内当前行按照排序字段排序的之前offset行的input列的值，如果offset大于当前窗口(组内当前行之前行数)则返回default值，default值默认为null
分析函数	lead	lead	用法：lead(input, [offset, [default]])，计算组内当前行按照排序字段排序的之后offset行的input列的值，如果offset大于当前窗口(组内当前行之后行数)则返回default值，default值默认为null

spark支持两种方式使用窗口函数：

• 在SQL语句中的支持的函数中添加OVER语句。例如avg(revenue) OVER (…)
• 使用DataFrame API在支持的函数调用over()方法。例如rank().over(…)。

当一个函数被作为窗口函数使用时，需要为该窗口函数定义相关的窗口规范。窗口规范定义了哪些行会包括到给定输入行相关联的帧(frame)中。窗口规范包括三部分：

• 分区规范：定义哪些行属于相同分区，这样在对帧中数据排序和计算之前相同分区的数据就可以被收集到同一台机器上。如果没有指定分区规范，那么所有数据都会被收集到单个机器上处理。
• 排序规范：定义同一个分区中所有数据的排序方式，从而确定了给定行在他所属分区中的位置
• 帧规范：指定哪些行会被当前输入行的帧包括，通过其他行对于当前行的相对位置实现。
  
  如果使用sql语句的话，PARTITION BY关键字用来为分区规范定义分区表达式、 ORDER BY关键字用来为排序规范定义排序表达式。格式：OVER (PARTITION BY ... ORDER BY ... )。
  如果使用DataFrame API的话，API提供了函数来定义窗口规范。实例如下：

import org.apache.spark.sql.expressions.Window

val windowSpec = Window.partitionBy(...).orderBy(...)

为了分区和排序操作，需要定义帧的开始边界、结束边界和帧的类型，这也是一个帧规范的三部分。一共有五种边界：UNBOUNDED PRECEDING(分区第一行)，UNBOUNDED FOLLOWING(分区最后一行)，CURRENT ROW，<value> PRECEDING(当前行之前行)和<value> FOLLOWING(当前行之后行)。有两种帧类型：ROW帧和RANGE帧。

ROW帧是基于当前输入行的位置的物理偏移量，比如：CURRENT ROW被用作边界表示当前输入行，<value> PRECEDING和<value> FOLLOWING分别表示出现在当前行之前和之后的行数。例如SQL语句ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING表示一个包括当前行、当前行之前1行和之后1行的帧。图示如下：

RANGE帧是基于当前行位置的逻辑偏移。逻辑偏移为当前输入行的排序表达式的值和帧边界行的排序表达式的值之差。也因为这种定义，使用RANGE帧时，只能允许使用单个排序表达式。对于RANGE帧，就边界计算而言，和当前输入行的排序表达式的值相同的行都被认为是相同行。例如：排序表达式为revenue，SQL语句为RANGE BETWEEN 2000 PRECEDING AND 1000 FOLLOWING，则边界为[current revenue value - 2000, current revenue value + 1000]。图示如下：

使用DataFrame API，可以使用以下方法实现ROW帧和RANGE帧

Window.partitionBy(...).orderBy(...).rowBetween(start, end)
Window.partitionBy(...).orderBy(...).rangeBetween(start, end)

下面体验以下窗口函数的强大。
比如有以下产品收入表：


如果我们需要统计每个类别最畅销和次畅销的产品，首先需要基于产品的收入对相同类别的产品进行排名，然后基于排名取出最畅销和次畅销的产品。使用窗口函数实现的sql语句如下：

SELECT product, category, revenue 
FROM (
    SELECT produce, category, revenue, dense_rank() OVER (PARTITION BY category ORDER BY revenue DESC) as rank 
    FROM productRevenue) tmp 
WHERE rank <= 2

执行结果如下：

如果需要统计相同类别中每种产品与该类别中最畅销产品收入差距又该如何呢？首先为了计算差距，需要先找到每个类别中收入最高的产品

SELECT product, category, revenue, (max(revenue) OVER(PARTITION BY category ORDER BY revenue DESC) - revenue) as revenue_diff
FROM produceRevenue

执行结果如下：

原文地址（传送门）