如何通过关联聚合子查询修复行程表中的地点名称/ID空值？

步骤1：预生成标准化地点映射表 #

先把有完整名称/ID的地点数据聚合出标准经纬度（截断到3位小数），生成可复用的映射表，避开JOIN时使用聚合函数的限制。

-- 生成地点映射表（兼容MySQL，BigQuery需替换TIMESTAMPDIFF为TIMESTAMP_DIFF(end_trip, start_trip, MINUTE)）
CREATE TEMPORARY TABLE location_mapping AS
SELECT 
    start_trip_name AS location_name, 
    start_trip_id AS location_id,
    TRUNC(AVG(start_trip_lat), 3) AS standard_lat, 
    TRUNC(AVG(start_trip_lng), 3) AS standard_lng
FROM my_table 
WHERE
    TIMESTAMPDIFF(MINUTE, start_trip, end_trip) BETWEEN 1 AND 1439
    AND start_trip_name IS NOT NULL
GROUP BY start_trip_name, start_trip_id;

步骤2：关联映射表修复空值 #

将原始数据的经纬度先截断到3位小数做标准化，再通过经纬度匹配映射表，用COALESCE替换空的名称/ID。

SELECT
    t.membership,
    t.start_trip,
    t.end_trip,
    TIMESTAMPDIFF(MINUTE, t.start_trip, t.end_trip) AS trip_duration,
    -- 修复起点名称/ID
    COALESCE(t.start_trip_name, lm_start.location_name) AS fixed_start_name,
    COALESCE(t.start_trip_id, lm_start.location_id) AS fixed_start_id,
    TRUNC(t.start_trip_lat, 3) AS start_lat_standard,
    TRUNC(t.start_trip_lng, 3) AS start_lng_standard,
    -- 修复终点名称/ID
    COALESCE(t.end_trip_name, lm_end.location_name) AS fixed_end_name,
    COALESCE(t.end_trip_id, lm_end.location_id) AS fixed_end_id,
    TRUNC(t.end_trip_lat, 3) AS end_lat_standard,
    TRUNC(t.end_trip_lng, 3) AS end_lng_standard
FROM my_table t
-- 关联起点映射表
LEFT JOIN location_mapping lm_start
    ON TRUNC(t.start_trip_lat,3) = lm_start.standard_lat
    AND TRUNC(t.start_trip_lng,3) = lm_start.standard_lng
-- 关联终点映射表
LEFT JOIN location_mapping lm_end
    ON TRUNC(t.end_trip_lat,3) = lm_end.standard_lat
    AND TRUNC(t.end_trip_lng,3) = lm_end.standard_lng
WHERE
    TIMESTAMPDIFF(MINUTE, t.start_trip, t.end_trip) BETWEEN 1 AND 1439
    AND t.end_trip_lat IS NOT NULL;

核心逻辑说明 #

• 提前聚合规避限制：把经纬度聚合操作放在临时表中完成，避免在JOIN的ON子句中使用聚合函数（BigQuery和MySQL均不支持此用法）。
• 经纬度标准化：通过TRUNC统一经纬度精度，解决原始数据格式不统一的问题，确保匹配准确性。
• 空值替换逻辑：COALESCE优先保留原始非空数据，仅在原始值为空时使用映射表匹配到的标准名称/ID。
• 兼容性调整：时间差函数在MySQL中用TIMESTAMPDIFF(MINUTE, start, end)，BigQuery环境替换为TIMESTAMP_DIFF(end_trip, start_trip, MINUTE)即可。

内容的提问来源于stack exchange，提问作者amb-cos