分享一下注册用户留存统计。研究出来一种比较丑的实现方案。分享出来。大家有需要的可以参考一下

select 
date_format(x.day, '%Y-%m-%d') as "日期",
x.regist_num as "新用户数",
round(y.day2*100.0/x.regist_num,1) as "次日留存",
round(y.day3*100.0/x.regist_num,1) as "3日留存",
round(y.day5*100.0/x.regist_num,1) as "5日留存",
round(y.day7*100.0/x.regist_num,1) as "7日留存"
from (
    select date_trunc('day' ,  __time__) as day, count(DISTINCT deviceId) as regist_num from log where eventId='new_loading_1' group by day order by day desc
) x
join ( 
    select a.day as day, 
    count_if(date_diff('day', a.day, b.day)=1) as day2, 
    count_if(date_diff('day', a.day, b.day)=2) as day3,
    count_if(date_diff('day', a.day, b.day)=4) as day5,
    count_if(date_diff('day', a.day, b.day)=6) as day7
    from (
        select deviceId, date_trunc('day' , __time__) as day from log where eventId = 'new_loading_1' group by day,deviceId
    ) a
    join (
        select deviceId, date_trunc('day' , __time__) as day from log where eventId = 'loading_1' group by day,deviceId
    ) b
    on a.deviceId = b.deviceId and 
    (
        date_diff('day', a.day, b.day) = 1 or 
        date_diff('day', a.day, b.day) = 2 or  
        date_diff('day', a.day, b.day) = 4 or
        date_diff('day', a.day, b.day) = 6
    )
    group by day order by day desc
) y
on x.day=y.day