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

news/2024/11/14 3:02:00/

在数据分析中,经常会遇到需要分析用户行为连续性的问题,比如查询用户连续三天登录的情况。本文将基于 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 中查询出了用户连续三天登录的所有数据记录,这种方法可以帮助我们更好地分析用户登录行为的连续性。希望这篇文章对你理解和解决类似问题有所帮助。


http://www.ppmy.cn/news/1546354.html

相关文章

中药标签打印软件下载 佳易王中药香料快速划价管理系统操作教程

一、概述 【软件资源在文章最后】 中药标签打印软件下载 中药香料快速划价管理系统操作教程 ‌核心功能‌: ‌快速划价‌:通过复制药方文本,点击划价按钮即可快速计算出总金额,支持多副药方计算。‌账单管理‌:保存账…

MyBatis操作--进阶

博主主页: 码农派大星. 数据结构专栏:Java数据结构 数据库专栏:MySQL数据库 JavaEE专栏:JavaEE 软件测试专栏:软件测试 关注博主带你了解更多知识 1. 动态SQL 动态SQL是Mybatis的强⼤特性之⼀&#xff0c;能够完成不同条件下不同的sql拼接 1.1 <if>标签 比如说注册…

MySQL中distinct与group by之间的性能进行比较

在 MySQL 中&#xff0c;DISTINCT 和 GROUP BY 都是用于去重或汇总数据的常用 SQL 语法。尽管它们在某些情况下能产生相同的结果&#xff0c;但它们的内部工作方式和性能表现可能有所不同。理解这两者的差异&#xff0c;对于选择正确的语法非常重要&#xff0c;尤其是在处理大量…

100种算法【Python版】第58篇——滤波算法之卡尔曼滤波

本文目录 1 算法步骤2 算法示例2.1 示例描述2.2 python代码3 算法应用:二维运动目标跟踪问题滤波算法是用于从信号中提取有用信息、去除噪声或估计系统状态的技术。在时间序列分析、信号处理和控制系统中,滤波算法起着关键作用。 1 算法步骤 卡尔曼滤波(Kalman Filter)的…

开源 PHP 商城项目 CRMEB 二次开发和部署教程

上篇文章给大家介绍了如何使用 Sealos 应用商店一键部署 CRMEB 开源商城系统&#xff0c;那速度真叫一个快啊&#xff0c;比宝塔快多了&#xff01; 但是有些读者还不满足于此&#xff0c;问我能不能边运行边改代码&#xff0c;而且还得用 Cursor 来改代码&#xff0c;改完了之…

基于Opencv的图像处理软件

本文所涉及所有资源均在 传知代码平台 可获取。 目录 一、背景及意义介绍 背景 意义

Linux 系统结构

Linux系统一般有4个主要部分&#xff1a;内核、shell、文件系统和应用程序。内核、shell和文件系统一起形成了基本的操作系统结构&#xff0c;它们使得用户可以运行程序、管理文件并使用系统。 1. linux内核 内核是操作系统的核心&#xff0c;具有很多最基本功能&#xff0c;它…

鸿蒙生态的全景透视

鸿蒙生态的全景透视 在这个智能设备日益普及的时代&#xff0c;你是否也在思考&#xff1a;不同设备之间如何才能实现无缝连接&#xff1f;鸿蒙生态&#xff0c;作为华为推出的全新操作系统&#xff0c;或许会给你答案。它不仅仅是一个操作系统&#xff0c;更是一个充满机遇和…