【面试题】Hive 查询：如何查找用户连续三天登录的记录

1. 需求概述

在分析用户行为时，查询用户的连续登录数据是一个常见需求。例如，我们需要找出每个用户连续三天登录的记录。给定一个包含用户登录记录的表，我们需要对这些数据进行处理，提取出用户连续三天登录的日期。

2. 问题说明

假设我们有一个用户登录记录表 user_log，表结构如下：

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

我们的目标是找出每个用户连续三天登录的所有数据记录，期望的输出结果如下：

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
2	2024-05-02
2	2024-05-03
2	2024-05-04

3. 查询思路

为了完成这个任务，我们可以利用 Hive SQL 的窗口函数来处理这个问题。主要的思路是：

1. 窗口函数的使用：通过 LEAD() 函数获取当前登录记录的下一天和下两天的日期。
2. 日期差计算：计算当前日期和下一天、下两天的日期差，判断是否为连续的三天。
3. 筛选符合条件的数据：最终筛选出满足条件（即连续三天登录）的数据记录。

4. 查询实现

下面是具体的 Hive SQL 查询实现：

with t as (select *, lead(dt,1,dt) over(partition by id order by dt) last_day, lead(dt,2,dt) over(partition by id order by dt) last_2_day from user_log
),
t2 as (select *, datediff(last_2_day, dt) date_diff from t
)
select distinct id, d 
from t2 
lateral view explode(map('dt', dt, 'last_day', last_day, 'last_2_day', last_2_day)) tem as s, d 
where date_diff = 2;

5.代码解析

1. 子查询 t：

这个子查询为每个用户的登录记录添加了两列，分别是 last_day 和 last_2_day，它们表示当前记录的下一天和下两天的登录日期。这里使用了窗口函数 LEAD() 来实现。

• LEAD(dt, 1, dt)：这个窗口函数获取当前行的下一天登录日期。如果下一天不存在，则返回当前日期 dt 作为默认值。
• LEAD(dt, 2, dt)：这个窗口函数获取当前行的下两天登录日期。如果下两天不存在，则返回当前日期 dt 作为默认值。
• PARTITION BY id：按 id 列（即用户ID）对数据进行分组。
• ORDER BY dt：按日期排序。

所以，t 子查询的结果将会如下（假设数据表 user_log 的某一部分）：

id	dt	last_day	last_2_day
1	2024-04-25	2024-04-26	2024-04-27
1	2024-04-26	2024-04-27	2024-04-28
1	2024-04-27	2024-04-28	2024-04-30
1	2024-04-28	2024-04-30	2024-05-01
1	2024-04-30	2024-05-01	2024-05-02
1	2024-05-01	2024-05-02	2024-05-04
1	2024-05-02	2024-05-04	2024-05-05

 

2. 子查询 t2：

在 t2 子查询中，我们计算了日期差 date_diff，它表示 last_2_day 和当前登录日期 dt 之间的天数差。使用了 DATEDIFF() 函数来计算两个日期之间的天数差。

• DATEDIFF(last_2_day, dt)：计算 last_2_day 与当前日期 dt 之间的天数差。

date_diff 为 2 的记录说明 dt 与 last_2_day 是连续的三天登录。

3. LATERAL VIEW 和 EXPLODE：

在查询的外层，使用了 LATERAL VIEW 和 EXPLODE 来对数据进行展平操作，并对每个用户的连续三天登录日期进行处理。

• LATERAL VIEW：LATERAL VIEW 用于展开复杂数据类型（如数组或映射）。在这个查询中，LATERAL VIEW 展开了一个映射（map），每个映射包含了 dt、last_day 和 last_2_day 三个字段。
• EXPLODE(map(...))：EXPLODE 会将一个映射中的每个键值对展开为多行。对于每一行数据，都会根据映射的每个键值对创建一行记录。

map('dt', dt, 'last_day', last_day, 'last_2_day', last_2_day) 创建了一个映射（map），映射的键是 'dt'、'last_day' 和 'last_2_day'，值分别是 dt、last_day 和 last_2_day。

这将会生成一个包含每个字段名（dt、last_day、last_2_day）和值的结果行。LATERAL VIEW 使得每一行的键值对都展开为多行数据，因此可以进一步进行查询操作。

4. 查询的最终条件：

最后，通过 where date_diff = 2 筛选出符合条件的记录。这意味着我们只选取那些连续三天登录的记录（日期差为 2），并通过 distinct 去重。

5. 查询结果示例

在执行查询后，我们将得到如下结果：

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
2	2024-05-02
2	2024-05-03
2	2024-05-04

 这个结果显示了每个用户连续三天登录的记录，符合我们预期的输出。