题解 | #牛客每个人最近的登录日期(三)#

本题比较有意思，方法思路非常多 1、巧秒方法：在group_concat(date)中找用户最找登录日期的下一天，如果能找到，那就标记为1，否则为0

select round(sum(t)/count(*),3) from 
(select user_id
,if(
FIND_IN_SET(date_add(min(date),interval 1 day),group_concat(date)),1,0
) as t from login
group by user_id) a

2、使用左连接方法，where子句中添加条件左表日期+1天=右表日期

SELECT ROUND(count(r.date) / COUNT(*), 3)
FROM (SELECT user_id, MIN(date) AS date FROM login GROUP BY user_id) l
LEFT JOIN login r ON l.user_id = r.user_id 
AND DATE_ADD(l.date, INTERVAL 1 DAY) = r.date
ORDER BY l.user_id, l.date

3、使用where in判断user_id是否在下一天登录

SELECT
ROUND(
COUNT(DISTINCT user_id)/(SELECT COUNT(DISTINCT user_id) FROM login)
,3)
FROM login
WHERE (user_id, date)
IN
(SELECT user_id, DATE_ADD(MIN(date),INTERVAL 1 DAY) FROM login GROUP BY user_id);

4、使用nth_value()和first_value()窗口函数，注意窗口函数外面不能再套用count或sum函数

select round(sum(date)/count(*),3) p from
(select distinct user_id,
case when
(nth_value(date,2) over w - first_value(date) over w)=1 then 1 else 0 end
as date
from login l
window w as(
partition by user_id order by date asc rows between unbounded preceding and unbounded following
))a

5、使用lead()窗口函数，与方法2思路是一致的

select round(
count(distinct case when datediff(ld,date) = 1 then user_id else null end)
/count(distinct user_id)
,3) as p 
from
(SELECT user_id, date
,lead(date,1)over(partition by user_id order by date)ld
from login )a