基于两张表的值在SQL中合并行程数据行的技术咨询

首先明确假设的表结构（如果你的表结构有差异，可对应调整字段名）：

表结构示例 #

trip_details（行程详情表） #

TravelPlan（行程规划表） #

方案一：递归CTE匹配连续行程（通用可靠） #

这个方案通过递归遍历连续的子行程，精准匹配TravelPlan中定义的完整起止点，自动聚合距离和时长：

WITH recursive_trips AS (
    -- 锚点：定位每个规划行程的起始子行程
    SELECT 
        td.CarID,
        td.Source AS current_source,
        td.Destination AS current_dest,
        td.Distance,
        td.Time,
        tp.StartPoint AS plan_start,
        tp.EndPoint AS plan_end,
        1 AS trip_seq
    FROM trip_details td
    JOIN TravelPlan tp 
        ON td.CarID = tp.CarID 
        AND td.Source = tp.StartPoint

    UNION ALL

    -- 递归：连接后续连续的子行程，直到到达规划终点
    SELECT 
        rt.CarID,
        td.Source AS current_source,
        td.Destination AS current_dest,
        rt.Distance + td.Distance AS Distance,
        rt.Time + td.Time AS Time,
        rt.plan_start,
        rt.plan_end,
        rt.trip_seq + 1 AS trip_seq
    FROM recursive_trips rt
    JOIN trip_details td 
        ON rt.CarID = td.CarID 
        AND rt.current_dest = td.Source
        AND rt.current_dest != rt.plan_end
)
-- 提取每个完整规划行程的最终聚合结果
SELECT 
    CarID,
    plan_start AS Source,
    plan_end AS Destination,
    Distance AS TotalDistance,
    Time AS TotalTime
FROM recursive_trips
WHERE current_dest = plan_end
ORDER BY CarID;

说明：

• 递归CTE先找到每个规划的起始行程，再逐步拼接后续连续的子行程
• 自动累加Distance和Time，直到到达TravelPlan定义的终点
• 支持分布式SQL引擎（如Spark SQL、Hive），需确保递归CTE功能开启（例如Spark需设置spark.sql.recursiveCTE.enabled=true）

方案二：窗口函数分组（适用于行程可排序场景） #

如果你的Source/Destination是可排序的（如字母顺序、站点编号），可以用窗口函数快速分组聚合：

WITH trip_groups AS (
    SELECT 
        td.*,
        tp.StartPoint,
        tp.EndPoint,
        -- 标记每个完整行程的分组ID：遇到规划起点则分组+1
        SUM(CASE WHEN td.Source = tp.StartPoint THEN 1 ELSE 0 END) 
            OVER (PARTITION BY td.CarID ORDER BY td.Source) AS group_id
    FROM trip_details td
    JOIN TravelPlan tp 
        ON td.CarID = tp.CarID
        -- 筛选属于当前规划范围内的子行程
        AND td.Source BETWEEN tp.StartPoint AND tp.EndPoint
)
SELECT 
    CarID,
    MIN(CASE WHEN Source = StartPoint THEN Source END) AS Source,
    MAX(CASE WHEN Destination = EndPoint THEN Destination END) AS Destination,
    SUM(Distance) AS TotalDistance,
    SUM(Time) AS TotalTime
FROM trip_groups
GROUP BY CarID, group_id
ORDER BY CarID;

说明：

• 利用窗口函数生成分组ID，将同一段完整行程的子行程归为一组
• 前提是Source/Destination具备可排序性，否则BETWEEN无法正确匹配
• 适合数据规整、无乱序子行程的场景

注意事项 #

• 如果TravelPlan中同一CarID有多个规划行程，需加入PlanID到分组条件中，避免混淆
• 若存在不属于任何TravelPlan的子行程，可在JOIN前添加过滤条件排除
• 分布式环境下，需确保表关联的分区键（如CarID）合理，避免数据倾斜

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