Hive SQL进阶：掌握间隔连续查询技巧，优化大数据分析

本文链接：https://blog.csdn.net/lzhcoder/article/details/134881171

这篇博客探讨了如何使用Hive SQL解决连续和间隔连续的问题，如计算连续7天登录的用户、连续3天碳排放量大于100的用户，以及用户的最大连续登录天数。通过rank函数、RANGE BETWEEN INTERVAL和LAG函数等技术进行分析，提供了详细的思路和解决方案。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

连续问题

问题一

算过去30天有哪些用户是连续7天登录我们APP的，如何写SQL

思路：用rank算出连续编号，然后用日期减去rank编号差值求解

SELECT DISTINCT user_id
FROM (
    SELECT user_id,
           login_date,
           date_sub(login_date, interval rank() over (partition by user_id order by login_date) day) as grp
    FROM logins
    WHERE login_date > current_date - interval 30 day
) t
GROUP BY user_id, grp
HAVING count(*) >= 7

问题二

如下数据为蚂蚁森林中用户领取的减少碳排放量

id dt lowcarbon
1001 2021-12-12 123
1002 2021-12-12 45
1001 2021-12-13 43
1001 2021-12-13 45
1001 2021-12-13 23
1002 2021-12-14 45
1001 2021-12-14 230
1002 2021-12-15 45
1001 2021-12-15 23
.......

找出连续3天及以上每天减少碳排放量在100以上的用户

思路一、日期减去rank排名再分组

-- 1) 按照用户ID及时间字段分组,计算每个用户单日减少的碳排放量
select  id,
        dt,
        sum(lowcarbon) lowcarbon
from    test1
group by 
        id,dt
having  lowcarbon>100; 记为 t1
得到：
1001	2021-12-12		123
1001	2021-12-13		111
1001	2021-12-14		230
 
-- 2) 按照用户分组,同时按照时间排序,计算每条数据的Rank值
select  id,
        dt,
        lowcarbon,
        rank() over(partition by id order by dt) rk
from    t1; 记为 t2
得到：
1001    2021-12-12      123     1
1001    2021-12-13      111     2
1001    2021-12-14      230     3
 
-- 3) 将每行数据中的日期减去Rank值
select  id,
        dt,
        lowcarbon,
        date_sub(dt,rk) flag
from    t2; 记为 t3
得到：
1001    2021-12-12      123     2021-12-11
1001    2021-12-13      111     2021-12-11
1001    2021-12-14      230     2021-12-11
 
-- 4) 按照用户及Flag分组,求每个组有多少条数据,并找出大于等于3条的数据
select  id,
        flag,
        count(*) ct
from    t3
group