问题: 现有用户登录记录表,请查询出用户连续三天登录的所有数据记录
id dt1 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
期望结果:
先创建表并向hive中插入数据:
create table user_log(id int,dt string
)
row format delimited
fields terminated by '\t';
load data local inpath '/home/hivedata/1107.txt' into table user_log;
--第一步 找出当前日期的后面的第三个日期(函数lead())和真正的三天后日期 (函数date_add())
lead():是一种窗口函数,用于在基于特定排序规则的结果集中,返回当前行之后的某一行(由用户指定偏移量)的指定列的值
date_add():核心功能是对日期进行加法运算。它接受一个日期值和一个时间间隔作为输入,然后返回在该日期基础上增加指定时间间隔后的日期
select *,lead(dt,2) over(partition by id order by dt) late3,date_add(dt,2) true3 from user_log;
查询结果:
--第二步 比较这两个日期是否相等 ,判断当前日期是否是连续三天的
with t as (select *,lead(dt,2) over(partition by id order by dt) late3,date_add(dt,2) true3 from user_log
)
select *,if(late3 == true3,1,0) from t;
查询结果:
--第三步 筛选出符合条件(连续登陆三天)的起始日期
with t as (select *,lead(dt,2) over(partition by id order by dt) late3,date_add(dt,2) true3 from user_log
),t1 as (select *,if(late3 == true3,1,0) num from t
)
select * from t1 where num=1
;
查询结果:
--第四步 将查询出来的表与另一个一行有三条数据的表进行join
with t as (select *,lead(dt,2) over(partition by id order by dt) late3,date_add(dt,2) true3 from user_log
),t1 as (select *,if(late3 == true3,1,0) num from t
),t2 as (select * from t1 where num=1
),t3 as (select * from t2,(select explode(`array`(0,1,2)) add_day) a
)
select id,date_add(dt,add_day) `date` from t3 part
;
两表合并后的查询结果:
最终查询结果: