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统计即可

这个方法因为要进行三次查询，所以速度慢