这篇博客介绍了如何使用SQL查询找出特定月份（2014年7月）内连续登陆超过7天的用户。通过去重、排序和窗口函数row_number()over()，对用户登陆日志进行分析，最终确定符合条件的用户ID及其连续登陆的起止日期。这种方法对于用户行为分析和数据挖掘具有重要意义。
首先给出一些样例数据：
用户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的写法。
扫一扫
774
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
