如何基于跨表时间戳匹配将单条公交取消记录扩展为多行

假设你管理公交系统，拥有两个表：bus_cancellations和bus_timetables：

bus_cancellations表存储所有公交班次取消记录（此处仅展示1条），每行代表被取消的时刻表区间（例如：全表取消=当日该公交完全停运；部分取消=公交仅在特定站点间运营）。此外，cancellation_time列记录班次取消时间。

bus_timetables：存储所有公交各日期的所有时刻表版本。公交可能有多个版本的时刻表，因为规划可能在运营日前修改时刻表，甚至在本该发车后仍可能调整（无论是否取消）。timetable_creation_time列记录时刻表生效时间。

• 在bus_timetables表中，确定公交取消时生效的时刻表（通过bus_cancellations.cancellation_time和bus_timetables.timetable_creation_time匹配）。逻辑为：选取创建时间早于或等于取消时间的最新时刻表。
• 将bus_cancellations中被取消的时刻表区间（stop_from至stop_to）扩展为bus_timetables表中对应站点的单行记录，实现按站点拆分展示。

根据上述示例表，预期结果如下：

针对目标1，尝试用Lateral Join获取取消时生效的时刻表记录，但尚未得到可行查询语句。
针对目标2，了解到可通过关联子查询将bus_cancellations中stop_from至stop_to的站点区间扩展为单行记录，但不确定该方案是否最优：

SELECT
  c.bus_id,
  c.driving_day,
  stop_from,
  stop_to,
  stop,
  timetable_stop_entry_position,
  t.timetable_version,
  t.timetable_creation_time
FROM bus_cancellations AS c
LEFT JOIN bus_timetables AS t
ON
  c.bus_id = t.bus_id
  AND c.driving_day = t.driving_day
WHERE
  timetable_stop_entry_position BETWEEN (SELECT timetable_stop_entry_position FROM bus_timetables WHERE stop = c.stop_from LIMIT 1)
  AND (SELECT timetable_stop_entry_position FROM bus_timetables WHERE stop = c.stop_to LIMIT 1);

目前尚未找到同时满足两个目标的完整且高效的解决方案。

以下是同时满足两个目标的SQL查询，兼容多数现代数据库：

WITH effective_timetables AS (
    SELECT
        c.bus_id,
        c.driving_day,
        c.cancellation_type,
        c.stop_from,
        c.stop_to,
        c.cancellation_time,
        MAX(t.timetable_version) AS effective_version
    FROM bus_cancellations c
    JOIN bus_timetables t
        ON c.bus_id = t.bus_id
        AND c.driving_day = t.driving_day
        AND t.timetable_creation_time <= c.cancellation_time
    GROUP BY c.bus_id, c.driving_day, c.cancellation_type, c.stop_from, c.stop_to, c.cancellation_time
),
stop_position_mapping AS (
    SELECT
        bus_id,
        driving_day,
        timetable_version,
        stop,
        timetable_stop_entry_position,
        arrive_time,
        depart_time
    FROM bus_timetables
)
SELECT
    et.bus_id,
    et.driving_day,
    et.cancellation_type,
    et.stop_from,
    et.stop_to,
    et.cancellation_time,
    sp.stop,
    sp.arrive_time,
    sp.depart_time
FROM effective_timetables et
JOIN stop_position_mapping sp
    ON et.bus_id = sp.bus_id
    AND et.driving_day = sp.driving_day
    AND sp.timetable_version = et.effective_version
    AND sp.timetable_stop_entry_position BETWEEN (
        SELECT timetable_stop_entry_position
        FROM stop_position_mapping
        WHERE bus_id = et.bus_id
          AND driving_day = et.driving_day
          AND timetable_version = et.effective_version
          AND stop = et.stop_from
    ) AND (
        SELECT timetable_stop_entry_position
        FROM stop_position_mapping
        WHERE bus_id = et.bus_id
          AND driving_day = et.driving_day
          AND timetable_version = et.effective_version
          AND stop = et.stop_to
    )
ORDER BY sp.timetable_stop_entry_position;

逻辑说明 #

1. effective_timetables CTE：关联取消记录与时刻表，筛选出取消时间前创建的所有版本，取最大版本号即为取消时生效的最新时刻表。
2. stop_position_mapping CTE：预存各时刻表版本的站点位置与时间信息，简化后续区间匹配逻辑。
3. 最终查询：将生效时刻表与对应站点关联，通过站点位置区间筛选出被取消的站点范围，得到按站点拆分的结果。

优化建议 #

• 为bus_timetables创建复合索引：(bus_id, driving_day, timetable_creation_time, timetable_version)，加速生效版本的查询。
• 为bus_timetables创建索引：(bus_id, driving_day, timetable_version, stop)，提升站点位置匹配的效率。

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