如何在Oracle 18c中查询包含近似重复数值的重复行？

表结构与示例数据 #

Oracle 18c中有ROAD_PROJECTS表，结构及示例数据如下：

with road_projects (proj_id, road_id, year_, status, from_measure, to_measure) as (
select 100, 1, 2022, 'APPROVED',  null, 100.1 from dual union all 
select 101, 1, 2022, 'APPROVED',     0, 100.1 from dual union all
select 102, 1, 2022, 'APPROVED',     0, 200.6 from dual union all
select 103, 1, 2022, 'APPROVED',     0, 199.3 from dual union all
select 104, 1, 2022, 'APPROVED',     0,   201 from dual union all
select 105, 2, 2023, 'PROPOSED',     0,    50 from dual union all
select 106, 2, 2023, 'PROPOSED',    75,   100 from dual union all
select 107, 3, 2024, 'DEFERRED',     0,   100 from dual union all
select 108, 3, 2025, 'DEFERRED',     0,   110 from dual union all
select 109, 4, 2026, 'PROPOSED',     0,  null from dual union all
select 110, 4, 2026, 'DEFERRED',     0,  null from dual)
select * from road_projects

查询结果：

PROJ_ID    ROAD_ID      YEAR_ STATUS   FROM_MEASURE TO_MEASURE
---------- ---------- ---------- -------- ------------ ----------
       100          1       2022 APPROVED         null      100.1 --重复行（除PROJ_ID外）；null视为0
       101          1       2022 APPROVED            0      100.1

       102          1       2022 APPROVED            0      200.6 --重复行：TO_MEASURE在5米公差范围内近似相等
       103          1       2022 APPROVED            0      199.3
       104          1       2022 APPROVED            0        201

       105          2       2023 PROPOSED            0         50 --非重复行：FROM_MEASURE和TO_MEASURE均不同
       106          2       2023 PROPOSED           75        100

       107          3       2024 DEFERRED            0        100 --非重复行：YEAR_和TO_MEASURE均不同
       108          3       2025 DEFERRED            0        110

       109          4       2026 PROPOSED            0       null  --非重复行：STATUS不同
       110          4       2026 DEFERRED            0       null

查询需求 #

需要筛选出ROAD_ID、YEAR_、STATUS、FROM_MEASURE和TO_MEASURE重复的行，具体规则：

• FROM_MEASURE和TO_MEASURE允许5米公差，例如200.6、199.3和201视为重复值（接受任何简便实现方案）
• 比较时将null视为0，输出时返回null或0均可

期望结果 #

PROJ_ID    ROAD_ID      YEAR_ STATUS   FROM_MEASURE TO_MEASURE
---------- ---------- ---------- -------- ------------ ----------
       100          1       2022 APPROVED         null      100.1 --重复行
       101          1       2022 APPROVED            0      100.1

       102          1       2022 APPROVED            0      200.6 --重复行
       103          1       2022 APPROVED            0      199.3
       104          1       2022 APPROVED            0        201

解决方案 #

方案一：区间分组法（适合大数据量） #

核心思路是将数值按5米区间分组，再筛选组内记录数大于1的行：

WITH processed_data AS (
    SELECT 
        proj_id,
        road_id,
        year_,
        status,
        from_measure,
        to_measure,
        -- 将null转为0，按5米区间映射到基准值
        FLOOR(NVL(from_measure, 0)/5)*5 AS from_group,
        FLOOR(NVL(to_measure, 0)/5)*5 AS to_group
    FROM road_projects
),
group_counts AS (
    SELECT 
        road_id,
        year_,
        status,
        from_group,
        to_group,
        COUNT(*) AS group_size
    FROM processed_data
    GROUP BY road_id, year_, status, from_group, to_group
    HAVING COUNT(*) > 1
)
SELECT 
    pd.proj_id,
    pd.road_id,
    pd.year_,
    pd.status,
    pd.from_measure,
    pd.to_measure
FROM processed_data pd
JOIN group_counts gc 
    ON pd.road_id = gc.road_id
    AND pd.year_ = gc.year_
    AND pd.status = gc.status
    AND pd.from_group = gc.from_group
    AND pd.to_group = gc.to_group
ORDER BY pd.proj_id;

• 该方案通过FLOOR(NVL(col,0)/5)*5将数值映射到5米区间下限，比如200.6、199.3、201都会被归到200区间，实现近似重复分组。

方案二：差值匹配法（贴合±5米公差需求） #

直接比较两个数值的绝对差是否≤5，适合数据量不大的场景：

SELECT 
    r1.proj_id,
    r1.road_id,
    r1.year_,
    r1.status,
    r1.from_measure,
    r1.to_measure
FROM road_projects r1
WHERE EXISTS (
    SELECT 1
    FROM road_projects r2
    WHERE r2.proj_id != r1.proj_id
      AND r2.road_id = r1.road_id
      AND r2.year_ = r1.year_
      AND r2.status = r1.status
      -- FROM_MEASURE差值≤5，null视为0
      AND ABS(NVL(r2.from_measure, 0) - NVL(r1.from_measure, 0)) <= 5
      -- TO_MEASURE差值≤5，null视为0
      AND ABS(NVL(r2.to_measure, 0) - NVL(r1.to_measure, 0)) <= 5
)
ORDER BY r1.proj_id;

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