SQL进阶--9__ hiveSQL窗口函数应用详细举例总结

0-前言

根据官网的介绍，hive推出的窗口函数功能是对hive sql的功能增强，确实目前用于离线数据分析逻辑日趋复杂，很多场景都需要用到。以下就是对hive窗口函数的一个总结附上案例。

1-理解下什么是WINDOW子句（灵活控制窗口的子集）

PRECEDING：往前
FOLLOWING：往后
CURRENT ROW：当前行
UNBOUNDED：起点（一般结合PRECEDING，FOLLOWING使用）
UNBOUNDED PRECEDING 表示该窗口最前面的行（起点）
UNBOUNDED FOLLOWING：表示该窗口最后面的行（终点）
比如说：
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW（表示从起点到当前行）
ROWS BETWEEN 2 PRECEDING AND 1 FOLLOWING（表示往前2行到往后1行）
ROWS BETWEEN 2 PRECEDING AND 1 CURRENT ROW（表示往前2行到当前行）
ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING（表示当前行到终点）
官网有一段话列出了哪些窗口函数是不支持window子句的，如下图所示：

2-准备需要演示的数据

insert overwrite table dw_tmp.window_function_temp
select 
split(detail,',')[0] as uname
,split(detail,',')[1] as create_time
,split(detail,',')[2] as pv
from
(
    select
    concat('测试用户,2019-10-02,7
    #测试用户,2019-10-05,4
    #测试用户,2019-10-07,5
    #测试用户,2019-10-03,6
    #测试用户,2019-10-04,3
    #测试用户,2019-10-01,3
    #测试用户,2019-10-06,4') as ct_str
) t
lateral view explode(split(ct_str,'#')) t2 as detail;

3-Windowing functions

• 1.LEAD(col,n,DEFAULT) 用于统计窗口内往下第n行值第一个参数为列名，第二个参数为往下第n行（可选，默认为1，不可为负数），第三个参数为默认值（当往下第n行为NULL时候，取默认值，如不指定，则为NULL）
• 2.LAG(col,n,DEFAULT) 用于统计窗口内往上第n行值第一个参数为列名，第二个参数为往上第n行（可选，默认为1，不可为负数），第三个参数为默认值（当往上第n行为NULL时候，取默认值，如不指定，则为NULL）

select 
uname
,create_time
,pv
,lead(pv,1,-9999) over (partition by uname order by create_time