SQL<计算用户的平均次日留存率>的三种解法

#题目
题目：现在运营想要查看用户在某天刷题后第二天还会再来刷题的留存率。请你取出相应数据。
牛客：[SQL29 计算用户的平均次日留存率](https://www.nowcoder.com/share/jump/7965046151739804930327 "SQL29 计算用户的平均次日留存率")
示例：question_practice_detail

| id | device_id | question_id | result | date        |
|----|-----------|-------------|--------|-------------|
| 1  | 2138      | 111         | wrong  | 2021-05-03  |
| 2  | 3214      | 112         | wrong  | 2021-05-09  |
| 3  | 3214      | 113         | wrong  | 2021-06-15  |
| 4  | 6543      | 111         | right  | 2021-08-13  |
| 5  | 2315      | 115         | right  | 2021-08-13  |
| 6  | 2315      | 116         | right  | 2021-08-14  |
| 7  | 2315      | 117         | wrong  | 2021-08-15  |
| 8  | 3214      | 112         | wrong  | 2021-05-09  |
| 9  | 3214      | 113         | wrong  | 2021-08-15  |
| 10 | 6543      | 111         | right  | 2021-08-13  |
| 11 | 2315      | 115         | right  | 2021-08-13  |
| 12 | 2315      | 116         | right  | 2021-08-14  |
| 13 | 2315      | 117         | wrong  | 2021-08-15  |
| 14 | 3214      | 112         | wrong  | 2021-08-16  |
| 15 | 3214      | 113         | wrong  | 2021-08-18  |
| 16 | 6543      | 111         | right  | 2021-08-13  |

根据示例，你的查询应返回以下结果：

|avg_ret|
|-|
|0.3000|
#题解
**⚠️ 下述方法依次更好，若无需递进，可由后往前看。**
####方法1：留存日总数/日期总数——各用户的留存日/日期数量获取——留存日的判断与打标签
**思路：分用户判断与打标签**
1.通过窗口函数lag判断各个用户当前日相较前一日是否留存，并标记。
2.汇总每个用户的留存日总数；汇总每个用户的登陆日总数。
3.汇总所有用户的留存日总数、登陆日总数，相除得到留存率。
<pre>
select
	round((sum(retention_sum) / sum(total_sum)), 4) avg_ret S3.汇总所有用户的留存日数量、登陆日数量，相除得到留存率。
from
 (
 select
 device_id,
			sum(retention_status) retention_sum S2.汇总各用户的留存日总数
 from
 (
 select
 device_id,
 date,
 case
 when date - lag (date, 1) over (
 partition by
 device_id
 order by
 date
 ) = 1 then 1
 else 0
						end as retention_status S1.通过日期相减 判断每个用户 相对于前一天 是否留存；
					留存当日记录为1；因为order by date，所以重复日期的记录不会被多余标记
 from
 question_practice_detail
 ) as sub
 group by
 device_id
 ) as sub_retention
	join ( 连接各用户的留存日数量、登录日数量表
 select
 device_id,
			count(distinct date) as total_sum S2.汇总各用户登陆日总数；
			因为数据不是截选，前后均没登录，考虑留存比较的登陆日数量时，登入日数量不用-1
 from
 question_practice_detail
 group by
 device_id
			) as sub_total 
			on sub_retention.device_id = sub_total.device_id</pre>
			
####方法2：留存日总数/日期总数——用户与日期的去重组合——留存日的判断与打标签
**思路：利用用户与日期的去重组合**
1.获取用户与登陆日的去重组合。
2.判断每个组合的登陆日留存情况，并标记。
3.汇总标记为留存的组合，除以组合总数，得到留存率。
<pre>SELECT 
SUM(IF(retention=1,1,0))/COUNT(device_id) avg_ret 汇总留存的用户、登陆日组合，除以所有用户、登陆日的组合
这里用到IF,因为两日期可能不连续，相减大于1,故只考虑1（留存情况）。
FROM(
 SELECT 
 device_id,
 date,
	date - (lag(date,1) over(PARTITION BY device_id ORDER BY date )) AS retention 获取每个用户、登陆日组合的留存情况
 FROM(
 SELECT DISTINCT device_id,date from question_practice_detail 用户、登陆日组合去重
 ) sub_group
 ORDER BY device_id
) sub_retention
</pre>

####方法3：留存日总数/日期总数——用户与日期的去重组合——通过多字段表连接判断留存
**思路：利用用户与日期的去重组合，利用多字段表连接判断留存。**
1.去重组合用户、日期。
2.left join上datelag列，用多字段表连接进行匹配。
3.对主表进行count并相除。
<pre>
select count(sub2.device_id)/count(sub1.device_id) as avg_ret sub1完整，left join的sub2仅包含匹配的datelag
from(
 select distinct device_id, date datenow from question_practice_detail
 ) as sub1 用户、日期的去重组合
left join( LEFT JOIN: datenow完整呈现，对应展现datelag匹配值
 select distinct device_id, date datelag from question_practice_detail
 ) as sub2 
 on sub1.device_id=sub2.device_id
	and sub1.datenow=sub2.datelag+1 用<a href='https://bluish.net/archives/2194/'>多字段表连接</a>将同用户的datelag与datenow匹配
</pre>

SQL<计算用户的平均次日留存率>的三种解法

http://bluish.net/archives/2218/

作者

bluish

发布时间

2025-02-17

许可协议

 CC BY-SA 4.0

SQL<计算用户的平均次日留存率>的三种解法

SQL<计算用户的平均次日留存率>的三种解法

作者

发布时间

许可协议

添加新评论

最新文章

最近回复

分类

归档