hive sql常用开窗函数

1.1开窗函数定义

开窗函数：定义一个行为列，在查询结果上直接新增一列窗口函数值，开窗函数的特征是带有over()。开窗函数从使用目的上可以分为两类：排序开窗函数和聚合开窗函数。

1.2常用排序开窗函数

1.	row_number() over(partition by ……order by ……)，当不加partition by ……order by ……时直接按记录顺序生成从1开始的自然数序列；
2.	rank() over(partition by ……order by ……),排序对于出现相同值会生成相同序号，下一个序号会根据相同值个数发生间断，如序号3出现两次，下一个序号则为5；
3.	dense_rank() over(partition by ……order by ……)，排序对于出现相同值会生成相同序号，下一个序号不会发生间断，如序号3出现两次，下一个序号则为4；
4.	ntile(n) over(partition by ……order by ……)。用于将分组数据按照顺序切分成n片，返回当前切片值。将一个有序的数据集划分为多个桶(bucket)，并为每行分配一个适当的桶数（切片值，第几个切片，第几个分区等概念）。它可用于将数据划分为相等的小切片，为每一行分配该小切片的数字序号。

1.3常用聚合开窗函数

1.count(columns) over(partition by ……order by ……)；
2.max(columns) over(partition by ……order by ……)；
3.min(columns) over(partition by ……order by ……)；
4.sum(columns) over (partition by col1 order by col2 rows between n/unbounded preceding and m following/current row )

如果不指定order by，则将分组内所有值累加；
如果不指定rows between，默认为从起点到当前行；
关键是理解rows between含义，也叫做window子句，用于限定累加值的范围：
preceding:往前                         
following：往后
current rows：当前行                
unbounded：起点
unbounded preceding：表示从前面的起点
unbounded following：表示到后面的终点；

5.avg(columns) over(partition by ……order by ……)；
6.first_value(columns) over(partition by ……order by ……)；
7.last_value(columns) over(partition by ……order by ……)；
8.lag(exp_str,offset,default) over(partition by ……order by ……),可以取每一个分区开始的前N条记录，offset=N，表示偏移量；
9.lead(exp_str,offset,default) over(partition by ……order by ……)，可以取每一个分区结束的后N条记录，offset=N，表示偏移量。

2.1实例

1、通过lead()开窗函数统计同一个用户上传作品的时间间隔

###通过lead()开窗函数统计同一个用户上传作品的时间间隔
select type,team,uin,content_id,upload_time
       ,lead(upload_time,1,'10') over( partition by type,team,uin order by type,team,uin,content_id,upload_time desc) as num
from works.test_to
where upload_time>='2017-03-01' and type='MCN'
group by type,team,uin,content_id,upload_time
order by type,team,uin,content_id,upload_time desc
limit 1000;

2、通过用户每日活跃表结合lag()函数计算，日活、新增、7日留存活跃、7日回流用户

###通过用户每日活跃表结合lag()函数计算，日活、新增、7日留存活跃、7日回流用户
select dt
        , count (distinct uin) as dau
        , count (distinct case when is_new=1 then uin else null end) as new_dau
        , count (distinct case when is_new=0 and gap<=7 then uin else null end) as 7days_active
        , count (distinct case when is_new=0 and gap>7 then uin else null end) as 7days_back_dau
    FROM
(
    select dt, uin, is_new, lag(dt, 1, '2022-07-24') over(partition by uin order by dt) as dt0
        , date_diff('day', cast (lag(dt, 1, '2022-07-24') over(partition by uin order by dt) as DATE), cast (dt as DATE)) as gap
    FROM
    (select cast (dt as varchar) dt, uin, is_new
    from mnv_ads_user.active_details_day ####用户活跃日表
    where dt>='2022-07-25' and dt<='2022-08-30' and uin>'10000'
    group by 1, 2, 3
    ) t1
) t2
    where dt>='2022-08-01' and dt<='2022-08-30'
    group by 1
;