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 的窗口函数来处理这个问题。主要的思路是：
- 窗口函数的使用：通过
LEAD()函数获取当前登录记录的下一天和下两天的日期。 - 日期差计算：计算当前日期和下一天、下两天的日期差，判断是否为连续的三天。
- 筛选符合条件的数据：最终筛选出满足条件（即连续三天登录）的数据记录。
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 |
这个结果显示了每个用户连续三天登录的记录，符合我们预期的输出。