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

1. 需求概述

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

2. 问题说明

假设我们有一个用户登录记录表

user_log

，表结构如下：
iddt12024-04-2512024-04-2612024-04-2712024-04-2812024-04-3012024-05-0112024-05-0212024-05-0412024-05-0522024-04-2522024-04-2822024-05-0222024-05-0322024-05-04
我们的目标是找出每个用户连续三天登录的所有数据记录，期望的输出结果如下：
iddt12024-04-2512024-04-2612024-04-2712024-04-2812024-04-3012024-05-0112024-05-0222024-05-0222024-05-0322024-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

的某一部分）：
iddtlast_daylast_2_day12024-04-252024-04-262024-04-2712024-04-262024-04-272024-04-2812024-04-272024-04-282024-04-3012024-04-282024-04-302024-05-0112024-04-302024-05-012024-05-0212024-05-012024-05-022024-05-0412024-05-022024-05-042024-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. 查询结果示例

在执行查询后，我们将得到如下结果：
iddt12024-04-2512024-04-2612024-04-2712024-04-2812024-04-3012024-05-0112024-05-0222024-05-0222024-05-0322024-05-04
这个结果显示了每个用户连续三天登录的记录，符合我们预期的输出。