t

thatdaniel

V1

2022/08/18阅读:10主题:默认主题

MySql 练习- 留存率计算

MySql 练习- 留存率计算

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

数据源:question_practice_detail

id device_id quest_id result date
1 2138 111 wrong 5/3/2021
2 3214 112 wrong 5/9/2021
3 3214 113 wrong 6/15/2021
4 6543 111 right 8/13/2021
5 2315 115 right 8/13/2021
6 2315 116 right 8/14/2021
7 2315 117 wrong 8/15/2021

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

思路分析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

输出结果如下:

device_id date device_id(1) date(1)
2138 5/3/2021 2138 5/3/2021
3214 5/9/2021 3214 5/9/2021
3214 5/9/2021 3214 6/15/2021
3214 5/9/2021 3214 8/15/2021
3214 5/9/2021 3214 8/16/2021
3214 5/9/2021 3214 8/18/2021
3214 6/15/2021 3214 5/9/2021
3214 6/15/2021 3214 6/15/2021
3214 6/15/2021 3214 8/15/2021
3214 6/15/2021 3214 8/16/2021
3214 6/15/2021 3214 8/18/2021
6543 8/13/2021 6543 8/13/2021
2315 8/13/2021 2315 8/13/2021
....

思路分析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

结果如下

device_id date device_id(1) date(1)
2138 5/3/2021 None None
3214 5/9/2021 None None
3214 6/15/2021 None None
6543 8/13/2021 None None
2315 8/13/2021 2315 8/14/2021
2315 8/14/2021 2315 8/15/2021
...

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

结果如下:

device_id date device_id(1) date(1)
2315 8/13/2021 2315 8/14/2021
2315 8/14/2021 2315 8/15/2021
3214 8/15/2021 3214 8/16/2021

思路分析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

分类:

后端

标签:

大数据

作者介绍

t
thatdaniel
V1