SQL:求n日留存率
假如有一个表,统计了用户id、登录日期date,让你求n日留存率(这里假设求次日和7日)
关键点:想办法搞到一个表,其结构如下图所示:
用户id 第一天记录 第二天记录 第n天记录 (登录为1,未登录为0)
a 1 0 1
b 1 1 1
c 0 1 0
d 1 1 0
方法1
核心代码:
select id,
count(case when date = '.....' then 1 else null end)as cnt_date1,
count(case when date = '.....' then 1 else null end)as cnt_date2,
count(case when date = '.....' then 1 else null end)as cnt_dateN,
from table
where date in (date1,date2,dateN)
group by id
首先按用户id分组,得到每个用户的登录情况(一个用户可能有多次登录记录)
然后用count(case when date = '.....' then 1 else null end)统计第一天、第二天、第7天是否登录
在'.....'处填入对应日期
然后就会得到上面关键点的那张表,后面就很简单
求第n天登录人数就用count(cnt_dateN),求留存率就用count(cnt_dateN)/ count(cnt_date1)
方法2
建立连接
核心代码:
select xxx
from (select distinct id from table where date = '.....')as a
left join (select distinct id from table where date = '.....')as b on a.id = b.id
left join (select distinct id from table where date = '.....')as c on a.id = c.id
这个方法通过三次子查询得到三张表,再用左连接连起来
a.id b.id c.id
a null a
b b b
null c null
d d null
然后相似的使用count统计即可
这个方法因为要进行三次查询,所以速度慢
学习sql,当sql之神