hive使用窗口函数实现n分位数据分析

hive使用窗口函数实现n分位

实际工作中，我们可能会遇到需要获取高质量的用户、主播或者大R的白名单来进行标签输入或者精准推送促活之类的需求以下以主播中用户观看时长为例

背景

图片说明：

• 横轴： 主播号
• 纵轴： 主播当天用户观看总时长

从上图我们可以得出结论，存在一批长尾用户，也就是观看时长很低的用户有很多个，然后我们的目标是为了筛选出拥有高观看时长的主播，那么我们可以再对明细数据进行处理（明细数据列为： 主播anchor， 观看时长timelong）结果如下，我们可以通过设置百分位来获取高指标的直播，然后进行标签标识或者推荐

hivesql实现

-- 获取一周的数据，以主播为维度进行聚合，获取一周内最大观看时长的主播，使用sum窗口函数进行累加排序，获取累加值到达百分之75之前的主播，最后使用concat,collect_set形成主播簇
select
    '高观看' as tag
	,level2
	,concat_ws(',',collect_set(cast(anchor as string))) as anchors
from
(
select
	level2
	, anchor
	-- 获取topN
	, row_number() over(partition by level2 order by timelong desc) rn
	, count(anchor) over(partition by level2) anchor_num 
	, timelong 
	-- 降序累加观看时长
	, sum(timelong) over(partition by level2 order by timelong desc) as timelong_acm
	-- 获取观看总时长进行可以进行比例获取
	, sum(timelong) over(partition by level2) as level_all_timelong
from
(
	select
		anchor
		,if(level2 in ('内部投放-应用宝', '应用宝微下载'), '应用宝', level2) as level2
		,max(timelong) as timelong
	from
		app::t_level_chat_timelong_new
	where ftime between date_sub(${YYYYMMDD}, 6) and ${YYYYMMDD}
	    and level2 in ('ASO推广'
                      ,'iOS'
                      ,'QQ直播公众号'
                      ,'内部投放-应用宝'
                      ,'应用宝微下载'
                      ,'应用市场-OPPO'
                      ,'应用市场-Vivo'
                      ,'应用市场-华为'
                      ,'应用市场-小米应用')
	group by anchor,if(level2 in ('内部投放-应用宝', '应用宝微下载'), '应用宝', level2)
) a
)t where timelong > 30 and timelong_acm <= level_all_timelong * 0.75
group by level2