oracle 通过分析函数实现求连续天数登陆的用户

首先给出一些样例数据：

用户ID	登陆时间（年）	登陆时间（月）	登陆时间（日）
1	2014	7	1
1	2014	7	2
1	2014	7	3
1	2014	7	3
1	2014	7	4
1	2014	7	5
1	2014	7	5
1	2014	7	7
1	2014	7	8
1	2014	7	9
1	2014	7	10
1	2014	7	11
1	2014	7	12
1	2014	7	13
1	2014	7	14
1	2014	7	14
1	2014	7	14
1	2014	7	15
1	2014	7	19
1	2014	7	21
1	2014	7	22
1	2014	7	23
1	2014	7	29
1	2014	7	30
1	2014	7	31

这是用户ID：1的7月份的登陆日志情况，简单说明一下，3号有两条重复的数据，表明3号用户登陆了2次，14号用户登陆了3次，即一天用户可能登陆N次，产生重复的数据。所以必须先去重。

具体算法说明：

1 首先去重得到数据集 T

2 在去重的数据集上通过分析函数over按user_id对日期login_day进行组内排序获得序号row_number()，并用日期减去当前组内排序号得到一个差值rn

3 按user_id和差值rn进行GROUP BY，取COUNT>=7的记录去重得最终结果

具体SQL如下：

select distinct user_id,min(login_day),max(login_day)

  from (select t.*,
               
               trunc(t.login_day - row_number() over(partition by t.user_id order by t.login_day)) rn
        
          from (select distinct * from login_history) t where t.login_year=2014 and t.login_month=7)

 group by user_id, rn having count(1)>=7

这里统计2014年7月份连续登陆>=7天的用户ID,还有连续登陆的起止日期。

同样在hive中好像也有row_number() over分析函数，后面再研究一下hive的写法。