建表sql语句：
drop table if exists liucunlv;create table liucunlv as select * from ( select 'a' as user_name, '-03-01'as logindateunion all select 'a' as user_name, '-03-02'as logindateunion all select 'a' as user_name, '-03-03'as logindateunion all select 'a' as user_name, '-03-05'as logindateunion all select 'b' as user_name, '-03-02'as logindateunion all select 'b' as user_name, '-03-03'as logindateunion all select 'c' as user_name, '-03-02'as logindateunion all select 'c' as user_name, '-03-04'as logindateunion all select 'c' as user_name, '-03-05'as logindateunion all select 'c' as user_name, '-03-06'as logindateunion all select 'c' as user_name, '-03-07'as logindateunion all select 'd' as user_name, '-03-07'as logindate) t
SELECT * FROM liucunlv
一：现在要求出哪些用户才是次日留存的用户
SELECT user_name,MIN(logindate) AS newdate FROM liucunlv GROUP BY user_name
如果logindate和newdate相差一天，那么就让if_ciriliucun=1否则if_ciriliucun=0
SELECT t1.user_name,CASE WHEN DATEDIFF(logindate,newdate)=1 THEN 1 ELSE 0 END AS if_ciriliucunFROM (SELECT user_name,MIN(logindate) AS newdate FROM liucunlv GROUP BY user_name)t1JOIN liucunlv t2 ON t1.user_name=t2.user_name
之后再做一个groupby user_name,就能选出是否是次日留存的用户了
SELECT t3.user_name,MAX(if_ciriliucun) FROM(SELECT t1.user_name,CASE WHEN DATEDIFF(logindate,newdate)=1 THEN 1 ELSE 0 END AS if_ciriliucunFROM (SELECT user_name,MIN(logindate) AS newdate FROM liucunlv GROUP BY user_name)t1JOIN liucunlv t2 ON t1.user_name=t2.user_name)t3 GROUP BY t3.user_name
可以看到，a和b是次日留存的用户
二：求每日的次日留存率
留存率解释：
在互联网行业中，用户在某段时间内开始使用应用，经过一段时间后，仍然继续使用该应用的用户，被认作是留存用户。这部分用户占当时新增用户的比例即是留存率，会按照每隔1单位时间（例日、周、月）来进行统计。
SELECT t1.logindate AS 日期,COUNT(DISTINCT CASE WHEN DATEDIFF(t3.logindate,newdate) = 1 THEN t3.user_name ELSE NULL END) AS 次日留存用户数,COUNT(DISTINCT t2.user_name) AS 新增用户数,CONCAT(round(COUNT(DISTINCT CASE WHEN DATEDIFF(t3.logindate,newdate) = 1 THEN t3.user_name ELSE NULL END)*100/COUNT(DISTINCT t2.user_name),2),'%') AS 次日留存率FROM(SELECT DISTINCT logindateFROM liucunlv) t1LEFT JOIN(SELECT user_name,MIN(logindate) newdateFROM liucunlvGROUP BY user_name) t2 ON t2.newdate = t1.logindateLEFT JOIN liucunlv t3 ON t2.user_name = t3.user_nameGROUP BY t1.logindate
求每日的次日留存率的整体代码如果读起来有困难，可以尝试接着往下读：
将求每日的次日留存率的整体代码拆解如下：：
t1:
SELECT DISTINCT logindateFROM liucunlv
t2:
SELECT user_name,MIN(logindate) newdateFROM liucunlvGROUP BY user_name
t3:
将t1,t2,t3合起来得到一个表a
SELECT *FROM(SELECT DISTINCT logindateFROM liucunlv) t1LEFT JOIN(SELECT user_name,MIN(logindate) newdateFROM liucunlvGROUP BY user_name) t2 ON t2.newdate = t1.logindateLEFT JOIN liucunlv t3 ON t2.user_name = t3.user_name
再将表a group by t1.logindate (表a的第一列)，做一些聚合运算，便得到
SELECT t1.logindate AS 日期,COUNT(DISTINCT CASE WHEN DATEDIFF(t3.logindate,newdate) = 1 THEN t3.user_name ELSE NULL END) AS 次日留存用户数,COUNT(DISTINCT t2.user_name) AS 新增用户数,CONCAT(round(COUNT(DISTINCT CASE WHEN DATEDIFF(t3.logindate,newdate) = 1 THEN t3.user_name ELSE NULL END)*100/COUNT(DISTINCT t2.user_name),2),'%') AS 次日留存率FROM(SELECT DISTINCT logindateFROM liucunlv) t1LEFT JOIN(SELECT user_name,MIN(logindate) newdateFROM liucunlvGROUP BY user_name) t2 ON t2.newdate = t1.logindateLEFT JOIN liucunlv t3 ON t2.user_name = t3.user_nameGROUP BY t1.logindate
得到我们想要的次日留存率：
引用：
/weixin_38617657/article/details/116697972
