表:
用户登录日期区间表:dws.app_user_act_rng(2020-09-04)前一天
其中9999-12-31代表计算当天也登录过;
用户日活表:dws.app_trf_agr_user(2020-09-05)新一天
需求:
求出用户留存分析:
留存用户定义:新用户登录后,第n日只要登录平台后,就叫n日留存。
实现
步骤1:将当天的用户日活表(2020-09-05)和上一天的用户登录日期区间(2020-09-04)表合并得到当天的用户登录日期区间表(2020-09-05)
合并时考虑以下情况:
情况1:昨日不在,今不在(保留原纪录)
情况2:昨日在,今不在(修改此人的最后区间为昨日日期)
情况3:昨日在,今在(保留原纪录)
情况4:新用户(添加记录)
情况5:昨日不在,今在,(老记录要保留,新添一行,需要单独join)
SQL:
1. 用区间记录表的T-1日 FULL JOIN 日活T日
得到结果part1:情况1-4
SELECT
nvl(a.guid,b.guid) as guid,
nvl(a.first_dt,b.dt) as first_dt,
nvl(a.rng_start,b.dt) as rng_start,
case
when a.rng_end = '9999-12-31' and b.guid is null then a.dt
when a.rng_end is null then '9999-12-31'
else a.rng_end
end as rng_end
FROM dws.app_user_act_rng a
FULL JOIN
dws.app_trf_agr_user b
ON a.dt='2020-09-04' AND b.dt='2020-09-05' AND a.guid = b.guid
2. 从区间记录表的T-1日中过滤出所有昨天没活跃的人, JOIN T日的日
得到结果part2:情况5
SELECT
a.guid as guid,
a.first_dt as first_dt,
b.dt as rng_start,
'9999-12-31' as rng_end
FROM
(
SELECT
guid,
first_dt
FROM dws.app_user_act_rng WHERE dt='2020-09-04'
GROUP BY guid,first_dt
HAVING max(rng_end)!='9999-12-31'
) a
JOIN
dws.app_trf_agr_user b
ON a.guid=b.guid and b.dt='2020-09-05'
3,part1 UNION ALL part2得到最终结果,并插入到用户登录日期区间表的新分区(2020-09-05)
步骤2:求出每个首登日期到几天的第n天,和第n天留存率
SELECT
first_dt as new_dt,
datediff('2020-09-05',first_dt) as ret_days, --第n日
count(if(rng_end='9999-12-31',1,null)) as ret_amt --第n日留存人数
FROM dws.app_user_act_rng
WHERE dt='2020-09-05' AND datediff('2020-09-05',first_dt)<=30
GROUP BY first_dt;
每次算出计算当天和首登日期的差值(n),和相比于首登的n日留存人数