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