静态网站后台,找大学生做网站,肥城市住房和城乡建设厅网站,国外设计网站及介绍如果要统计用户活跃度#xff0c;那就涉及连续登录问题#xff0c;接下来将举一个简单的例子来详细说明这个问题#xff1a;
一、创建一些模拟数据
一些测试数据如下#xff1a;
deviceid1,2022-10-26,2022-10-26,2022-11-01 deviceid1,2022-10-26,2022-11-03,2022-11-0… 如果要统计用户活跃度那就涉及连续登录问题接下来将举一个简单的例子来详细说明这个问题
一、创建一些模拟数据
一些测试数据如下
deviceid1,2022-10-26,2022-10-26,2022-11-01 deviceid1,2022-10-26,2022-11-03,2022-11-05 deviceid2,2022-10-27,2022-10-27,2022-11-08 deviceid2,2022-10-27,2022-11-10,9999-12-31 deviceid3,2022-10-27,2022-10-27,2022-11-01 deviceid3,2022-10-27,2022-11-04,2022-11-11 deviceid3,2022-10-27,2022-11-14,9999-12-31 deviceid4,2022-10-01,2022-10-01,2022-10-11 deviceid4,2022-10-01,2022-10-13,2022-11-01 deviceid4,2022-10-01,2022-11-03,2022-11-05
-- 1.创建用户活跃区间表
create table tmp.app_user_active_range(deviceid string, -- 设备编号first_login string, -- 首访日期start_dt string, -- 用户活跃区间起始时间end_dt string -- 用户活跃区间结束时间
)
partitioned by(dt string)
row format delimited
fields terminated by ,;-- 2、模拟用户活跃数据将上述数据保存到服务器/root/range.txt路径下 然后load到tmp.app_user_active_range表load data local inpath /root/range.txt overwrite into table tmp.app_user_active_range partition(dt2022-11-23); 二、一些统计用户活跃度的问题
下面是三个简单的指标
1、最近一个月内每个用户的最大连续活跃天数
2、最近一个月内连续活跃[1-10)天的人数[10-20)天的人数[20 天的人数
3、最近一个月内沉默天数超过3天的有多少人超过5天有多少人
三、 指标开发详细解决步骤
1、最近一个月内每个用户的最大连续活跃天数
select * from tmp.app_user_active_range;
with t as (select *,datediff(if(end_dt9999-12-31,dt,end_dt),if(start_dtdate_sub(dt,30),date_sub(dt,30),start_dt))1 active_daysfrom tmp.app_user_active_range where end_dtdate_sub(dt,30)unionselect *,0 from tmp.app_user_active_range where end_dtdate_sub(dt,30)
)select deviceid,max(active_days) max_days from t group by deviceid;
2、最近一个月内连续活跃[1-10)天的人数[10-20)天的人数[20 天的人数
-- 需求2最近一个月内连续活跃[1-10)天的人数[10-20)天的人数[20 天的人数
with t as (select *,datediff(if(end_dt9999-12-31,dt,end_dt),if(start_dtdate_sub(dt,30),date_sub(dt,30),start_dt))1 active_daysfrom tmp.app_user_active_range where end_dtdate_sub(dt,30)unionselect *,0 from tmp.app_user_active_range where end_dtdate_sub(dt,30)
),t1 as (select deviceid,case when active_days between 1 and 9 then [1-10)when active_days between 10 and 19 then [10-20)when active_days20 then [20else 0end as qujianfrom t
)select qujian,count(distinct deviceid) people_num from t1 group by qujian; 3、最近一个月内沉默天数超过3天的有多少人超过5天有多少人
-- 最近一个月内沉默天数超过3天的有多少人超过5天有多少人-- 最终结果
with z as (-- 前半段沉默时间和中间沉默时间with t as (select *,datediff(start_dt,lag(end_dt,1,if(first_logindate_sub(dt,30),date_sub(dt,30),first_login)) over(partition by deviceid order by start_dt))-1 chenmodaysfrom tmp.app_user_active_range where end_dtdate_sub(dt,30)
)select deviceid,chenmodays from t where chenmodays0
union all
-- 后半段沉默时间
select deviceid,datediff(dt,end_dt)-1 chenmodays from (select *,max(end_dt) over(partition by deviceid) max_chenmo from tmp.app_user_active_range where end_dtdate_sub(dt,30))t where max_chenmodt and end_dtmax_chenmo
) ,z2 as (select deviceid,sum(chenmodays) chenmodays from z group by deviceid
) selectcase when chenmodays3 and chenmodays5 then [3,5)when chenmodays5 then [5)else [0,3)end flag,count(1) numfrom z2 group by flag;
