SQL连续登录问题（详细案例分析）

       如果要统计用户活跃度，那就涉及连续登录问题，接下来将举一个简单的例子来详细说明这个问题：

一、创建一些模拟数据

一些测试数据如下：

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(dt='2022-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_dt=='9999-12-31',dt,end_dt),`if`(start_dt<date_sub(dt,30),date_sub(dt,30),start_dt))+1 active_daysfrom tmp.app_user_active_range where end_dt>=date_sub(dt,30)unionselect *,0 from tmp.app_user_active_range where end_dt<date_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_dt=='9999-12-31',dt,end_dt),`if`(start_dt<date_sub(dt,30),date_sub(dt,30),start_dt))+1 active_daysfrom tmp.app_user_active_range where end_dt>=date_sub(dt,30)unionselect *,0 from tmp.app_user_active_range where end_dt<date_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_days>=20 then '[20+'else '0'end 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_login<date_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_dt>=date_sub(dt,30)
)select deviceid,chenmodays from t where chenmodays>0
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_dt>=date_sub(dt,30))t where max_chenmo<dt and end_dt=max_chenmo
) ,z2 as (select deviceid,sum(chenmodays) chenmodays from z group by deviceid
) selectcase when chenmodays>=3 and chenmodays<5 then '[3,5)'when chenmodays>5 then '[5+)'else '[0,3)'end flag,count(1) numfrom z2 group by flag;