问题

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

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

实现方式

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）的格式，并对整个数据进行去重，输出数据如下：

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

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

排序之后的数据如下：

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

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

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

排序之后输出数据如下：

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

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

最后

以上就是欢呼纸飞机为你收集整理的Spark——Spark/Hive SQL计算获取连续登陆N天及以上的用户问题实现方式的全部内容，希望文章能够帮你解决Spark——Spark/Hive SQL计算获取连续登陆N天及以上的用户问题实现方式所遇到的程序开发问题。

如果觉得靠谱客网站的内容还不错，欢迎将靠谱客网站推荐给程序员好友。