如何用SQL实现重叠时间区间的表关联与多匹配类型标记

安全阿岳

2026-8-30

SQL实现方案

核心思路是先完成两表的区间匹配关联，再针对每个time_sample统计匹配到的TABLE1记录数，按规则生成最终的job_type字段，写法兼容MySQL 8+、Hive、Spark SQL等主流支持窗口函数的SQL引擎。

可直接运行的SQL代码

WITH match_detail AS (
    SELECT 
        t2.job,
        t2.time_sample,
        t1.job_type,
        COUNT(t1.job_id) OVER (PARTITION BY t2.job, t2.time_sample) AS match_cnt
    FROM TABLE2 t2
    LEFT JOIN TABLE1 t1
        ON t2.job = t1.job
        -- 时间字段若为字符串存储，建议先转DATETIME/TIMESTAMP类型再做区间判断，避免比较逻辑出错
        AND t2.time_sample >= t1.start_time
        AND t2.time_sample <= t1.end_time
)
SELECT 
    job,
    time_sample,
    CASE
        WHEN match_cnt = 0 THEN NULL
        WHEN match_cnt > 1 THEN 'MULTIPLE'
        ELSE MAX(job_type)
    END AS job_type
FROM match_detail
GROUP BY job, time_sample, match_cnt
ORDER BY time_sample;

逻辑说明

第一层CTE完成基础关联：按照job字段相等、time_sample落在start_time和end_time闭区间内的规则，关联出所有命中的明细记录
用窗口函数直接统计每个job + time_sample维度下命中的TABLE1记录总数，不需要提前分组就能拿到单条采样点的匹配数量
最后分组输出结果：命中数大于1直接标记为MULTIPLE，命中数为1时取对应记录的job_type即可，运行结果和给出的期望输出完全一致。