HQL求连续活跃用户并完成相关统计（基于等差数列原理）

106 阅读 0 评论 70 点赞

我是靠谱客的博主土豪美女，最近开发中收集的这篇文章主要介绍HQL求连续活跃用户并完成相关统计（基于等差数列原理），觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

需求

有日志如下，写出代码求得所有用户和活跃用户的总数及平均年龄。（活跃用户指连续两天都有访问记录的用户）
日期用户年龄
dt string,user_id string,age int
2019-02-11,test_1,23
2019-02-11,test_2,19
2019-02-11,test_3,39
2019-02-11,test_1,23
2019-02-11,test_3,39
2019-02-11,test_1,23
2019-02-12,test_2,19
2019-02-13,test_1,23
2019-02-15,test_2,19
2019-02-16,test_2,19
create table user_age(dt string,user_id string,age int)row format delimited fields terminated by ‘,’;

分析：
相信所有用户的相关指标并不难求，关键在于活跃用户的求取。这里关于活跃用户的定义是连续两天有访问记录。可能有的小伙伴会想到用lag或lead函数，这里的指标是连续两天确实可以。但如果活跃定义指标业务口径为连续7天、30天有访问记录呢？

在此换一种思路，基于row_number对dt字段进行自然排序（默认升序，注意在此之前先进行数据去重）获得dtrk。而后将dt减去dtrk得到日期diff。结果如下：
在这里插入图片描述

基于等差数列原理，不难发现连续活跃的日期下会得到相同的diff。而后基于user_id,diff进行聚合即可得到用户的连续活跃天数
在这里插入图片描述
而后取出连续天数seqactive最大值，并进行过滤即得到目标。为了方便后续操作，在此添加了状态列state。

最后进行union all两表（全部用户、活跃用户相关指标）

代码如下：

求获取全部用户(连续两天有访问记录)相关指标，采用等等差数列原理
1、去重
select user_id,age,dt from user_age group by user_id,age,dt;  =>t1

2、求所有用户的平均年龄
select  'all' state  ,sum(1) userCount,avg(age) avgAge from 
t1;  =>tt

求获取活跃用户(连续两天有访问记录)相关指标，采用等等差数列原理
1、按照user_id进行分组，按访问时间进行排序row_number
select user_id,dt, row_number() over(partition by user_id order by dt) dtrk ,age from 
t1;  =>t2

2、计算日期与rk的差值
select user_id,dt,dtrk,date_sub(dt,dtrk) as diff ,age from 
t2; =>t3

3、按差值进行聚合统计，日期差值相同的即为连续活跃的记录
select user_id,age ,count(*) seqActive from 
t3 
group by user_id,age,diff; =>t4

4、取出活跃天数最大值，去重
select user_id,age ,max(seqActive)   seqActiveMax from 
t4
group by user_id,age;  =>t5

5、过滤出活跃用户（最大活跃天数大于2的用户），并计算活跃用户总数以及平均年龄,添加状态列
select  'active' state  , sum(1) actuserCount,avg(age) actavgAge from 
t5
where seqActiveMax >=2;  =>t6

6、合并两表（最终结果）

select *from 
(select  'all' state  , sum(1) userCount,avg(age) avgAge from 
(select user_id,age from user_age group by user_id,age)t1)tt
union all
select  'active' state  , sum(1) actuserCount,avg(age) actavgAge from 
(select user_id,age ,max(seqActive)   seqActiveMax from 
(select user_id,age ,count(*) seqActive from 
(select user_id,dt,dtrk,date_sub(dt,dtrk) as diff ,age from 
(select user_id,dt, row_number() over(partition by user_id order by dt) dtrk ,age from 
(select user_id,age,dt from user_age group by user_id,age,dt)t1)t2)t3 
group by user_id,age,diff)t4
group by user_id,age)t5
 where seqActiveMax >=2  ;