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