【次日留存率】三步骤和两思路对比分析

一、确定总体问题

每个日期新用户的次日留存率 -- 关键问题
结果保留小数点后面3位数(3位之后的四舍五入) --次要问题
并且查询结果按照日期升序排序 -- 次要问题

二、分析关键问题（约束条件）

每个日期的新用户数--分母
每个日期的新用户次日留存--分子
求比率（/）

分析：

{写在前面：面对难题的时候，往往分析到这里就停住了，明明题目都懂，也知道分子和分母是什么，但是就是不知道如何写，这里的障碍恰恰就是我们提高SQL分析能力的机遇了。

下面来一步步分析究竟如何切入的问题

首先，不知道如何写，本质是不知道写在哪里，用什么关键字将条件表达出来。解决了这两个问题就解决了这个问题的核心。

其次，解决上述两种问题，需要在每道难题中抽至少两种方法对比分析，通过分析，你慢慢积累经验，知道制约条件可以写在哪里，对应使用什么关键字，同样表达一个意思但为什么在这里不能使用某个关键字等等。如果不停看别人的参考答案就完事，虽然每次都理解他的思路，那么下次做题还是不会

再次，对于解决上述两种问题的先后顺序，我认为首先解决约束条件写在哪里，因为位置决定了你使用何种关键字的表达，不同位置有不同的表达方式}

1.究竟将约束条件放在哪里？用什么关键字好？下面提供两个角度

【角度① 分析关系】：既然求比率，就要分别求分子分母，他们两属于包含关系，所以可以根据‘漏斗模型’，将约束条件放在from left join中描述（下面的方法一详细讲述）

【角度② 分析求和方法】：计数count（直接法）或赋值后，间接计数sum或count（间接法）。

如果用间接法，那么首先要赋值，而赋值可在比率公式上直接利用case when插入约束条件解决；

如果用直接法，则case when和漏斗模型两种都可用。

2.用什么来求数值？count还是sum？

若用角度①，则要用count而不能用sum来聚合符合条件的数量，因为条件写在from left join中并没有对其赋值
若用角度②，则count和sum都可以，因为用的case when 中有赋值，而且仅仅是计数，所以两者都能用。角度②具体如下：
COUNT（CASE WHEN THEN 1 ELSE NULL END）-- 跳过NULL值
SUM（CASE WHEN THEN 1 ELSE 0或NULL END）-- 跳过NULL值

三、搭框架

如果在上面的第二大点已经分析到位，那么大框架就显而易见了，主要是把次要问题方在合适的地方上（分母可能为零、保留小数点、排序等问题）

根据上面两种分析角度，提供下面两种方法并做异同比较。

【方法一的详细SQL】

select t0.date,
ifnull(round(count(distinct t2.user_id)/(count(t1.user_id)),3),0)
from
(
select date
from login
group by date
) t0
left join
(
select user_id,min(date) as date
from login
group by user_id
)t1
on t0.date=t1.date
left join login as t2
on t1.user_id=t2.user_id and datediff(t2.date,t1.date)=1
group by t0.date

【方法二的详细SQL】

SELECT date,IFNULL(ROUND(SUM(CASE WHEN (user_id,date) IN
(SELECT user_id,DATE_ADD(date,INTERVAL -1 DAY) FROM login)
AND (user_id,date) IN (SELECT user_id,MIN(date) FROM login GROUP BY user_id)
THEN 1 ELSE 0 END)/
SUM(CASE WHEN (user_id,date) IN (SELECT user_id,MIN(date) FROM login GROUP BY user_id)
THEN 1 ELSE 0 END),3),0) AS p
FROM login
GROUP BY date
ORDER BY date;

两种框架的比较

共性：

ifnull（考虑分母为0，即当天没有新用户的情况）--求比率问题必须考虑的特殊情况
round（四舍五入保留三位小数）--数值型格式要求
group by 和order by（按date分组和升序）--结果展示要求

异性：

一、约束条件放置位置不同 --（决定运用何种框架）

【方法一（间接法）】放在from left join中。类比拿来主义，因为已经在from中写好约束条件，所以直接拿from表格中的某些字段用于count的字段
粗框架：

select date,IFNULL(ROUND(count()/count(),3,
from （select 去重的日期表）
    left join on （select 新用户的首次登陆日期表 ）
    left join on （select 新用户的次日登录日期表 ）
group by
ORDER BY

该框架类比漏斗模型，左边最宽，向右逐次缩窄，需满足的条件也越来越严苛

【方法二（直接法）】放在比率（分子分母）的case when中
粗框架：

SELECT date,IFNULL(ROUND(SUM(CASE WHEN ...) /
                        (SUM(CASE WHEN ...),3),0)
FROM login
GROUP BY date
ORDER BY date;

虽然该框架中的分子会重复书写分母的条件，但是框架简洁，可读性较高。（个人比较喜欢）

二、约束条件的写法对比

条件 / 框架

条件写在from left join里

条件写在case when里

每日

新用户数

...left join

(select user_id,min(date) as date

from login

group by user_id

)t1 on t0.date=t1.date

CASE WHEN (user_id,date)

IN (SELECT user_id,MIN(date)

FROM login GROUP BY user_id)

THEN 1 ELSE 0 END

每日

新用户

次日留存数

...left join login as t2

on t1.user_id=t2.user_id

and datediff(t2.date,t1.date)=1

CASE WHEN (user_id,date)

IN(SELECT user_id,DATE_ADD(date,INTERVAL -1 DAY)

FROM login)

AND (user_id,date) IN (SELECT user_id,MIN(date)

FROM login GROUP BY user_id)

THEN 1 ELSE 0 END)

如何写制约条件？

【方法一（间接法）】

①结构：根据条件的范围从大到小left join下去

②制约条件：

对自身字段的约束（分母），如最值，就写在原本字段的位置，如“每日新用户数”转化为“按ID分组后的最小的日期”，即(select user_id,min(date) as date from login group by user_id；

对表的进一步约束（分子），如“每日新用户留存数”，本质上是两个形容词的约束，那么要再进一步left join一个表来约束“留存”，约束条件写在join之后的 on 上，即left join login as t2 on t1.user_id=t2.user_id and datediff(t2.date,t1.date)=1

【方法二（直接法）】

①结构：分子的条件要包括分母的条件，多个条件用AND 联结；

②根据需制约的字段（user_id,date），运用 IN关键字写条件：CASE WHEN (user_id,date) IN (SELECT...) AND (user_id,date) IN (SELECT...)