SQL实现连续登录天数和最大登录天数

SQL实现连续登录天数和最大登录天数

题目：求出用户连续登录天数
问题延伸：连续登录3天的用户，用户最大登录天数等

sheet1表中给定字段用户id，登录时间date，如下表：

1、清洗数据

由于是对连续登录天数的计算，这里精确到了时分秒，而表中可能出现，用户多次连续登录，同一天多次登录等情况，因此进行数据清洗。
这里只需要用date进行清洗就可以了。

SELECT id,date(date) time from sheet1 GROUP BY id ,time

得到：

2、 对用户的登录天数进行排序

这里需要用到窗口函数row_number() over(partition by 字段1 order by 字段2)

SELECT id,time,row_number() over(partition by id order by time) num 
from
(SELECT id,date(date) time from sheet1 GROUP BY id ,time) a

得到：

3、用登录时间减去排序号（以序号为天数）

因为如果用户是连续登录，比如1月2、3、4号都进行了登录，排序就是1、2、3，1月2号减对应序号1就得到1月1号，1月3号减对应序号2也得到1月1号，所以如果用户连续登录，那登录日期减去序号作为的天数，得到的日期是一样的。对减过序号后一样的日期进行计数，这个数就是连续登录的天数。 这里需要用到date_sub(time，interval number day)

SELECT *,DATE_SUB(time,INTERVAL num day) newtime
from
(SELECT id,time,row_number() over(partition by id order by time) num 
from
(SELECT id,date(date) time from sheet1 GROUP BY id ,time) a) a

得到：

4、对用户登录的天数进行计数

用户可能在不同时间段都进行过登录，所以连续登录天数可能发生过几次，以下就得到了用户连续登录的天数

SELECT id,newtime,count(newtime) rk
from
(SELECT *,DATE_SUB(time,INTERVAL num day) newtime
from
(SELECT id,time,row_number() over(partition by id order by time) num 
from
(SELECT id,date(date) time from sheet1 GROUP BY id ,time) a) a) a 
GROUP BY id,newtime

得到：

延伸问题1：连续登录三天

对上面的代码，继续嵌套

SELECT DISTINCT id
from
(SELECT id,newtime,count(newtime) rk
from
(SELECT *,DATE_SUB(time,INTERVAL num day) newtime
from
(SELECT id,time,row_number() over(partition by id order by time) num 
from
(SELECT id,date(date) time from sheet1 GROUP BY id ,time) a) a) a 
GROUP BY id,newtime) a
where rk=3

得到：

或者直接用having进行筛选，但是如果这个用户在很多段时间都连续登录3天，还需要再嵌套去重一下。

SELECT id,newtime,count(newtime) rk
from
(SELECT *,DATE_SUB(time,INTERVAL num day) newtime
from
(SELECT id,time,row_number() over(partition by id order by time) num 
from
(SELECT id,date(date) time from sheet1 GROUP BY id ,time) a) a) a 
GROUP BY id,newtime
having rk=3

得到：

延伸问题2：最大登录天数

SELECT DISTINCT id,max(rk)
from
(SELECT id,newtime,count(newtime) rk
from
(SELECT *,DATE_SUB(time,INTERVAL num day) newtime
from
(SELECT id,time,row_number() over(partition by id order by time) num 
from
(SELECT id,date(date) time from sheet1 GROUP BY id ,time) a) a) a 
GROUP BY id,newtime) a
GROUP BY id

得到：