hive sql 实战案例-查找密接

问题描述

有如下用户访问表，每条记录是用户访问某地方的开始时间和结束时间，假设100是个小洋人，请找出与用户100同时访问某地的用户。

分析问题

101在小洋人来之前就已经走了，属于非密接；
102晚走了一会会，属于密接；
103经历了全过程，属于密接；
104虽然只来了一会会，属于密接；
105早来了一会会，赶上了末班车，属于密接；
106 来时小洋人已经走了，属于非密接。

准备数据

-- 删除表
drop table if exists visit;
-- 建表
CREATE TABLE visit (
 user_id bigint,
 area string,
 start_time string,
 end_time string
);

-- 插入数据
INSERT overwrite table visit  
VALUES
 (100, 'A','2023-01-01 09:00:00','2023-01-01 10:00:00'),
 (101, 'A','2023-01-01 07:00:00','2023-01-01 08:00:00'),
 (102, 'A','2023-01-01 08:00:00','2023-01-01 09:30:00'),
 (103, 'A','2023-01-01 08:00:00','2023-01-01 17:00:00'),
 (104, 'A','2023-01-01 09:30:00','2023-01-01 09:40:00'),
 (105, 'A','2023-01-01 09:50:00','2023-01-01 11:00:00'),
 (106, 'A','2023-01-01 11:00:00','2023-01-01 12:00:00');  
-- 查看数据
select * from visit

方案1

select t2.*
from (
  -- 筛选小洋人
	select *
  from visit
  where user_id=100
  
) t1
join (
  -- 其他用户
	select *
  from visit
  where user_id !=100
)t2 on t1.area=t2.area
where t1.start_time <=t2.end_time -- 我还没走你就来了
and  t1.end_time >= t2.start_time -- 我来了你还没走
order by t2.user_id

方案2：排除法

select t2.*
from (
 -- 筛选小洋人
   select *
 from visit
 where user_id=100
 
) t1
join (
 -- 其他用户
   select *
 from visit
 where user_id !=100
)t2 on t1.area=t2.area
where 
not (
   t2.end_time <t1.start_time -- 我走了他还没来
 or
 t2.start_time >t1.end_time -- 我来时他早走了
)
order by t2.user_id