统计连续 5 天登录的用户，login 登录表：
hive> select * from temp;OK2020-09-01a2020-09-01b2020-09-02a2020-09-03a2020-09-03b2020-09-04a2020-09-04b2020-09-05a2020-09-06a2020-09-07b2020-09-08a2020-09-10a2020-09-11a2020-09-11b2020-09-23b2020-09-24b2020-09-26b2020-09-27b2020-09-29b2020-09-30b2020-09-12b2020-09-17bTime taken: 0.085 seconds, Fetched: 22 row(s)hive> select distinct a.userid from (select dt,userid,row_number() over(partition by userid order by dt) as rank from temp) a join (select dt,userid,row_number() over(partition by userid order by dt) as rank from temp) b on a.userid = b.userid and a.rank = b.rank -5 and a.dt = date_add(b.dt,-5);OKaTime taken: 10.525 seconds, Fetched: 1 row(s)
SQLselect distinct a.userid from (select dt,userid,row_number() over(partition by userid order by dt) as rank from temp) a join (select dt,userid,row_number() over(partition by userid order by dt) as rank from temp) b on a.userid = b.userid and a.rank = b.rank -5 and a.dt = date_add(b.dt,-5);