MySQL实现7天,15天,30天,90天用户留存数量查询

糊涂涂糊涂涂

已于 2023-06-12 18:15:35 修改

阅读量2k

点赞数

分类专栏： MYSQL 文章标签： mysql 数据库 sql

于 2023-06-09 16:51:31 首次发布

本文链接：https://blog.csdn.net/m0_74444744/article/details/131130481

版权

MYSQL 专栏收录该内容

5 篇文章 0 订阅

订阅专栏

如何计算用户留存率

新增用户留存率=新增用户中登录用户数/新增用户数*100%（一般统计周期为天）

新增用户数：在某个时间段（一般为第一整天）新登录应用的用户数；

登录用户数：登录应用后至当前时间，至少登录过一次的用户数；

第N日留存：指的是新增用户日之后的第N日依然登录的用户占新增用户的比例

第1日留存率（即“次留”）：（当天新增的用户中，新增日之后的第1天还登录的用户数）/第一天新增总用户数；

第3日留存率：（当天新增的用户中，新增日之后的第3天还登录的用户数）/第一天新增总用户数；

第7日留存率：（当天新增的用户中，新增日之后的第7天还登录的用户数）/第一天新增总用户数；

第30日留存率：（当天新增的用户中，新增日之后的第30天还登录的用户数）/第一天新增总用户数；

查询SQL

SELECT
	create_time as '日期',
	count(id) '新增数量',
	count(id1)  '7日留存',
	count(id2) '15日留存',
	count(id3) '30日留存',
	count(id4)  '90日留存'
FROM
(SELECT	DISTINCT
	u.create_time,u.idx_user_id as id,u1.idx_user_id as id1,u2.idx_user_id as id2,u3.idx_user_id as id3,u4.idx_user_id as id4
FROM
(SELECT DISTINCT 
	min(Date(create_time)) create_time , idx_user_id,cost_status
FROM
	tbconsumerecord
WHERE 
cost_status=1 
GROUP BY 
	idx_user_id) u
LEFT JOIN 
	tbconsumerecord u1 
ON DATEDIFF(DATE(u1.create_time),u.create_time) = 7 AND u.idx_user_id=u1.idx_user_id
LEFT JOIN 
	tbconsumerecord u2 
ON DATEDIFF(DATE(u2.create_time),u.create_time) = 15 AND u.idx_user_id=u2.idx_user_id
LEFT JOIN 
	tbconsumerecord u3 
ON DATEDIFF(DATE(u3.create_time),u.create_time) = 30 AND u.idx_user_id=u3.idx_user_id
LEFT JOIN 
	tbconsumerecord u4
ON DATEDIFF(DATE(u4.create_time),u.create_time) = 90 AND u.idx_user_id=u4.idx_user_id
)as user
GROUP BY
create_time;

函数解释

MySQL DATEDIFF函数计算两个DATE，或TIMESTAMP值之间的天数。

MySQL DATEDIFF函数的语法如下：
用于返回计算两个日期指定单位的时间差(指定单位可以是年，季度，月，星期，天数，小时，分钟，秒等等)
SELECT DATEDIFF('2023-06-12', '2023-06-05') as Intervaltime;

输出结果:7

##日期函数 DATE_ADD(date,INTERVAL expr unit) date 日期 INTERVAL expr 间隔

负数往前正数往后 unit 时间单位 day 天 hour 小时 minute 分钟

##day 天

select date_add('2023-06-10',INTERVAL '5' day) as afterDay;

select date_add('2023-06-10',INTERVAL '-5' day) as beforeDay;

##hour 小时

select date_add('2023-06-10',INTERVAL '5' hour) as afterHour;

select date_add('2023-06-10',INTERVAL '-5' hour) as beforeHour;

##minute 分钟

select date_add('2023-06-10',INTERVAL '5' minute) as afterMinute;

select date_add('2023-06-10',INTERVAL '-5' minute) as beforeMinute;

##每天新增用户数据
select a.date, count(b.user_id) from
-- 日期表
(select distinct Date(create_time) as date from tbconsumerecord) a left join
-- 左连接日期不全的查询结果
(select idx_user_id user_id, min(Date(create_time)) as date from tbconsumerecord WHERE cost_status=1 group by idx_user_id ) b
on a.date = b.date
group by a.date;

select *
FROM tbconsumerecord
WHERE Date(create_time)='2023-03-06' or Date(create_time)='2023-04-14';

第一橙色部分含义:先查询该表所有日期

第二橙色部分含义:查询用户最小登录日期(当前用户第一次登录即为新增用户)

##每天新增用户数据
select a.date, count(b.user_id) as count from
-- 日期表
(select distinct Date(create_time) as date from tbconsumerecord
union
select distinct Date(create_time) as date from tbconsumerecord_old ) a left join
-- 左连接日期不全的查询结果
(select idx_user_id user_id, max(Date(create_time)) as date from tbconsumerecord WHERE cost_status=1 group by idx_user_id
union
select idx_user_id user_id, max(Date(create_time)) as date from tbconsumerecord WHERE cost_status=1 group by idx_user_id
) b
on a.date = b.date
group by a.date;

由于第一张表只存了当月数据,所以需要并上另外一张表数据结果去重

新增用户查询结果如下