基于日期范围关联两张表生成目标数据集的SQL方案咨询

问题背景 #

现有两张数据表TableA和TableB，数据如下：

TableA数据 #

Person  Assignation StartDate   EndDate
usera   BAT A       2016-03-11  2017-02-21
usera   BAT B       2017-02-22  2017-03-28
usera   BAT C       2017-04-01  2017-09-30
usera   BAT C       2017-10-01  2019-12-31
usera   BAT D       2020-01-01  2020-03-31
usera   BAT D       2020-04-01  2021-11-30
usera   BAT E       2021-12-01  2022-03-31
usera   BAT F       2022-04-01  2027-03-31

TableB数据 #

Person  StartDate   Integration
usera   2017-02-15  R0
usera   2017-09-11  R1
usera   2020-05-20  R2
usera   2020-09-03  R3
usera   2021-12-09  R4

需求说明 #

基于日期范围关联两张表，将TableB的Integration字段匹配到TableA的对应时间段中：当Integration的日期落在TableA的某一时间段内时，拆分该时间段生成新记录，最终得到如下目标数据集：

目标结果 #

Person  Assignation Integration StartDate   EndDate
usera   BAT A       R0          2016-03-11  2017-02-21
usera   BAT B       R0          2017-02-22  2017-03-28
usera   BAT C       R0          2017-04-01  2017-09-10
usera   BAT C       R0          2017-09-11  2017-09-30
usera   BAT C       R1          2017-10-01  2019-12-31
usera   BAT D       R1          2020-01-01  2020-05-19
usera   BAT D       R2          2020-05-20  2020-09-02
usera   BAT D       R3          2020-09-03  2021-11-30
usera   BAT E       R3          2021-12-01  2021-12-08
usera   BAT E       R4          2021-12-09  2022-03-31
usera   BAT F       R4          2022-04-01  2027-03-31

实现方案 #

你的思路方向是对的，结合LEAD()函数和范围关联就能实现。以下是兼容大多数支持窗口函数的数据库（如PostgreSQL、SQL Server、BigQuery等）的通用方案：

完整SQL代码 #

WITH TableB_with_end AS (
    SELECT 
        Person,
        StartDate AS Integration_Start,
        Integration,
        LEAD(StartDate, 1) OVER (PARTITION BY Person ORDER BY StartDate) AS Next_Integration_Start
    FROM TableB
),
TableB_ranges AS (
    SELECT 
        Person,
        Integration,
        Integration_Start,
        CASE 
            WHEN Next_Integration_Start IS NOT NULL THEN DATEADD(DAY, -1, Next_Integration_Start)
            ELSE '9999-12-31'
        END AS Integration_End
    FROM TableB_with_end
)
SELECT 
    a.Person,
    a.Assignation,
    b.Integration,
    -- 取两个时间段的起始最大值作为新记录的StartDate
    CASE 
        WHEN a.StartDate < b.Integration_Start THEN b.Integration_Start
        ELSE a.StartDate
    END AS StartDate,
    -- 取两个时间段的结束最小值作为新记录的EndDate
    CASE 
        WHEN a.EndDate > b.Integration_End THEN b.Integration_End
        ELSE a.EndDate
    END AS EndDate
FROM TableA a
JOIN TableB_ranges b 
    ON a.Person = b.Person 
    AND a.StartDate <= b.Integration_End 
    AND a.EndDate >= b.Integration_Start
ORDER BY a.Person, a.Assignation, StartDate;

逻辑说明 #

1. 补充TableB的时间范围：

   • 用LEAD()函数获取每个Integration的下一个生效日期，计算出当前Integration的截止日期（下一个生效日期的前一天）；
   • 最后一条Integration的截止日期设为极大值9999-12-31，确保覆盖后续所有时间段。

2. 关联并拆分时间段：

   • 通过范围关联找到TableA和TableB中时间重叠的记录；
   • 用CASE语句拆分出重叠的子时间段，分别取两个时间段的起始最大值和结束最小值作为新记录的起止日期。

内容的提问来源于stack exchange，提问作者Cascador84