MYSQL练习--每个日期新用户的次日留存率

哈哈的二哈

已于 2024-03-15 14:45:30 修改

阅读量518

点赞数 12

文章标签： mysql

于 2024-03-15 14:17:34 首次发布

本文链接：https://blog.csdn.net/qq_42981010/article/details/136738333

版权

本文介绍了如何使用SQL在MySQL中查询每个日期新用户的次日留存率，提供了两种方法，一种是通过窗口函数和分组计算，另一种是利用子查询和除法操作。结果按日期升序排序并保留3位小数。

摘要由CSDN通过智能技术生成

一、题目

查询每个日期新用户的次日留存率，结果保留小数点后面3位数(3位之后的四舍五入)，并且查询结果按照日期升序排序

二、源数据

drop table if exists login;
CREATE TABLE `login` (
`id` int(4) NOT NULL,
`user_id` int(4) NOT NULL,
`client_id` int(4) NOT NULL,
`date` date NOT NULL,
PRIMARY KEY (`id`));

INSERT INTO login VALUES
(1,2,1,'2020-10-12'),
(2,3,2,'2020-10-12'),
(3,1,2,'2020-10-12'),
(4,2,2,'2020-10-13'),
(5,1,2,'2020-10-13'),
(6,3,1,'2020-10-14'),
(7,4,1,'2020-10-14'),
(8,4,1,'2020-10-15');

三、结果展示

四、代码

第一种解法：

select x.date,
       case when p is null then 0.000
       else p end as p
from
(select distinct date from login order by date asc) as x

left join

(select date, round(count(re_user)/count(new_user),3) as p
from
(select c.date, c.user_id as new_user, d.user_id as re_user
from
(select date,user_id from 
(select *,
       dense_rank () over (partition by user_id order by date asc) as date_rank
from login) as a
where date_rank = 1
order by date_rank asc,date asc) as c

left join 

(select date_sub(date,interval 1 day) as day_lead,user_id from login
order by date asc, user_id asc) as d

on c.date = d.day_lead and c.user_id = d.user_id) as z

group by date) as y

on x.date = y.date

第二种解法：

select a.date, round(if(b.dr is null,0,c.lc/b.dr),3) p from
(select distinct date  from login) a 


left join 
(select date,count(user_id) dr from 
(select user_id	,min(date) date from login  group by user_id) a group by date) b
on a.date=b.date


left join 
(select date,count(bd) lc from 
(select a.user_id,a.date  date ,b.date bd from login a left join login b on a.user_id=b.user_id and a.date=DATE_SUB(b.date, INTERVAL 1 DAY) where (a.user_id,a.date) in(select user_id	,min(date) date from login  group by user_id) ) a group by date) c
on c.date =a.date


order by a.date