如何通过匹配Postcode补全SQL表中缺失的经纬度值

我需要对一些实体进行地理定位，所有实体都有完整的Postcode（邮编）数据，但部分实体的latitude（纬度）和longitude（经度）信息缺失。希望通过匹配相同邮编对应的有效经纬度值，补全这些缺失的数据。我是SQL新手，尝试用自连接方式补全，但结果仅重复数据，没达到预期效果，以下是我创建的测试数据，请教如何补全Postcode为7010和6011的缺失值？

with [test_data] ([key], [Postcode],[latitude],[longitude]) as
     (
       select '1', '7010', '-41.27471' ,'173.28356'   union all 
       select '2', '8011' ,'-43.53299','172.63588'    union all
       select '3', '7010' , NULL      , NULL         union all
       select '4', '6011', '-41.29324', '174.78341' union all
       select '5', '2113','-37.08052'   ,'174.92238'union all
       select '6', '6011', NULL,    '174.78341'        
     )
-- 我之前的错误尝试：
SELECT *  FROM(
    SELECT [key],[Postcode],[latitude], [longitude]
    FROM [test_data]) AS s
LEFT JOIN [test_data] j
ON j.[key] = s.[key]

你的自连接条件错误，j.[key] = s.[key] 只会让每条数据和自身关联，自然只会得到重复数据。正确逻辑是按相同Postcode关联，用同邮编下的非空经纬度补全缺失值，以下是两种可行方法：

方法一：自连接+COALESCE补全 #

通过自连接关联同邮编的有效记录，用COALESCE优先保留自身非空值，为空时取关联到的有效数据：

with [test_data] ([key], [Postcode],[latitude],[longitude]) as
     (
       select '1', '7010', '-41.27471' ,'173.28356'   union all 
       select '2', '8011' ,'-43.53299','172.63588'    union all
       select '3', '7010' , NULL      , NULL         union all
       select '4', '6011', '-41.29324', '174.78341' union all
       select '5', '2113','-37.08052'   ,'174.92238'union all
       select '6', '6011', NULL,    '174.78341'        
     )
SELECT 
    s.[key],
    s.[Postcode],
    COALESCE(s.latitude, j.latitude) as latitude,
    COALESCE(s.longitude, j.longitude) as longitude
FROM [test_data] s
LEFT JOIN [test_data] j 
    ON s.Postcode = j.Postcode 
    AND j.latitude IS NOT NULL 
    AND j.longitude IS NOT NULL
GROUP BY s.[key], s.[Postcode], s.latitude, s.longitude;

方法二：先聚合邮编经纬度，再关联补全 #

先对每个邮编聚合出唯一的有效经纬度（假设同一邮编的经纬度一致），再和原表关联，避免自连接可能产生的重复行：

with [test_data] ([key], [Postcode],[latitude],[longitude]) as
     (
       select '1', '7010', '-41.27471' ,'173.28356'   union all 
       select '2', '8011' ,'-43.53299','172.63588'    union all
       select '3', '7010' , NULL      , NULL         union all
       select '4', '6011', '-41.29324', '174.78341' union all
       select '5', '2113','-37.08052'   ,'174.92238'union all
       select '6', '6011', NULL,    '174.78341'        
     ),
postcode_locations as (
    SELECT 
        Postcode,
        MAX(latitude) as latitude,
        MAX(longitude) as longitude
    FROM test_data
    GROUP BY Postcode
)
SELECT 
    t.[key],
    t.Postcode,
    COALESCE(t.latitude, pl.latitude) as latitude,
    COALESCE(t.longitude, pl.longitude) as longitude
FROM test_data t
LEFT JOIN postcode_locations pl ON t.Postcode = pl.Postcode;

关键说明 #

• COALESCE函数会返回第一个非空参数，确保自身有值时优先保留，仅缺失时才用同邮编的有效数据。
• 方法二更适合存在多条同邮编有效记录的场景，聚合操作能确保每个邮编仅对应一组经纬度，避免结果重复。

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