有点难度的SQL题---计算用户的平均次日留存率

2022年5月22日 5次阅读 来源: xxr_yl

SQL 计算用户的平均次日留存率

目录

需求描述：

现在相关部门想要查看，用户在某一天刷题后，第二天还会来刷题的平均概率，即次日留存率

数据示例：

表 question_practice_detail
《有点难度的SQL题---计算用户的平均次日留存率》
根据示例，看到表question_practice_detail中，有5个字段，分别是：

  • id 自增id
  • device_id 设备号 标识唯一用户
  • question_id 问题id
  • result 问题的结果
  • date 登录时间

查询结果

avg_ret 代表平均次日留存率

《有点难度的SQL题---计算用户的平均次日留存率》

问题分析

1. 次日留存

构建一个新的列

  • 使用lead()over()函数，构造新的时间列，作为下次登录时间 new_date
  • lead函数中，需要对设备号进行分组，时间升序排序
  • 即 LEAD(DATE,1)OVER(PARTITION BY device_id ORDER BY DATE)

如何判断一个人第一天刷题后，第二天还刷题了？

  • 利用时间函数 datediff(new_date，date)
  • 代表 new_date – date , 暂且把这个值 定为rn
  • 如果rn=1，那么就代表相隔一天，用户重新上线刷题
  • 所以rn=1就是我们定位次日留存的入口

2. 平均值

平均值怎么算？

  • (第一天刷题，第二天还刷 的用户数) / (第一天刷题 的用户数) 即可

《有点难度的SQL题---计算用户的平均次日留存率》

我的代码

SELECT 
	SUM(IF(rn=1,1,0))/SUM(IF(rn!=0 OR rn IS NULL,1,0)) avg_ret
FROM 	
(SELECT 
	DISTINCT device_id,
	`date`,
	LEAD(DATE,1)OVER(PARTITION BY device_id ORDER BY DATE)  new_date,
	DATEDIFF(LEAD(DATE,1)OVER(PARTITION BY device_id ORDER BY DATE),DATE) rn
FROM 
	question_practice_detail)a;

《有点难度的SQL题---计算用户的平均次日留存率》

解释一下，次日留存率 avg_ret最后的统计：
SUM(IF(rn=1,1,0)) / SUM(IF(rn!=0 OR rn IS NULL,1,0)) avg_ret

  • SUM(IF(rn=1,1,0)) 代表 满足次日留存的条目数，只要rn=1，就代表第二天这个人又来刷题了，值取1 ，如果rn !=1 就代表次日没来，值取0；

  • 统计 1 的个数 就是符合次日留存的 条目总数；

  • SUM(IF(rn!=0 OR rn IS NULL,1,0)) avg_ret

  • 其中 rn != 0 过滤掉 new_date = date的数据，明显是无意义的；（这里还有别的方法过滤，我试了几种，比如 最内层的表先distinct device_id,再查询，也就是说再嵌套一层，欢迎评论区的小伙伴指点，或者有更好的方法欢迎评论区留言）

  • 由于 rn !=0 会自动将null过滤，但是我们也是需要null统计，因为null也没关系，加上rn IS NULL的值 ，才是完整的 第一天就刷题的人的 总条目数；

欢迎评论区指点

    原文作者：xxr_yl
    原文地址: https://blog.csdn.net/m0_57102326/article/details/122782588
    本文转自网络文章，转载此文章仅为分享知识，如有侵权，请联系博主进行删除。
点赞