查询各个用户最长的连续登陆天数？

使用row_number函数

select uid,loadtime,row_number() over(partition by uid order by uid,loadtime) as a1 from tmp_data_12;

Select UID,No,count(*) as ct From (Select UID,LoadTime,(Day(LoadTime)-ROW_NUMBER() OVER (Partition By UID Order By UID,LoadTime)) as No From Tmp_Data_12) a1 Group By UID,No;

再子查询对uid进行分组，选出最大值

select uid,max(cnt) from (Select UID,Grp_No,count(*) as cnt From (Select UID,LoadTime,(Day(LoadTime)-ROW_NUMBER() OVER (Partition By UID Order By UID,LoadTime)) as Grp_No From Tmp_Data_12) a Group By UID,Grp_No)a group by uid