Hive的窗口函数

具体使用语法如下：

分析函数/专用窗口函数 over(partition by 列名 order by 列名 rows between 开始位置 and 结束位置)

partition by 字段(窗口分区)

是对指定的字段进行分组，后续都会以组为单位，把每个分组单独作为一个窗口进行统计分析操作。划分的范围被称为窗口，这也是窗口函数的由来.则整个结果集将作为单个窗口分区；如果没有 ORDER BY，我们则无法定义窗口帧，进而整个分区将作为单个窗口帧进行处理。

rows between 开始位置 and 结束位置(窗口帧)

窗口帧用于从分区中选择指定的多条记录，供窗口函数处理。Hive 提供了两种定义窗口帧的形式：ROWS 和 RANGE。两种类型都需要配置上界和下界。例如：ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW 表示选择分区起始记录到当前记录的所有行。

rows between 常用的参数如下：

① n preceding：往前

② n following：往后

③ current row：当前行

④ unbounded：起点（一般结合preceding，following使用）

1. unbounded preceding：表示该窗口最前面的行（起点）
2. unbounded following：表示该窗口最后面的行（终点）
3. current row：表示执行的当前行

这些参数需要好好记忆，使用例子如下：

rows between unbounded preceding and current row（表示从起点到当前行的数据进行）

rows between current row and unbounded following（表示当前行到终点的数据进行）

rows between unbounded preceding and unbounded following （表示起点到终点的数据）

（3）窗口函数：窗口函数会基于当前窗口帧的记录计算结果。Hive 提供了以下窗口函数：

NTILE(n)，用于将分组数据按照顺序切分成n片，返回当前切片值；

ROW_NUMBER()、RANK() 会为帧内的每一行返回一个序数，区别在于存在字段值相等的记录时，RANK() 会返回相同的序数；

LEAD(col, n), LAG(col, n) 返回当前记录的上n条或下n条记录的字段值；

FIRST_VALUE(col), LAST_VALUE(col) 可以返回窗口帧中第一条或最后一条记录的指定字段值；

COUNT(), SUM(col), MIN(col) 和一般的聚合操作相同。

排序窗口函数

ROW_NUMBER、RANK和DENSE_RANK函数操作  

【函数说明】：

ROW_NUMBER()从1开始，按照顺序，生成分组内记录的序列

RANK() 生成数据项在分组中的排名，排名相等会在名次中留下空位

DENSE_RANK() 生成数据项在分组中的排名，排名相等会在名次中不会留下空位

【执行脚本】：

SELECT   order_id,  user_id,  user_name, order_date,  order_amount, RANK() OVER(PARTITION BY user_id ORDER BY order_amount desc) AS rnk1, DENSE_RANK() OVER(PARTITION BY user_id ORDER BY order_amount desc) AS rnk2, ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY order_amount desc) AS rnk3  FROM ds_hive.ch8_t_order ;

【执行结果】：

聚合窗口函数

count 统计条数,sum 求和,avg 求平均值,max 求最大值,min 求最小值这类的聚合函数的聚合效果都是在窗口内，并且是默认计算第一行到当前行。

sum() over()

SELECT   order_id,  user_id,  user_name, order_date,  order_amount, sum(order_amount) over(partition by user_id order by order_date rows between unbounded preceding and current row) sum_so_far FROM ds_hive.ch8_t_order ;

运行结果

位移窗口函数

LAG函数操作

【函数说明】：LAG(col,n,DEFAULT) 用于统计窗口内往上第n行值

第一个参数为列名，第二个参数为往上第n行（可选，默认为1），第三个参数为默认值（当往上第n行为NULL时候，取默认值，如不指定，则为NULL）。

 LEAD函数操作

【函数说明】：与LAG相反，LEAD(col,n,DEFAULT) 用于统计窗口内往下第n行值

第一个参数为列名，第二个参数为往下第n行（可选，默认为1），第三个参数为默认值（当往下第n行为NULL时候，取默认值，如不指定，则为NULL）

【执行脚本】：

select     order_id,     user_id,     user_name,     order_date,     order_amount,     lag(order_date,1,null) over(partition by user_id order by order_date) last_order_date,     lead(order_date,1,null) over(partition by user_id order by order_date) last_order_date from ds_hive.ch8_t_order

极值窗口函数

 FIRST_VALUE函数操作

【函数说明】：取分组内排序后，截止到当前行，第一个值

LAST_VALUE函数操作

【函数说明】：取分组内排序后，截止到当前行，最后一个值

【执行脚本】：

select     order_id,     user_id,     user_name,     order_date,     order_amount,     first_value(order_date) over(partition by user_id,substr(order_date,1,7) order by order_date) first_date,     last_value(order_date) over(partition by user_id,substr(order_date,1,7) order by order_date rows between unbounded preceding and unbounded following) last_date from ds_hive.ch8_t_order ;

分箱窗口函数

 NTILE函数操作

【函数说明】：NTILE(n)，用于将分组数据按照顺序切分成n片，返回当前切片值

NTILE不支持ROWS BETWEEN，

如果切片不均匀，默认增加第一个切片的分布

一般用来求百分比的操作，比如求前20%，就可以分5个箱。

【执行脚本】：

SELECT         order_id,       user_id,       user_name,       order_date,       order_amount,       NTILE(2) OVER(PARTITION BY user_id ORDER BY order_amount DESC) AS rnk         FROM ds_stu1.ch_t_order_temp2

执行结果 ： 如下会先排序，然后按分桶数等分，rnk=1则取前50%数据，rnk=2则取后50%数据