SELECT e.date,ROUND(
CASE
WHEN f.rate IS NULL THEN 0
ELSE f.rate
END
,3) AS p
FROM (
SELECT DISTINCT(date)
FROM login
) AS e LEFT JOIN(
/2.按照日期分组统计各日期的新用户留存率/
SELECT d.date,SUM(d.flag)1.0/ COUNT() AS rate
FROM (
/1.选择每个日期的新用户，并新增flag列即如果该新用户出现在次日则flag置为1，否则0/
SELECT a.user_id,a.date,
CASE
WHEN a.user_id IN (
SELECT c.user_id
FROM login AS c
WHERE DATE(a.date,'+1 day')=c.date
) THEN 1
ELSE 0
END
AS flag
FROM login AS a
WHERE a.user_id NOT IN(
SELECT b.user_id
FROM login AS b
WHERE b.date<a.date
)
) AS d
GROUP BY d.date
) AS f
ON e.date=f.date
ORDER BY e.date