MySQL 高级SQL进阶真题讲解(四)

一、题目描述

二、数据构建

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 a.date,
 case when sum(case when b.user_id is null then 0 else 1 end) =0 then 0
 else   ifnull(round(sum(case when c.user_id is null then 0 else 1 end)/sum(case when b.user_id is null then 0 else 1 end),3),0) 
 end as p
 from  login a
left join (select min(date) as date,user_id from  login 
group by user_id) b on a.user_id = b.user_id and a.date = b.date
left join  login c on b.user_id = c.user_id and c.date = adddate(b.date,INTERVAL 1 DAY)
group by a.date;

四、答案讲解

(select min(date) as date,user_id from  login 
group by user_id

求出每个用户的第一次登陆日期

left login c on b.user_id = c.user_id and c.date = adddate(b.date,INTERVAL 1 DAY)

用第一次登陆日期的user_id 自关联一次 ，关联条件为 b.user_id = c.user_id 且 第二天有登陆。

然后再统计第一次登陆人数，与第二天登陆人数就可以了。