牛客网sql29题

       题目：计算客户在某天刷题后第二天继续来刷题的平均概率，即：客户留存率。

        题解：1.重在如何计算客户留存率。同一设备在一天内多次重复刷题，因此需要对设备用户进行去重。

                     2.可以筛选出某天的device_id,date的有效数据作为表1，运用date_add（date,interval 1 day）函数构造出第二天客户继续刷题的日期，筛选出device_id,次日日期作为表2，把表1当做主表，运用left join 连接表2，用

ON q1.device_id = q2.device_id AND q1.date = DATE_ADD(q2.date, interval 1 day)

进行限制，因为使用的是表1左级联表2，所以表1的所有信息是显示的；而表2中只显示留存的信息，否则为null。

3.

次日留存率=去重的数据表中所有条目数目去重的数据表中符合次日留存的条目数目​

SELECT 
    COUNT(q2.device_id) / COUNT(q1.device_id) AS avg_ret 
FROM 
    (SELECT DISTINCT device_id, DATE FROM question_practice_detail)AS q1
LEFT JOIN
    (SELECT DISTINCT device_id, DATE_ADD(DATE,INTERVAL 1 DAY) d2 FROM question_practice_detail) AS q2
ON q1.device_id = q2.device_id AND q1.date = q2.d2

也可以都挨个筛选出来，提取表1数据，汇总提取表2数据，汇总提取表3数据，然后对表3的数据进行计算

SELECT COUNT(t3.d2)/COUNT(t3.date) avg_cnt FROM
(SELECT DISTINCT t1.device_id,t2.d2,DATE FROM question_practice_detail t1
LEFT JOIN 
(SELECT DISTINCT device_id,DATE_ADD(DATE,INTERVAL 1 DAY) d2 FROM 
`question_practice_detail` ) t2 ON t1.device_id=t2.device_id
AND t1.date=t2.d2 ) t3

datediff函数

思路：平均次日留存率=次日留存用户数/对应的当日用户数
第一，对现有关系数据表的列进行拆解重构，目的是实现当日用户与次日用户的对应匹配，通过对question_practice_detail表利用from ...left outer join ... on ...建立自联结,这里重点指出通过on q1.device_id=q2.device_id and datediff(q2.date,q1.date)=1实现联结匹配，datediff(次日，当日)=1即次日-当日=1（相隔一天）发挥构建桥梁作用；
第二，分母count(distinct q1.device_id,q1.date) 通过DISTINCT 对当日的device_id，date进行双项剔重，利用COUNT()统计用户数；同样的分子count(distinct q2.device_id,q2.date)对次日的device_id，date进行双项剔重。

SELECT count(distinct q2.device_id,q2.date)/count(distinct q1.device_id,q1.date) as avg_ret
FROM question_practice_detail as q1 left outer join question_practice_detail as q2 
on q1.device_id=q2.device_id and datediff(q2.date,q1.date)=1;