SQL

执行顺序：from-on-join-where-group by-with-having-select-distinct-order by-limit
一、用SQL计算留存率
#用户行为信息表 userinfo
1.计算次日留存用户数
create table c as(select a.用户id,a.登陆时间 as a_t,b.登陆时间 as b_t
from 用户行为信息表 as a
left join 用户行为信息表 as b
on a.用户id = b.用户id
where a.应用名称= '相机';)

select *,timestampdiff(day,a_t,b_t)
as day_interval
from c;

select a_t,count(distinct 用户id) as 活跃用户数,
count(distinct case when 时间间隔=1 then 用户id else null end) as 次日留存数,
count(distinct case when 时间间隔=1 then 用户id else null end)/ count(distinct 用户id) as 次日留存率,
count(distinct case when 时间间隔=3 then 用户id else null end) as 三日留存数,
count(distinct case when 时间间隔=3 then 用户id else null end)/ count(distinct 用户id) as 三日留存率,
count(distinct case when 时间间隔=7 then 用户id else null end) as 七日留存数,
count(distinct case when 时间间隔=7 then 用户id else null end)/ count(distinct 用户id) as 七日留存率
from
(select *,timestampdiff(day,a_t,b_t) as 时间间隔
from
(select a.用户id,a.登陆时间 as a_t,b.登陆时间 as b_t
from 用户行为信息表 as a
left join 用户行为信息表 as b
on a.用户id = b.用户id
where a.应用名称= '相机') as c
) as d
group by a_t;