如何实现基于日期与ID的指定条件数据表自连接？

原始数据表 #

┌──────┬─────┬─────┐
│ date ┆ id  ┆ v   │
│ ---  ┆ --- ┆ --- │
│ i64  ┆ i64 ┆ i64 │
╞══════╪═════╪═════╡
│ 1    ┆ 1   ┆ 1   │
│ 1    ┆ 2   ┆ 2   │
│ 2    ┆ 2   ┆ 3   │
│ 2    ┆ 3   ┆ 4   │
│ 3    ┆ 3   ┆ 5   │
│ 3    ┆ 4   ┆ 6   │
└──────┴─────┴─────┘

需求说明 #

• 自连接时仅保留left.date = right.date + 1的日期配对
• 针对ID执行全外连接，保留左右表中所有ID（无论是否匹配）
• 期望输出结果：

date_left date_right id_left id_right v_left v_right
    2         1        NULL     1      NULL    1
    2         1         2       2       3      2
    2         1         3      NULL     4     NULL
    3         2        NULL     2      NULL    3
    3         2         3       3       5      4
    3         2         4      NULL     6     NULL

实现代码（标准SQL） #

WITH date_pairs AS (
    -- 筛选所有符合left.date = right.date +1的日期组合
    SELECT DISTINCT t1.date AS date_left, t2.date AS date_right
    FROM your_table t1
    JOIN your_table t2 ON t1.date = t2.date + 1
),
left_data AS (
    -- 提取左表（对应date_left）的所有数据
    SELECT date, id, v FROM your_table
),
right_data AS (
    -- 提取右表（对应date_right）的所有数据
    SELECT date, id, v FROM your_table
)
SELECT
    dp.date_left,
    dp.date_right,
    ld.id AS id_left,
    rd.id AS id_right,
    ld.v AS v_left,
    rd.v AS v_right
FROM date_pairs dp
-- 关联左表对应日期的数据
LEFT JOIN left_data ld ON ld.date = dp.date_left
-- 按ID做全外连接，关联右表对应日期的数据
FULL JOIN right_data rd ON rd.date = dp.date_right AND ld.id = rd.id
-- 按日期和ID排序，保证结果顺序和期望一致
ORDER BY dp.date_left, COALESCE(ld.id, rd.id);

逻辑说明 #

1. 日期配对筛选：通过date_pairs先锁定有效日期组合，避免无效的日期关联
2. 全外连接逻辑：对每个日期组合内的左右数据，按ID做全外连接，覆盖三种场景：
   • 右表存在某ID但左表同日期无该ID
   • 左右表同日期同ID均存在
   • 左表存在某ID但右表同日期无该ID
3. 排序处理：通过ORDER BY保证输出顺序和期望结果一致

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