SQL求助：按行程求Entity2与Entity1组合的最小距离

表结构与需求说明 #

• Table1：存储不同TripID对应的行程站点信息，包含字段：TripID、StopNumber、EntityType（注：原描述中「Entity ID」为笔误，实际为EntityType）、Entity_Zip_Code、From_Zip_Code
• Table2：存储邮编及对应的距离数据，包含字段：FromZipCode、ToZipCode、Distance

核心需求 #

针对每个独立行程（按TripID分组）：

1. 提取该行程下所有EntityType=2的邮编
2. 将每个EntityType=1的站点邮编与这些EntityType=2的邮编逐一对比距离
3. 取距离最近的EntityType=2对应的距离，作为该EntityType=1站点的结果
4. 若行程仅存在1个EntityType=2的邮编，则直接取该邮编到每个站点的距离作为结果

示例说明 #

• 行程2：有2个EntityType=2的站点（邮编X、Y）和1个EntityType=1的站点（邮编Z），对比Z与X、Y的距离后，取更近的距离8作为结果
• 行程1：仅1个EntityType=2的站点，直接取该站点到每个行程站点的距离

当前尝试的SQL代码 #

CREATE OR REPLACE TEMPORARY TABLE Final AS 
SELECT table1.*,
       Distance,
       MIN(CASE WHEN EntityType = 2 THEN Distance END) OVER (PARTITION BY TripID ORDER BY StopNumber) AS min_DistanceInMiles
FROM Table1
LEFT JOIN 
   Table2 b
ON 
   Table1.Entity_Zip_Code=b.FromZipCode
   AND Table1.From_Zip_Code=b.ToZipCode
ORDER BY 
   TripID,StopNumber asc

解决方案 #

思路 #

1. 先按TripID分组，提取每个行程下所有EntityType=2的邮编集合
2. 将每个行程的EntityType=1站点与该行程的所有EntityType=2邮编进行关联，匹配Table2中的距离
3. 对每个EntityType=1站点，在关联出的所有距离中取最小值
4. 对于EntityType=2的站点，可保留原距离或按需处理（示例中未显示，可根据实际调整）

实现SQL #

WITH trip_entity2_zips AS (
    -- 提取每个行程下所有EntityType=2的邮编
    SELECT TripID, Entity_Zip_Code AS entity2_zip
    FROM Table1
    WHERE EntityType = 2
),
trip_all_stations_with_distances AS (
    -- 将每个行程的所有站点与该行程的Entity2邮编关联，获取所有可能的距离
    SELECT 
        t1.TripID,
        t1.StopNumber,
        t1.EntityType,
        t1.Entity_Zip_Code,
        t1.From_Zip_Code,
        t2.Distance
    FROM Table1 t1
    -- 关联当前行程的所有Entity2邮编
    JOIN trip_entity2_zips te2 ON t1.TripID = te2.TripID
    -- 匹配邮编间的距离
    LEFT JOIN Table2 t2 
        ON t1.From_Zip_Code = t2.FromZipCode 
        AND te2.entity2_zip = t2.ToZipCode
),
trip_min_distances AS (
    -- 按行程和站点分组，取最小距离
    SELECT 
        TripID,
        StopNumber,
        MIN(Distance) AS min_DistanceInMiles
    FROM trip_all_stations_with_distances
    WHERE EntityType = 1  -- 仅处理EntityType=1的站点
    GROUP BY TripID, StopNumber
)
-- 最终结果：将最小距离关联回原表
SELECT 
    t1.*,
    tmd.min_DistanceInMiles
FROM Table1 t1
LEFT JOIN trip_min_distances tmd 
    ON t1.TripID = tmd.TripID 
    AND t1.StopNumber = tmd.StopNumber
ORDER BY t1.TripID, t1.StopNumber ASC;

代码说明 #

• trip_entity2_zips：预查询每个行程的所有Entity2邮编，避免重复计算
• trip_all_stations_with_distances：将每个行程的站点与该行程的Entity2邮编全关联，得到所有可能的距离组合
• trip_min_distances：对每个Entity1站点，计算其到当前行程所有Entity2邮编的最小距离
• 最后关联回原表，得到每个站点对应的最小距离（Entity2站点可根据需求决定是否保留距离值）

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