Spark——Spark/Hive SQL计算获取连续登陆N天及以上的用户

问题

如何通过SQL获取每个月连续登陆超过5天的用户？

例如，某个用户3月的登录数据如下（一个用户在一天之内可能会登录多次）：

user_id	login_time
0001	2021-03-01 09:20:36
0001	2021-03-01 13:45:36
0001	2021-03-02 10:20:36
0001	2021-03-03 07:20:36
0001	2021-03-09 09:20:36
0001	2021-03-10 09:20:36
0001	2021-03-11 09:20:36
0001	2021-03-11 20:20:36
0001	2021-03-12 09:20:36
0001	2021-03-13 09:20:36
0001	2021-03-14 09:20:36
0001	…

实现方式

1. 实现SQL

先上实现SQL语句：

select
    distinct month, user_id 
from (
    select
        user_id,
        date_format(login_time, "yyyy-MM") month,
        row_number over(partition by user_id, new_login_time order by new_login_time asc) as rn_1
    from (
        select
            user_id,
            login_time,
            date_sub(login_time, rn) new_login_time
        from (
            select
                user_id,
                login_time,
                row_number over(partition by user_id order by login_time asc) as rn
            from (
                select 
                    distinct user_id, 
                    date_format(login_time, "yyyy-MM-dd") login_time 
                from t
            ) t1
        ) t2
    ) t3
) t4
where rn_1 >= 5

2. 实现步骤

1. 由于一天之内同一个用户会登录多次，我们将login_time格式化成时分秒（yyyy-MM-dd）的格式，并对整个数据进行去重，输出数据如下：

user_id	login_time
0001	2021-03-01
0001	2021-03-02
0001	2021-03-03
0001	2021-03-09
0001	2021-03-10
0001	2021-03-11
0001	2021-03-12
0001	2021-03-13
0001	2021-03-14
0001	…

2. 使用窗口函数对步骤1输出的数据进行组内排序，SQL如下：

//rn为行号标记
row_number over(partition by user_id order by login_time asc) as rn

排序之后的数据如下：

user_id	login_time	rn
0001	2021-03-01	1
0001	2021-03-02	2
0001	2021-03-03	3
0001	2021-03-09	4
0001	2021-03-10	5
0001	2021-03-11	6
0001	2021-03-12	7
0001	2021-03-13	8
0001	2021-03-14	9
0001	…	…

3. 我们将login_time列减去rn列，输出以下数据：

user_id	login_time	new_login_time
0001	2021-03-01	2021-02-28
0001	2021-03-02	2021-02-28
0001	2021-03-03	2021-02-28
0001	2021-03-09	2021-03-05
0001	2021-03-10	2021-03-05
0001	2021-03-11	2021-03-05
0001	2021-03-12	2021-03-05
0001	2021-03-13	2021-03-05
0001	2021-03-14	2021-03-05
0001	…	…

4. 对步骤3输出的数据进行组内排序，SQL如下：

//rn_1为行号标记
row_number over(partition by user_id, new_login_time order by new_login_time asc) as rn_1

排序之后输出数据如下：

user_id	login_time	new_login_time	rn_1
0001	2021-03-01	2021-02-28	1
0001	2021-03-02	2021-02-28	2
0001	2021-03-03	2021-02-28	3
0001	2021-03-09	2021-03-05	1
0001	2021-03-10	2021-03-05	2
0001	2021-03-11	2021-03-05	3
0001	2021-03-12	2021-03-05	4
0001	2021-03-13	2021-03-05	5
0001	2021-03-14	2021-03-05	6
0001	…	…	…

这一步其实也可以通过group by分组求和，取大于等于5的记录。

5. 过滤出那些rn_1大于等于5的记录，并对user_id进行去重，就能得出每月连续登陆大于等于5天的用户了。