Join with overlapping times using sql?

Question

我有两个表，TABLE1:

job     job_type  job_id    start_time      end_time
hello   EXPRESS   125445801 6/9/22 10:59    6/9/22 11:59
hello   EXPRESS   125449030 6/9/22 11:19    6/9/22 11:38
hello   EXPRESS   125444620 6/9/22 10:59    6/9/22 11:11

和TABLE2:

job     time_sample
hello   6/9/22 10:59
hello   6/9/22 11:13
hello   6/9/22 11:21
hello   6/9/22 11:49

问题在第一张桌子上。由于开始和结束时间重叠，我需要能够区分是否有重叠。如果有重叠，那么我需要将类别标识为"多重"。如果没有重叠，那么我只将类别标记为。job_type。当连接到第二个表时，第二个表需要填充job_type，这样，如果TABLE2中的time_sample只落在TABLE1中只有一条记录的开始和结束时间之间，它将显示一个job_type。如果time_sample在该作业的多个开始/结束时间内，则应该填充MULTIPLE。我很难理解如何能够执行这种半聚合/连接逻辑来创建以下内容:

job     time_sample     job_type
hello   6/9/22 10:59    MULTIPLE
hello   6/9/22 11:13    EXPRESS
hello   6/9/22 11:21    MULTIPLE
hello   6/9/22 11:49    EXPRESS

连接应该在'job'上，并且在表2。time_sample在表1之间。start_time和TABLE1.end_time

Answer 1

可以在表之间执行一个简单的连接。对于作业类型，检查是否有多个匹配项:

select 
t2.job, 
t2.time_sample, 
if (count(*)>1, 'MULTIPLE', max(t1.job_type)) as "job_type"
from table2 t2
join table1 t1 on t2.time_sample between t1.start_time and t1.end_time
group by t2.job, t2.time_sample

看到db-fiddle