我写的长垃圾代码:
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