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

我写的长垃圾代码：

select l1.date, 
case when round(l4.cnt_user /  count(l2.user_id),3) then round(l4.cnt_user /  count(l2.user_id),3)
    else round(0.000, 3) end
from login l1
left join(  
    -- 获取到每个用户的首次访问日期  
    select l2.user_id, min(l2.date) as first_date
    from login l2
    group by l2.user_id
)l2 on l1.user_id=l2.user_id  and l1.date=l2.first_date  -- 这个条件设置设置了把不是首次访问的时间置空
left join (
    -- 获取到第一天访问日期后，第二天还访问的人数
    select l3.date, count(t.user_id) as cnt_user, DATE_ADD(l3.date, INTERVAL -1 DAY) as next_day
    from login l3
    inner join (
        -- 获取到每个用户的首次访问日期  
        select user_id, min(date) as first_date
        from login 
        group by user_id
    ) as t on l3.user_id=t.user_id and l3.date=DATE_ADD(t.first_date, INTERVAL 1 DAY)
    group by l3.date
)as l4 on l1.date=l4.next_day
group by l1.date

暂存临时表优化一下：

with user_mindate as(
     -- 获取到每个用户的首次访问日期  
    select user_id, min(date) as first_date
    from login
    group by user_id
)
--  我：费劲千辛万苦的长代码…
select l1.date, 
case when round(l4.cnt_user /  count(l2.user_id),3) then round(l4.cnt_user /  count(l2.user_id),3)
    else round(0.000, 3) end
from login l1
left join user_mindate as l2 on l1.user_id=l2.user_id  
    and l1.date=l2.first_date  -- 这个条件设置设置了把不是首次访问的时间置空
left join (
    -- 获取到第一天访问日期后，第二天还访问的人数
    select l3.date, count(t.user_id) as cnt_user, DATE_ADD(l3.date, INTERVAL -1 DAY) as next_day
    from login l3
    inner join user_mindate as t on l3.user_id=t.user_id and l3.date=DATE_ADD(t.first_date, INTERVAL 1 DAY)
    group by l3.date
)as l4 on l1.date=l4.next_day
group by l1.date

猴哥的思路（牛逼）

SELECT
    a.date,
    ROUND( COUNT( b.user_id ) * 1.0 / COUNT( a.user_id ), 3 ) AS p
FROM
    ( SELECT user_id, MIN( date ) AS date FROM login GROUP BY user_id ) a
    LEFT JOIN login b ON a.user_id = b.user_id
    AND b.date = DATE_ADD(a.date,INTERVAL 1 DAY)
GROUP BY
    a.date UNION
SELECT
    date,
    0.000 AS p
FROM
    login
WHERE
    date NOT IN ( SELECT MIN( date ) FROM login GROUP BY user_id )
ORDER BY
    date;

网上其他人+猴哥，最好的方法!：

--  其他人+猴哥第二个版本，好！
SELECT  d.date, 
round (IFNULL( count(l2.user_id) / count(l1.user_id), 0), 3)
FROM (SELECT date FROM login  group by date ) AS d 
LEFT JOIN (
    SELECT user_id, min(date) as first_day  -- 找出最小日期
    FROM login
    GROUP BY user_id 
) AS l1 ON l1.first_day=d.date 
LEFT JOIN(
    SELECT user_id, date
    FROM login
) as l2 ON l1.user_id=l2.user_id and l2.date=DATE_ADD(l1.first_day, INTERVAL 1 DAY)
GROUP BY d.date