通过datediff()函数和date_add()函数计算【留存率】

第一：
通过datediff(string enddate,string begindate) 【日期比较函数】（返回enddate与begindate之间的时间差的天数）
示例1：查询新登录用户次日成功的留存率，即第1天登陆之后，第2天再次登陆的概率,保存小数点后面3位(3位之后的四舍五入)

select round((count(distinct case when datediff(l2.date,l1.date)=1 then l1.user_id end)/count(distinct l1.user_id)),3) as '次日留存率'
from login as l1 join login as l2
on l1.user_id=l2.user_id;

示例2：通过一条SQL语句进行查询次日留存率、三日留存率和七日留存率

select u1.dayno as '日期' ,
       count(distinct u1.uid) as '活跃用户数',
       count(distinct case when datediff(u2.dayno,u1.dayno)=1 then u1.uid end) as '次日留存用户数',
       count(distinct case when datediff(u2.dayno,u1.dayno)=3 then u1.uid end) as '三日留存用户数',
       count(distinct case when datediff(u2.dayno,u1.dayno)=7 then u1.uid end) as '七日留存用户数',
       concat(convert((count(distinct case when datediff(u2.dayno,u1.dayno)=1 then u1.uid end)/ count(distinct u1.uid) )*100,decimal(10,2)),'%') as '次日留存率',
       concat(format((count(distinct case when datediff(u2.dayno,u1.dayno)=3 then u1.uid end)/ count(distinct u1.uid) )*100,2),'%') as '三日留存率',
       concat(round( (count(distinct case when datediff(u2.dayno,u1.dayno)=7 then u1.uid end)/ count(distinct u1.uid) )*100,2),'%') as '七日留存率'
from  userinfo as u1  left outer join userinfo as u2            -- 自联结表
on u1.uid=u2.uid
where u1.app_name='相机' and (u1.duration>=1 or u1.times>=3) and  u2.app_name='相机'  and (u2.duration>=1 or u2.times>=3)
group by u1.dayno;

第二：
通过date_add(string date,interval int day[month][year]) 【日期增加函数】 （返回date增加day[month][year]后的日期）
示例1：查询新登录用户次日成功的留存率，即第1天登陆之后，第2天再次登陆的概率,保存小数点后面3位(3位之后的四舍五入)

select round(count(distinct case when t2.date=date_add(t1.date,interval 1 day) then t1.user_id end)/count(distinct t1.user_id),3)  as '次日留存率'
from logins t1 join logins t2
on t1.user_id=t2.user_id;