MySql 练习- 留存率计算

MySql 练习- 留存率计算

最近开始每天抽空刷几道MySQL的题，还是要多实战练练，不然容易忘。今天分享一道MySQL里面的留存率计算

数据源：question_practice_detail

题目：现在运营想要查看用户在某天刷题后第二天还会再来刷题的平均概率。请你取出相应数据。

思路分析1：

-- 先将device_id去重，取出date字段，并自关联

select distinct device_id,date from question_practice_detail;

-- 自关联
select * from
(select distinct device_id,date from question_practice_detail) t1
left join 
(select distinct device_id,date from question_practice_detail) t2
on t1.device_id=t2.device_id

输出结果如下：

思路分析2：结合题目只要次日留存率，其实就是date(1)要比date往后延1天；

select * from
(select distinct device_id,date from question_practice_detail) t1
left join 
(select distinct device_id,date from question_practice_detail) t2
on t1.device_id=t2.device_id and datediff(t2.date,t1.date)=1
-- 或者是t2.date=date_add(t1.date, interval 1 day)

此处简单讲一下MySQL中left join on 后的 and 条件与 where 中条件的区别：

• left join的on里加的and，左表的数据一直会在，右表里，不符合条件的部分会直接为null。

• left join的on里加的where，是对两表结合后的结果做筛选；

  and 示例

select * from
(select distinct device_id,date from question_practice_detail) t1
left join 
(select distinct device_id,date from question_practice_detail) t2
on t1.device_id=t2.device_id 
and datediff(t2.date,t1.date)=1

结果如下

where 示例

select * from
(select distinct device_id,date from question_practice_detail) t1
left join 
(select distinct device_id,date from question_practice_detail) t2
on t1.device_id=t2.device_id 
where datediff(t2.date,t1.date)=1

结果如下：

思路分析3：计算留存率，其实就是count(device_id(1))/count(device_id)

select count(t2.device_id)/count(t1.device_id) from
(select distinct device_id,date from question_practice_detail) t1
left join 
(select distinct device_id,date from question_practice_detail) t2
on t1.device_id=t2.device_id 
and datediff(t2.date,t1.date)=1

结果：0.3000