当前位置：首页 > article >正文

Hive面试题-- hive中查询用户连续三天登录记录的实现与解析

article 2024/11/8 0:00:47

在数据分析中，经常会遇到需要分析用户行为连续性的问题，比如查询用户连续三天登录的情况。本文将基于 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) true3dt
    from 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) true3dt
  from 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) true3dt
  from 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) true3dt
  from 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。