【SQL】用户连续登录天数、窗口函数

select user,count(1) cn
from(select a.*,date_sub(date,rn) flag
from (select user,date,row_number() over(partition by user order by date) rn
from test) a ) a 
group by user,flag

结果：

讨论：

窗口函数排序

select user,date,row_number() over(partition by user order by date) rn
from test

打标签flag：日期减去rn

select a.*,date_sub(date,rn) flag
from (select user,date,row_number() over(partition by user order by date) rn
from test) a 

分组计数，取出结果。

取出连续登录大于3次的用户：

select user
from(select a.*,date_sub(date,rn) flag
from (select user,date,row_number() over(partition by user order by date) rn
from tmp_service_products_fg.test) a ) a 
group by user,flag
having count(1) > '3'