一道适合练手的HiveSql

题目

问题： 现有用户登录记录表，请查询出用户连续三天登录的所有数据记录

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';


insert into user_log values(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');
select * from user_log;

我的答案

with t1 as (
    select id,to_date(dt)now_dt,lead(to_date(dt),2) over(partition by id order by to_date(dt)) 2day_after from user_log
), t2 as (
    select id,now_dt,2day_after,datediff(now_dt,2day_after) diff from t1
),t3 as (
    select `map`(id,`array`(now_dt,date_add(now_dt,1),date_add(now_dt,2) )) dt_map from t2 where diff=-2 )
select t4.id,t5.fn_dt from t3 lateral view explode(dt_map) t4 as id,rs_dt lateral view explode(rs_dt) t5 as fn_dt;

标准答案

--第一步：求解每行日期后面第三行的日期 lead()和 真正第三天的日期
select*,
      lead(dt,2) over(partition by id order by dt) later3dt,
      date_add(dt,2) true3dt
    from user_log;
--第二步：判断是否连续登录三天
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;
--第三步：筛选出连续登录三天的每个起始日期
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;
-- 第四步：表合并求最终结果（和一个三行的表进行合并）（笛卡尔积）
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;