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

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;

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

新增用户查询结果如下

验证结果的正确性

前两条数据代表同一个用户不同日期登录,第一次登录2023-03-06,第二次登录2023-04-14

所以该用户新增的日期为2023-03-06.