在Synapse专用池中编写T-SQL存储过程实现门店竞品距离映射

以下是满足需求的T-SQL存储过程，可直接在Synapse专用池中执行：

CREATE PROCEDURE dbo.InsertCompetitorDistanceMapping
AS
BEGIN
    SET NOCOUNT ON;

    -- 可选：若需要每次执行先清空Mapping表旧数据，取消下面注释
    -- TRUNCATE TABLE dbo.Mapping;

    -- 计算并插入符合条件的记录
    INSERT INTO dbo.Mapping (Out_Id, Out_Name, Comp_Name, Comp_Distance)
    SELECT 
        o.Out_id,
        o.Out_name,
        c.Comp_Name,
        -- 按给定公式计算径向距离（单位：公里），保留2位小数
        ROUND(SQRT(POWER(c.comp_Latitude - o.Out_Latitude, 2) + POWER(c.comp_Longitude - o.Out_Longitude, 2)) * 111, 2) AS Comp_Distance
    FROM 
        dbo.Outlets o
    CROSS JOIN 
        dbo.Competition c
    WHERE 
        -- 筛选距离小于5公里的记录
        SQRT(POWER(c.comp_Latitude - o.Out_Latitude, 2) + POWER(c.comp_Longitude - o.Out_Longitude, 2)) * 111 < 5;
END;

关键说明 #

• 全量配对计算：用CROSS JOIN实现每个门店与所有竞品的配对，确保覆盖所有组合的距离计算。
• 距离公式实现：严格遵循需求中的计算公式，ROUND函数用于统一距离精度（可根据业务需求调整小数位数）。
• 条件筛选：直接在WHERE子句中过滤出距离小于5公里的记录，只插入符合要求的数据。
• 清空表选项：如果需要每次执行存储过程时重置Mapping表，可启用TRUNCATE TABLE语句。

大数据量场景优化建议 #

• 为经纬度字段创建统计信息，帮助Synapse生成更优的查询计划：

  CREATE STATISTICS stats_Outlets_LatLon ON dbo.Outlets (Out_Latitude, Out_Longitude);
  CREATE STATISTICS stats_Competition_LatLon ON dbo.Competition (comp_Latitude, comp_Longitude);
  

• 若数据量极大，可将计算逻辑拆分为分批处理，避免单次查询占用过多资源。

执行存储过程 #

EXEC dbo.InsertCompetitorDistanceMapping;

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