SQL查询最近连续登录的用户

画了下楼主的思路，很厉害

我说下我的思路：
1、首先过滤掉明显不符合要求的group by userid having count(1)<3
2、再筛选出每个用户的logindate>=max(logindate-2) and logindate<=max(logindate)
3、再group by userid having count(distinct logindate)=3 结果即为所求

fly2015 发表于 2018-4-27 17:21
我说下我的思路：
1、首先过滤掉明显不符合要求的group by userid having count(1)=max(logindate-2) and  ...

1、首先过滤掉明显不符合要求的group by userid having count(1)<3
2、再筛选出每个用户的logindate>=max(logindate-2) and logindate<=max(logindate) --这里得在分组的基础上进行判断吧？使用分组函数？还是其他方法？
3、再group by userid having count(distinct logindate)=3 结果即为所求

select user_id, user_name, logon_date, row_number() over(partiton by user_id order by logon_date) as group_seq
from user_logon_history;