sql 除以_1.七日留存率-SQL实现

b219aec615db45c8376bae0513cc7af6.png

一、背景

留存率:是用户分析的核心指标之一。它也是经典的AARRR模型(海盗模型)中就有一个重要节点——留存(Acquisition)。留存率的计算也是用户分析模型的计算基础,那么如何在数据库中用SQL实现呢?

18829a32c7a0904d262137bbbcd34775.png

二、什么是留存率?

常见的留存率有次日留存、三日留存、7日留存、14日留存、30日留存、90日留存等等,不同产品用户行为的频率是有差别的,留存率的设定也应该视不同产品而定,有些低频的产品用周或月的颗粒度就够了。

留存率计算逻辑:

假如某日新增了100个用户,第二天登录了50个,则次日留存率为50/100=50%,第三天登录了30个,则第二日留存率为30/100=30%,以此类推,第7天登录了10个用户,则7日留存率就是10/100=10%。

以12月1日的新增用户为例,如果12月2日也登录了,就算做次日留存;如果12月3日又登录了,就算做三日留存;12月6日再次登录,就算作7日留存。

三、SQL实现

1、数据说明

计算留存率只需要2个字段:用户ID (user_id) 和 登录日期 (login_time)

  • t_user_login:表名
  • user_id: 用户id,也可用设备ID等
  • login_time:登录日期时间,例如:2020-05-25 16:03:05

2、实现步骤:

  • 步骤一:从数据库中提取user_id和login_time, 并计算 first_day, 用于存储每个用户ID最早登录日期(最小日期);
  • 步骤二:用登录日期-最早登录日期,得到每个登录日期距离最早登录日期的时间间隔,即留存日期;
  • 步骤三:对不同留存日期的user_id进行汇总就是留存人数,除以首日登录人数,就得到了不同留存时间的留存率。

3、SQL实现

SELECT
	log_day '日期',
	count( user_id_day0 ) '新增数量',
	count( user_id_day1 ) / count( user_id_day0 ) '次日留存率',
	count( user_id_day2 ) / count( user_id_day0 ) '3日留存率',
	count( user_id_day7 ) / count( user_id_day0 ) '7日留存率',
	count( user_id_day30 ) / count( user_id_day0 ) '30日留存率' 
FROM
	(
	SELECT DISTINCT
		log_day,
		a.user_id_day0,
		b.user_id AS user_id_day1,
		c.user_id AS user_id_day3,
		d.user_id AS user_id_day7,
		e.user_id AS user_id_day30 
	FROM
		( SELECT DISTINCT 
				Date( login_time ) AS log_day, 
				user_id AS user_id_day0 
				FROM 
				t_user_login 
				GROUP BY user_id 
				ORDER BY log_day 
				) a
		LEFT JOIN t_user_login b ON DATEDIFF( DATE( b.login_time ), a.log_day ) = 1 
		AND a.user_id_day0 = b.user_id
		LEFT JOIN t_user_login c ON DATEDIFF( date( c.login_time ), a.log_day ) = 2 
		AND a.user_id_day0 = c.user_id
		LEFT JOIN t_user_login d ON datediff( date( d.login_time ), a.log_day ) = 6 
		AND a.user_id_day0 = d.user_id
		LEFT JOIN t_user_login e ON datediff( date( e.login_time ), a.log_day ) = 29 
		AND a.user_id_day0 = e.user_id 
	) temp 
GROUP BY
	log_day

代码亲测有效,但数据计算的过程耗时较长,如果有更高效的实现方式,也你的欢迎分享。

代码参考了以下博客:

https://blog.csdn.net/tsyh8797/article/details/103597215?utm_medium=distribute.pc_relevant.none-task-blog-BlogCommendFromMachineLearnPai2-1.nonecase&depth_1-utm_source=distribute.pc_relevant.none-task-blog-BlogCommendFromMachineLearnPai2-1.nonecase

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值