用户留存分析—SQL

关注

原创 2022-04-30 00:04:21 971 阅读 AI 写同款· GEO 优化 ›

大数据开发中如何计算用户留存及SQL示例 first_login` 表包含每个用户的首次登录时间，而 `retention_users` 表包含那些在首次登录后的次日也登录了的用户。在大数据开发中，计算用户留存是评估产品对用户的吸引力和用户粘性的重要手段。用户留存通常是指用户在经过一定时间后仍然继续使用产品的行为，这可以通过留存率来量化。除了次日留存率，还可以计算3日留存率、7日留存率等。假设我们有一个用户行为日志表 `user_log`，包含字段 `user_id`（用户ID）和 `login_time`（登录时间）。### 2. 准备数据。阅读详情

SELECT
	log_day '日期',
	count( user_id_day0 ) '新增数量',
	count( user_id_day1 ) / count( user_id_day0 ) '次日留存率',
	count( user_id_day3 ) / 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 AS log_day, 
				user_id AS user_id_day0 
				FROM 
				data
				GROUP BY user_id 
				ORDER BY log_day 
				) a
		LEFT JOIN data b ON DATEDIFF(b.date, a.log_day ) = 1 
		AND a.user_id_day0 = b.user_id
		LEFT JOIN data c ON DATEDIFF(c.date, a.log_day ) = 2 
		AND a.user_id_day0 = c.user_id
		LEFT JOIN data d ON datediff(d.date, a.log_day ) = 6 
		AND a.user_id_day0 = d.user_id
		LEFT JOIN data e ON datediff(e.date, a.log_day ) = 29 
		AND a.user_id_day0 = e.user_id 
	) temp 
GROUP BY
	log_day
ORDER BY log_day;