SQL Server下基于Google Maps地理数据的供应商组内异常值计算问询

不需要使用临时表存储中间结果，直接基于窗口函数扩展现有CTE逻辑即可实现需求，性能和可读性都优于临时表方案。

完整实现代码 #

WITH cte AS 
(
    SELECT 
        ItemID,
        SupplierID,
        LatLng,
        LatLng.STDistance(GEOGRAPHY::Point(a.Latitude, a.Longitude, 4326))/1000 As Distance
    FROM 
        Items v
    JOIN 
        Suppliers a ON v.SupplierID = a.SupplierID
),
supplier_distance_metrics AS (
    SELECT
        ItemID,
        SupplierID,
        Distance,
        -- 按供应商分组计算平均距离、距离标准差
        AVG(Distance) OVER(PARTITION BY SupplierID) AS supplier_avg_distance,
        STDEV(Distance) OVER(PARTITION BY SupplierID) AS supplier_distance_stdev
    FROM cte
)
SELECT 
    ItemID,
    SupplierID,
    Distance,
    supplier_avg_distance,
    supplier_distance_stdev
FROM supplier_distance_metrics
-- 过滤出距离超出【均值+1倍标准差】的异常商品
WHERE Distance > supplier_avg_distance + supplier_distance_stdev

方案说明 #

• OVER(PARTITION BY SupplierID) 窗口语法会按供应商维度分组计算统计值，不需要用GROUP BY合并行，每个商品行都会直接附带所属供应商的平均距离、标准差指标，无需额外关联中间表
• 当某供应商仅关联1件商品时，STDEV计算结果为NULL，这类数据不会被判定为异常，符合业务逻辑
• 可根据业务灵活调整异常阈值：如果需要更严格的判定规则，把WHERE条件调整为Distance > supplier_avg_distance + 2 * supplier_distance_stdev即可过滤出偏离程度更高的异常商品

可选优化建议 #

如果数据量极大且需要高频查询异常商品列表，可考虑将supplier_distance_metrics的结果存入临时表降低重复计算成本，单次查询场景下直接用上述CTE方案即可。

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