app端-留存分析-周留存率报表开发

用户表
user_id  user_big_type user_mid_type fst_login_date
100001    上海市          徐汇区       2016-01-02


用户登录表
user_id    login_date
100001       2016-03-03


时间维度表
date_id       date_name  date_year
1        1900-01-01    1900
....
40000     2016-01-01    2016


求上海市用户的留存率(用sql语句写以下的逻辑)
维度 第一周的留存率    第二周的留存率    第三周的留存率    第四周的留存率
上海市       50%             30%             20%             15%

注:第几周为第一次登录开始每7天为一周,如用户2016-03-01为首次登录,那么 第一周从2016-03-02至2016-03-08,一天内有多次登录只算一次


/*
--计算日期 2022-03-29                          2-8       9-15   16-22  23-29
user_id    | user_big_type    | fst_login_date | login_date  | w1   | w2   |  w3  |  w4 |fm1| fm2 | fm3 | fm4
100001 |  上海市       |  2022-01-01   | 2022-01-02 | 1      |    0  |  0  |  0   |    1 |  1 |   1  |  1
100001 |  上海市       |  2022-01-01   | 2022-01-08 | 1      |    0  |  0  |  0   |    1 |  1 |   1  |  1
100001 |  上海市       |  2022-01-01   | 2022-01-13 | 0      |    1  |  0  |  0   |    1 |  1 |   1  |  1
100001 |  上海市       |  2022-01-01   | 2022-01-19  | 0      |    0  |  1  |  0  |    1 |  1 |   1  |  1
100001 |  上海市       |  2022-01-01   | 2022-01-30  | 0      |    0  |  0  |  0  |    1 |  1 |   1  |  1
100001 |  上海市       |  2022-01-01   | 2022-01-31  | 0      |    0  |  0  |  0  |    1 |  1 |   1  |  1


100002 |  上海市       |  2022-03-27   | 2022-03-28 | 1      |    0  |  0  |  0  |   1  |  0 |  0   |  0

*/

with tmp as (
--为用户每周是否留存打上标记,如果来了,为1,如果没来为0
--计算周活跃度的分母,计算日期如果离首次登陆日期太近的话,有些数据根本就没有第二周,第三周的数据,会导致分母变大(严格来说应该剔除这些数据)
SELECT
	t1.user_id,
	t1.user_big_type,
	max(if(datediff(t2.login_date,t1.fst_login_date) BETWEEN  1 AND 7  ,1,0)) as w1, --第一周是否活跃的标记,活跃为1,不活跃为0
	max(if(datediff(t2.login_date,t1.fst_login_date) BETWEEN  8 AND 14 ,1,0)) as w2, --第二周是否活跃的标记,活跃为1,不活跃为0
	max(if(datediff(t2.login_date,t1.fst_login_date) BETWEEN 15 AND 21 ,1,0)) as w3, --第三周是否活跃的标记,活跃为1,不活跃为0
	max(if(datediff(t2.login_date,t1.fst_login_date) BETWEEN 22 AND 28 ,1,0)) as w4	--第四周是否活跃的标记,活跃为1,不活跃为0
	max(if(datediff('2022-03-29',t1.fst_login_date)>0,1,0)) as fm1,		--判断用户的第一次登陆时间与当前时间的差值,判断其是否可以参与第一周活跃总人数的计算
	max(if(datediff('2022-03-29',t1.fst_login_date)>7,1,0)) as fm2,		--判断用户的第一次登陆时间与当前时间的差值,判断其是否可以参与第二周活跃总人数的计算
	max(if(datediff('2022-03-29',t1.fst_login_date)>14,1,0)) as fm3,    --判断用户的第一次登陆时间与当前时间的差值,判断其是否可以参与第三周活跃总人数的计算
	max(if(datediff('2022-03-29',t1.fst_login_date)>21,1,0)) as fm4     --判断用户的第一次登陆时间与当前时间的差值,判断其是否可以参与第四周活跃总人数的计算
FROM
	(
	--从用户表中获取上海市的数据
	SELECT
		user_id,
		user_big_type,
		fst_login_date
	FROM tb_user
	WHERE user_big_type='上海市'
	) t1
JOIN
	(
	--一个用户一天可能会登录多次,一天只需要留一天登录数据,jion时可以减少数据量
	SELECT
		user_id,
		login_date
	FROM tb_login
	GROUP BY user_id,login_date
	) t2
ON t1.user_id=t2.user_id
GROUP BY t1.user_id,t1.user_big_type
)

SELECT
	user_big_type as '维度',
	sum(w1)/sum(fm1) as '第一周的留存率',
	sum(w2)/sum(fm2) as '第二周的留存率',
	sum(w3)/sum(fm3) as '第三周的留存率',
	sum(w4)/sum(fm4) as '第四周的留存率',
FROM tmp