Hive 基于扫码记录查找密接人员

什么是密接人员？

同一时间段出现在同一区域内的人员。

创建数据源

数据来源：数据源

drop table trail;

CREATE TABLE trail(
  uid string,
  area string,
  scan_time timestamp);

INSERT INTO trail VALUES
('13011111111', 'A001', '2022-05-01 09:00:00'),
('13011111111', 'A001', '2022-05-01 10:00:00'),
('13011111111', 'A001', '2022-05-01 11:00:00'),
('13011111111', 'A002', '2022-05-01 11:05:00'),
('13011111111', 'A002', '2022-05-01 13:00:00'),
('13011111111', 'A001', '2022-05-01 13:15:00'),
('13011111111', 'A001', '2022-05-01 14:00:00'),
('13022222222', 'A001', '2022-05-01 10:30:00'),
('13022222222', 'A001', '2022-05-01 12:00:00'),
('13033333333', 'A001', '2022-05-01 11:00:00'),
('13033333333', 'A001', '2022-05-01 12:00:00'),
('13033333333', 'A001', '2022-05-01 13:00:00');

基于扫码记录，计算驻留时间段

即：查找一个用户在某一个区域内的起始时间和结束时间。

如下图所示：

就以 13011111111 用户的视角来看，在 A001 区域中。

该用户一共出现过两次驻留时间段：

• 第一次是 2022-05-01 09:00:00 —— 2022-05-01 11:00:00
• 第二次是 2022-05-01 13:15:00 —— 2022-05-01 14:00:00

下面编写 SQL 来计算驻留时间段：

SELECT 
       uid,
       area,
       date(scan_time) scan_date,
       min(scan_time) start_time,
       max(scan_time) end_time
FROM 
    (SELECT 
         uid,
         area,
         scan_time,
         row_number() over(partition by uid,date(scan_time) ORDER BY  scan_time) 
         - 
         row_number() over(partition by uid,area,date(scan_time) ORDER BY  scan_time) num
    FROM trail)t1
GROUP BY  uid,area,num,date(scan_time);

计算结果：

思路：

这里主要是理解子查询中的这条语句：

row_number() over(partition by uid,date(scan_time) ORDER BY  scan_time)
- 
row_number() over(partition by uid,area,date(scan_time) ORDER BY scan_time)

首先我们通过 uid 和扫码日期进行分组，按照扫码时间排序，通过 row_number 得到该用户某天的全部扫码序号；

然后通过该用户 uid 、area 和扫码日期进行分组，按照扫码时间排序，得到该用户某天某区域的序号。

如下图所示：

我们还是以 13011111111 用户的视角来观察。

num1 是通过 uid 和扫码日期得到的序号；

num2 是通过uid 、area 和扫码日期得到的序号。

我们可以观察到，当 num1 和 num2 处于同一个区间时，两数相减的值是一致的。

这是因为 num1 得到的是该用户某天根据扫码时间排序后的序号，而 num2 在其基础上做了 area 区域划分；如果两数相减的值是一样的，则证明两者属于某天内的同一区间中。

计算密接人员

假设：我们将同一区域内与确诊人员时空接触满 10 分钟的视为密接人员。

我们在 驻留时间段 的基础上进行计算。

WITH tmp as(
SELECT 
       uid,
       area,
       date(scan_time) scan_date,
       min(scan_time) start_time,
       max(scan_time) end_time
FROM 
    (SELECT 
         uid,
         area,
         scan_time,
         row_number() over(partition by uid,date(scan_time) ORDER BY  scan_time) 
         - 
         row_number() over(partition by uid,area,date(scan_time) ORDER BY  scan_time) num
    FROM trail)t1
GROUP BY  uid,area,num,date(scan_time))
SELECT
    *
FROM
    tmp p1
join
    tmp p2
on
    p1.uid <> p2.uid -- 显然，我们不能让自己和自己进行时空交集
    and
    p1.area = p2.area -- 同一区域
    and
    -- 判断是否密接
    p1.start_time + INTERVAL 10 minutes <= p2.end_time 
    -- 确认人员的开始时间 + 10 <= 密接人员的离开时间，否则不满足时空交集要求。
    and
    p2.start_time + INTERVAL 10 minutes <= p1.end_time
    -- 密接人员的开始时间 + 10 <= 确诊人员的离开时间，否则不满足时空交集要求。
where
    -- 假设确诊人员为 13011111111
    p1.uid = '13011111111';

计算结果：

可以看到，有一个密接人员。