时间戳

首页 > 解决方案 > Google Maps SQL Server：计算组内的异常地理数据

sql-server - Google Maps SQL Server：计算组内的异常地理数据

问题描述

有 100 个供应商，每个供应商有 50 到 1000 件商品。每个供应商都可能在他们的办公室附近或遍布整个国家或大陆拥有物品。

由于 LatLngs 是由人工输入的，因此会发生一些错误。由于大量数据和不断的“流失”，错误很难识别。

为了提高数据质量，我想识别每个供应商的异常值，以便修复它们。如果供应商的商品大部分都在纽约附近，那么加利福尼亚的商品将是一个异常值。

供应商

SupplierID int
Latitude DECIMAL(12,9)
Longitude DECIMAL(12,9)

项目

ItemID int
SupplierID int
LatLng geography

我假设我需要为此使用标准偏差，但是将它放入 T-SQL 让我很头疼。

我想根据每个供应商的特定偏差输出每个供应商的异常值列表。

此代码输出项目以及每个项目与供应商办公室之间的距离。

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
)
SELECT 
    ItemID, SupplierID, Distance 
FROM cte

这是标准偏差的 SQL 功能（来自博客文章）：

DECLARE @StdDev DECIMAL(5,2) 
DECLARE @Avg DECIMAL(5,2)

SELECT 
   @StdDev = STDEV(Qty), 
   @Avg = AVG(Qty) 
FROM Sales

SELECT 
   * 
FROM 
   Sales 
WHERE 
   Qty > @Avg - @StdDev AND
   Qty < @Avg + @StdDev

我需要做的步骤

  1. 计算距离STDEV，AVGGROUP BY SupplierID
  2. 输出距离大于AVG + STDEV项目供应商的项目

这是我挠头的地方，因为这是我已经执行的多个步骤之后的多个步骤。我想我可以将我拥有的内容插入 TEMP 表并从那里开始，但这真的是最好的方法吗？

标签： sql-serverstandard-deviation

解决方案

您可以为此使用窗口函数。两者AVG和STDEV都可用作窗口函数

WITH Distances AS 
(
    SELECT 
        i.ItemID,
        s.SupplierID,
        i.LatLng,
        v.SupplierLocation,
        i.LatLng.STDistance(v.SupplierLocation)/1000 As Distance
    FROM 
        Items i
    JOIN 
        Suppliers s ON i.SupplierID = s.SupplierID
    CROSS APPLY (VALUES (
        GEOGRAPHY::Point(s.Latitude, s.Longitude, 4326)
    )) v(SupplierLocation)
),
Averages AS (
    SELECT
        ItemID,
        SupplierID,
        LatLng,
        SupplierLocation
        Distance,
        AVG(Distance)   OVER (PARTITION BY SupplierID) AS Avg,
        STDEV(Distance) OVER (PARTITION BY SupplierID) AS StDev
    FROM
        Distances
)
SELECT 
    ItemID,
    SupplierID,
    Distance,
    Avg,
    StDev
FROM
    Averages
WHERE 
    Distance > Avg - StdDev AND
    Distance < Avg + StdDev;

推荐阅读