选择表中的离群值

Question

我有一个包含大约100,000个名称/行的表，看起来像这样。大约有3000种不同的参考文献。这些名字在地理上集中在refr附近。问题是有些名字的位置是错误的。我需要找到那些不适合的行。我想我会通过找到离经度和纬度太远的经度来做到这一点。因此，如果你看到第一个Refnr，它们中的两个位于纬度10.67xxx，而1位于纬度10.34xxx。

如果我说我想比较不同ref中的所有名字，并找出第二个十进制数与其他名字的不同之处。

是否有办法这样做，这样我就不必手动运行查询3000次?

<表类> Refnr 纬度经度名称tbody><<tr>12310.6764350.67523鲍勃12310.6714350.67737乔12310.3413350.67848al23411.5689250.12324berny23411.5612350.12432漂亮的23411.9813550.12223arby56710.2289250.67143地56710.2212350.67236蒂莉56710.2214850.22422比利

希望这是一个有效的解决方案-它给出了样本数据中的3个异常值，看看它是否适用于更大的数据集将会很有趣。

为每个经度和纬度创建一个CTE，仅根据小数点后两位计算匹配值的数量，并选择每个组的最小值-即该组的离群值。

将结果与主表和过滤器连接到仅匹配离群值的行。

with outlierLat as (
select top (1) with ties refnr,  Round(latitude,2,1) latitude
from t
group by refnr, Round(latitude,2,1)
order by Count(*) 
), outlierLong as (
select top (1) with ties refnr,  Round(Longitude,2,1) Longitude
from t
group by refnr, Round(Longitude,2,1)
order by Count(*) 
)
select t.* 
from t
left join outlierLat lt on lt.refnr=t.refnr and Round(t.latitude,2,1)=lt.latitude
left join outlierLong lo on lo.refnr=t.refnr and Round(t.Longitude,2,1)=lo.Longitude
where lt.latitude is not null or lo.Longitude is not null

参见demo Fiddle

这变得过于复杂，可能没有那么有用。尽管如此，研究它还是很有趣的。

首先，设置测试数据

DROP TABLE #Test
GO
CREATE TABLE #Test
(
Refnr      int           not null
,Latitude   decimal(7,5)  not null
,Longitude  decimal(7,5)  not null
,Name       varchar(100)  not null
)
INSERT #Test VALUES
(123, 10.67643, 50.67523, 'bob')
,(123, 10.67143, 50.67737, 'joe')
,(123, 10.34133, 50.67848, 'al')
,(234, 11.56892, 50.12324, 'berny')
,(234, 11.56123, 50.12432, 'bonny')
,(234, 11.98135, 50.12223, 'arby')
,(567, 10.22892, 50.67143, 'nilly')
,(567, 10.22123, 50.67236, 'tilly')
,(567, 10.22148, 50.22422, 'billy')
SELECT *
from #Test

由于需求有点不精确，使用它来舍入到所需的精度。

DECLARE  @Precision  TINYINT  = 1
--SELECT
--   Latitude
--  ,round(Latitude, @Precision)
-- from #Test

然后就乱了。如果在纬度或经度上有多个"异常值"，问题就会出现。我认为这将解释所有问题，并删除重复项，但需要进一步的审查和测试。

;WITH cteGroups as (
--  Set up groups by lat/lon proximity
SELECT
Refnr
,'Latitude'  Type
,round(Latitude, @Precision)  Proximity
,count(*)  HowMany
from #Test
group by
Refnr
,round(Latitude, @Precision)
UNION ALL SELECT
Refnr
,'Longitude'  Type
,round(Longitude, @Precision)  Proximity
,count(*)  HowMany
from #Test
group by
Refnr
,round(Longitude, @Precision)
)
,cteOutliers as (
--  Identify outliers
select
Type
,Refnr
,Proximity
,row_number() over (partition by Type, Refnr order by HowMany desc)  Ranking
from cteGroups
)
--  Pull out all items that match with outliers
select te.*
from cteOutliers  cte
inner join #Test  te
on te.Refnr = cte.Refnr
and (   (cte.Type = 'Latitude' and round(te.Latitude, @Precision) = Proximity)
or (cte.Type = 'Longitude' and round(te.Longitude, @Precision) = Proximity) )
where cte.Ranking > 1  --  Not in the larger groups

计算位置中心的平均值，并查找远离它的位置

SELECT *
,  ABS((SELECT Sum(Latitude) / COUNT(*) FROM #Test) - Latitude) 
+  ABS((SELECT Sum(Longitude) / COUNT(*) FROM #Test) - Longitude) as Awayfromhome
from #Test
Order by Awayfromhome desc