在数据分析中，经常会遇到需要分析用户行为连续性的问题，比如查询用户连续三天登录的情况。本文将基于 Hive 来解决这个问题，并详细解释每一步的代码。
一、问题背景与数据准备
我们有一个用户登录记录表，包含两个字段：id（用户标识）和 dt（登录日期）。数据示例如下：
1 2024-04-25
1 2024-04-26
1 2024-04-27
1 2024-04-28
1 2024-04-30
1 2024-05-01
1 2024-05-02
1 2024-05-04
1 2024-05-05
2 2024-04-25
2 2024-04-28
2 2024-05-02
2 2024-05-03
2 2024-05-04
首先，我们需要创建表并加载数据：
--建表
create table user_log(id int,dt string
)
row format delimited
fields terminated by '\t';
load data local inpath '/home/hivedata/lianxu.txt' into table user_log;这里创建了一个名为 user_log 的表，定义了 id 为整型，dt 为字符串类型（假设日期以字符串形式存储）。通过指定行格式和字段分隔符，并使用 load data 语句将本地路径下的数据文件加载到表中。
二、求解每行日期后面第三行的日期和真正第三天的日期
--第一步：求解每行日期后面第三行的日期 lead()和 真正第三天的日期
select*,lead(dt,2) over(partition by id order by dt) later3dt,date_add(dt,2) true3dtfrom user_log;
lead(dt,2) over(partition by id order by dt)：lead函数用于获取当前行之后第n行（这里n = 2）的值。通过partition by id按照用户id进行分区，在每个分区内按照登录日期dt排序。这样对于每个用户的登录记录，会得到当前登录日期之后第 2 个登录日期的值，将其命名为later3dt。date_add(dt,2)：使用date_add函数计算当前登录日期加上 2 天的日期，即真正的第三天日期，将其命名为true3dt。这一步的结果是在原表数据基础上增加了两列，方便后续判断是否连续登录三天。
三、判断是否连续登录三天
--第二步：判断是否连续登录三天
with t as (select*,lead(dt,2) over(partition by id order by dt) later3dt,date_add(dt,2) true3dtfrom user_log
) select *,if(later3dt==true3dt,1,0) num from t;这里使用了一个公共表达式（CTE）
t，它复用了上一步的查询结果。然后通过if函数判断later3dt（通过lead函数得到的第三天日期）和true3dt（通过date_add计算得到的第三天日期）是否相等。如果相等，表示这是连续登录三天中的第一天，标记为1，否则标记为0，新增的列名为num。
四、筛选出连续登录三天的每个起始日期
--第三步：筛选出连续登录三天的每个起始日期
with t as (select*,lead(dt,2) over(partition by id order by dt) later3dt,date_add(dt,2) true3dtfrom user_log
),t1 as (select *,if(later3dt==true3dt,1,0) num from t
)select * from t1 where num=1;这里再次使用 CTE，
t1是在上一步的基础上得到的结果。通过where子句筛选出num = 1的记录，即连续登录三天的起始日期记录。
五、表合并求最终结果（和一个三行的表进行合并）（笛卡尔积）
-- 第四步：表合并求最终结果（和一个三行的表进行合并）（笛卡尔积）
with t as (select*,lead(dt,2) over(partition by id order by dt) later3dt,date_add(dt,2) true3dtfrom user_log
),t1 as (select *,if(later3dt==true3dt,1,0) num from t
),t2 as (select * from t1 where num=1
) select id,dt,list,date_add(dt,d.list) dt2 from t2,(select explode(array(0,1,2)) list) d;
- 首先，通过前面的步骤得到了
t2，它是连续登录三天的起始日期记录。- 然后，通过
(select explode(array(0,1,2)) list)创建了一个临时数据集，它包含值为 0、1、2 的一列list。- 最后，通过笛卡尔积将
t2和这个临时数据集合并。对于每个起始日期记录，通过date_add(dt,d.list)计算出连续三天的日期，其中d.list分别为 0、1、2，从而得到用户连续三天登录的所有日期记录，最终结果包含用户id、起始登录日期dt、临时数据集中的值list（这里用于计算连续日期）以及计算后的连续登录日期dt2。
通过以上步骤，我们成功地在 Hive 中查询出了用户连续三天登录的所有数据记录，这种方法可以帮助我们更好地分析用户登录行为的连续性。希望这篇文章对你理解和解决类似问题有所帮助。