hive窗口函数记录

一 、 排序 row_number, rank，dense_rank

1. row_number(): 分组内，从1到n连续
2. rank()： 分组内，排序如下：1，2，2，3
3. dense_rank():分组内，排序如下：1，2，2，4

准备数据：

create table personas_test_2
 as 
select "cookid_01" AS cookid , "2017-09-18" as create_time ,1 as pv union all
select "cookid_01" AS cookid , "2017-09-18" as create_time ,9 as pv union all
select "cookid_01" AS cookid , "2017-09-18" as create_time ,8 as pv union all
select "cookid_01" AS cookid , "2017-09-18" as create_time ,7 as pv union all
select "cookid_01" AS cookid , "2017-09-18" as create_time ,6 as pv union all
select "cookid_01" AS cookid , "2017-09-18" as create_time ,5 as pv union all
select "cookid_01" AS cookid , "2017-09-18" as create_time ,4 as pv union all
select "cookid_01" AS cookid , "2017-09-18" as create_time ,3 as pv union all
select "cookid_01" AS cookid , "2017-09-18" as create_time ,2 as pv union all
select "cookid_01" AS cookid , "2017-09-18" as create_time ,0 as pv union all
select "cookid_01" AS cookid , "2017-09-18" as create_time ,10 as pv union all
select "cookid_02" AS cookid , "2017-09-18" as create_time ,1 as pv union all
select "cookid_02" AS cookid , "2017-09-18" as create_time ,9 as pv union all
select "cookid_02" AS cookid , "2017-09-18" as create_time ,8 as pv union all
select "cookid_02" AS cookid , "2017-09-18" as create_time ,7 as pv union all
select "cookid_02" AS cookid , "2017-09-18" as create_time ,6 as pv union all
select "cookid_03" AS cookid , "2017-09-18" as create_time ,5 as pv union all
select "cookid_03" AS cookid , "2017-09-18" as create_time ,4 as pv union all
select "cookid_03" AS cookid , "2017-09-18" as create_time ,3 as pv union all
select "cookid_03" AS cookid , "2017-09-18" as create_time ,2 as pv union all
select "cookid_03" AS cookid , "2017-09-18" as create_time ,0 as pv union all
select "cookid_03" AS cookid , "2017-09-18" as create_time ,10 as pv

sql如下：

select 
   cookid
   ,create_time
   ,pv
   ,row_number() over(partition by create_time order by pv desc) as row_number_rn
   ,rank() over(partition by create_time order by pv desc)       as rank_rn
   ,dense_rank() over(partition by create_time order by pv desc) as dense_rank_rn
from personas_test_2 

结果：

二、sum、avg、min、max、count

preceding：往前
following：往后
current row：当前行
unbounded：起点，unbounded preceding 表示从前面的起点， unbounded following：表示到后面的终点

select 
    cookid
    ,create_time
    ,pv
    ,sum(pv) over(partition by cookid) as total_0 --分组内所有数据
    ,sum(pv) over(partition by cookid order by pv asc) as total_1 --分组内第一行到当前行
    ,sum(pv) over(partition by cookid order by pv asc rows between unbounded preceding and current row) as total_2 --同上total_1
    ,sum(pv) over(partition by cookid order by pv asc rows between 1 preceding and current row)         as total_3 --分组内当前行+前一行
    ,sum(pv) over(partition by cookid order by pv asc rows between 1 preceding and 2 following)         as total_4 --分组内前一行+当前行+往下两行
    ,avg(pv) over(partition by cookid) as avg_5
    ,max(pv) over(partition by cookid) as max_5
    ,min(pv) over(partition by cookid) as min_5
    ,count(1) over(partition by cookid) as count_5
from personas_test_2

结果如下：

三、ntile

ntile(n)，用于将分组数据按照顺序切分成n片，返回当前切片值，如果切片不均匀，默认增加第一个切片的分布。ntile不支持rows between

select 
    cookid
    ,create_time
    ,pv
    ,ntile(11) over(partition by cookid) as ntile_0
    ,ntile(10) over(partition by cookid) as ntile_1
    ,ntile(5) over(partition by cookid)  as ntile_2
from personas_test_2

一般使用场景是取出前百分之几的数据

四、lead,lag,first_value,last_value

1. LEAD(col,n,DEFAULT) 用于统计窗口内往下第n行值： 第一个参数为列名，第二个参数为往下第n行（可选，默认为1），第三个参数为默认值（当往下第n行为NULL时候，取默认值，如不指定，则为NULL）
2. LAG(col,n,DEFAULT) 用于统计窗口内往上第n行值： 第一个参数为列名，第二个参数为往上第n行（可选，默认为1），第三个参数为默认值（当往上第n行为NULL时候，取默认值，如不指定，则为NULL）
3. FIRST_VALUE:取分组内排序后，截止到当前行，第一个值
4. LAST_VALUE:取分组内排序后，截止到当前行，最后一个值

select 
    cookid
    ,create_time
    ,pv
    ,lead(pv,1,999) over(partition by cookid order by pv asc)     as lead_0
    ,lag(pv,1,999) over(partition by cookid order by pv asc)      as lag_1
    ,first_value(pv) over(partition by cookid order by pv asc )   as first_2
    ,last_value(pv) over(partition by cookid order by pv asc )    as last_3
from personas_test_2

五、灵活应用场景：

取用户连续登录最大天数

如下数据：

create table personas_test_3
 as 
select "cookid_01" AS cookid , "2021-03-05" as log_time union all
select "cookid_01" AS cookid , "2021-03-06" as log_time union all
select "cookid_01" AS cookid , "2021-03-07" as log_time union all
select "cookid_01" AS cookid , "2021-03-11" as log_time union all
select "cookid_01" AS cookid , "2021-03-12" as log_time union all
select "cookid_01" AS cookid , "2021-03-13" as log_time union all
select "cookid_01" AS cookid , "2021-03-14" as log_time union all
select "cookid_01" AS cookid , "2021-03-15" as log_time union all
select "cookid_01" AS cookid , "2021-03-18" as log_time union all
select "cookid_01" AS cookid , "2021-03-20" as log_time union all
select "cookid_01" AS cookid , "2021-03-21" as log_time  union all
select "cookid_02" AS cookid , "2021-03-22" as log_time union all
select "cookid_02" AS cookid , "2021-03-20" as log_time union all
select "cookid_02" AS cookid , "2021-03-21" as log_time union all
select "cookid_02" AS cookid , "2021-03-10" as log_time union all
select "cookid_02" AS cookid , "2021-03-11" as log_time union all
select "cookid_03" AS cookid , "2021-03-15" as log_time union all
select "cookid_03" AS cookid , "2021-03-16" as log_time union all
select "cookid_03" AS cookid , "2021-03-17" as log_time union all
select "cookid_03" AS cookid , "2021-03-18" as log_time union all
select "cookid_03" AS cookid , "2021-03-19" as log_time union all
select "cookid_03" AS cookid , "2021-03-20" as log_time 

期望结果：

select
    cookid
    ,max(num) as max_num
FROM
(
    select
        cookid
        ,log_time
        ,row_number() over(partition by cookid order by log_time asc) as rn --按照日期排序
        ,date_sub(log_time,row_number() over(partition by cookid order by log_time asc)) as sub_date --登录日期减去排序序号
        ,count(1) over(partition by cookid,date_sub(log_time,row_number() over(partition by cookid order by log_time asc))) as num  --根据上面sub_date和用户id分组计数
    from personas_test_3
)t
group by cookid