-- 计算某日用户留存率(次日、3日、7日、30日,...)select*,
concat(round(100*次日留存用户/日活跃用户数,2),'%')'次日留存率',
concat(round(100*三日留存用户/日活跃用户数,2),'%')'三日留存率',
concat(round(100*七日留存用户/日活跃用户数,2),'%')'七日留存率'from(select
a.day,count(distinct a.uid)'日活跃用户数',count(distinct b.uid)'次日留存用户',count(distinct c.uid)'三日留存用户',count(distinct d.uid)'七日留存用户'from tb_user_tj a
leftjoin tb_user_tj b on a.uid = b.uid and b.day= a.day+1leftjoin tb_user_tj c on a.uid = c.uid and c.day= a.day+3leftjoin tb_user_tj d on a.uid = d.uid and d.day= a.day+7groupby a.day) p;
计算某日新增用户留存率(次日、3日、7日、30日、…)
-- 计算某日新增用户留存率(次日、3日、7日、30日、...)select*,
concat(round(100*次日留存用户/日新增用户数,2),'%')'次日留存率',
concat(round(100*三日留存用户/日新增用户数,2),'%')'三日留存率',
concat(round(100*七日留存用户/日新增用户数,2),'%')'七日留存率'from(select
c.day'日期',count(distinct c.uid)'日新增用户数',count(distinct d.uid)'次日留存用户',count(distinct e.uid)'三日留存用户',count(distinct f.uid)'七日留存用户'from(select a.*from tb_user_tj a
leftjoin tb_user_tj b on a.uid = b.uid and b.day< a.daywhere b.dayisnull) c
leftjoin tb_user_tj d on c.uid = d.uid and d.day= c.day+1leftjoin tb_user_tj e on c.uid = e.uid and e.day= c.day+3leftjoin tb_user_tj f on c.uid = f.uid and f.day= c.day+7groupby c.day) p;