多周期日期差计算问题：关联合同起止与退出日期

现有数据库表结构及数据如下：

CREATE TABLE tab1 (
    id int,
    year float
);

INSERT INTO tab1 (id, year)
VALUES 
  (1, '2015'),
       (1, '2016'),
       (1, '2017'),
       (1, '2018'),
       (1, '2019'),
       (1, '2020'),
       (1, '2021'),
      (3, '2014'),   
       (3, '2015'),
       (3, '2016'),
       (3, '2017'),
       (3, '2018'),
       (3, '2019'),
       (3, '2020')

  
CREATE TABLE tab2 (
    id int,
    startdate date,
    enddate date
);

INSERT INTO tab2 (id, startdate, enddate)
VALUES 
  (1,'2015-03-01', '2020-12-31'),
       (1,'2021-01-01', '2021-04-20'),
  (3, '2014-03-01', '2019-08-09'),
       (3, '2019-08-12', '2020-01-31')

同一ID可拥有多个无间隔（如ID1）或有间隔（如ID3）的合同。

当前错误计算结果 #

当前计算结果中，diff_smallest_startdate_drop_out字段存在错误（加粗部分）：

需求说明 #

需要计算startdate与drop_out日期之间的日差值，但当前错误地使用了ID对应的最小startdate进行计算。对于存在多次drop_out记录的ID（如ID3），第二次计算时需使用第一次drop_out日期之后的下一个startdate，期望得到如下正确结果：

期望正确结果 #

解决方案 #

要实现这个逻辑，需要先为每个ID的合同按时间顺序分组，再为每个drop_out记录匹配对应的合同起始日期。以下是实现的SQL查询：

WITH ranked_contracts AS (
    -- 为每个ID的合同按起始日期排序，分配序号
    SELECT 
        id, 
        startdate, 
        enddate,
        ROW_NUMBER() OVER (PARTITION BY id ORDER BY startdate) AS contract_seq
    FROM tab2
),
drop_out_records AS (
    -- 关联年度记录与合同，标记合同结束年度的drop_out日期
    SELECT 
        t1.id,
        t1.year,
        rc.startdate,
        rc.enddate,
        rc.contract_seq,
        CASE 
            WHEN t1.year = EXTRACT(YEAR FROM rc.enddate) THEN rc.enddate 
            ELSE NULL 
        END AS drop_out
    FROM tab1 t1
    JOIN ranked_contracts rc ON t1.id = rc.id
    WHERE t1.year >= EXTRACT(YEAR FROM rc.startdate) 
      AND t1.year <= EXTRACT(YEAR FROM rc.enddate)
),
grouped_drops AS (
    -- 提取每个合同组的起始日期
    SELECT 
        *,
        FIRST_VALUE(startdate) OVER (PARTITION BY id, contract_seq ORDER BY year) AS contract_start
    FROM drop_out_records
)
SELECT 
    id,
    year,
    startdate,
    enddate,
    drop_out,
    CASE 
        WHEN drop_out IS NOT NULL THEN 
            EXTRACT(DAY FROM (drop_out - contract_start))
        ELSE NULL 
    END AS diff_smallest_startdate_drop_out
FROM grouped_drops
ORDER BY id, year, startdate;

逻辑说明 #

1. ranked_contracts：为每个ID的合同按起始日期排序并分配序号，区分不同阶段的合同。
2. drop_out_records：将年度记录与合同关联，仅在合同结束年度标记drop_out日期。
3. grouped_drops：为每个合同组提取对应的起始日期，确保drop_out使用当前合同的起始日期而非ID的最小起始日期。
4. 最终查询：计算drop_out与对应合同起始日期的天数差，得到正确结果。

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