如何批量查询同一Refnr分组下经纬度异常的表格数据

以下方案适用于支持窗口函数的主流数据库（MySQL 8.0+、PostgreSQL、SQL Server等），单次查询即可直接返回所有符合要求的异常行，无需逐Refnr执行查询。 #

核心逻辑 #

1. 按Refnr分组，统计每组内纬度、经度保留2位小数后的众数（即出现次数最多的数值，代表组内主流位置）
2. 逐行对比当前行的经纬度（保留2位小数）和对应组的众数差值，差值超过阈值则判定为异常
3. 仅需调整阈值即可匹配不同的差异判定标准

对应SQL语句 #

WITH ref_group_stats AS (
    -- 计算每个Refnr分组的经纬度众数（保留2位小数）
    SELECT 
        Refnr,
        MODE() WITHIN GROUP (ORDER BY ROUND(Latitude, 2)) AS group_lat_mode,
        MODE() WITHIN GROUP (ORDER BY ROUND(Longitude, 2)) AS group_lon_mode
    FROM 你的表名
    GROUP BY Refnr
)
-- 关联原表筛选离群行
SELECT 
    t.Refnr,
    t.Latitude,
    t.Longitude,
    t.Name
FROM 你的表名 t
JOIN ref_group_stats s ON t.Refnr = s.Refnr
WHERE 
    -- 纬度第二位小数和组内众数差超过2（可自行调整阈值）
    ABS(ROUND(t.Latitude, 2) - s.group_lat_mode) > 0.02
    -- 或经度第二位小数和组内众数差超过2
    OR ABS(ROUND(t.Longitude, 2) - s.group_lon_mode) > 0.02
ORDER BY t.Refnr;

低版本MySQL兼容方案 #

如果使用的数据库不支持MODE()函数，可使用以下替代语句：

SELECT 
    t.Refnr,
    t.Latitude,
    t.Longitude,
    t.Name
FROM 你的表名 t
JOIN (
    -- 统计每组纬度众数
    SELECT 
        Refnr,
        ROUND(Latitude, 2) AS group_lat_mode
    FROM 你的表名
    GROUP BY Refnr, ROUND(Latitude, 2)
    HAVING COUNT(*) = (
        SELECT MAX(cnt) FROM (
            SELECT COUNT(*) AS cnt FROM 你的表名 t2 
            WHERE t2.Refnr = 你的表名.Refnr GROUP BY ROUND(Latitude, 2)
        ) lat_counts
    )
) lat_stats ON t.Refnr = lat_stats.Refnr
JOIN (
    -- 统计每组经度众数
    SELECT 
        Refnr,
        ROUND(Longitude, 2) AS group_lon_mode
    FROM 你的表名
    GROUP BY Refnr, ROUND(Longitude, 2)
    HAVING COUNT(*) = (
        SELECT MAX(cnt) FROM (
            SELECT COUNT(*) AS cnt FROM 你的表名 t3 
            WHERE t3.Refnr = 你的表名.Refnr GROUP BY ROUND(Longitude, 2)
        ) lon_counts
    )
) lon_stats ON t.Refnr = lon_stats.Refnr
WHERE 
    ABS(ROUND(t.Latitude, 2) - lat_stats.group_lat_mode) > 0.02
    OR ABS(ROUND(t.Longitude, 2) - lon_stats.group_lon_mode) > 0.02
ORDER BY t.Refnr;

调整说明 #

• 把语句中你的表名替换为实际的表名即可运行
• 判定阈值0.02可根据业务需求调整，如果要求第二位小数差超过1即判定异常，改为0.01即可
• 如果同Refnr允许存在多个合法聚集区域，可将众数逻辑替换为四分位距离群判定逻辑，适配更复杂的场景

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