sql - 在表中查找重复项

Question

我有一个包含重复项的表。识别重复项的方法是 - key 应该在同一组(1、2、3 或 4) - p 应该相同 - P 是一个 id，表示这个键是相同的一个键只能在同一组中匹配多次。

假设我们有下面这个示例:

key,p,group
1,1,1
33,1,1
5,1,1
5,2,1
4,2,1
4,15,1
8,4,1
10,5,1
15,6,1
21,15,1
78,7,1
79,8,2
80,8,2
81,9,3
82,9,3
85,10,4
90,11,1
91,11,1
73,12,1

输出应该是:

key,p,group
1,999,1
5,999,1
4,999,1
21,999,1
33,999,1
8,4,1
10,5,1
15,6,1
78,7,1
79,111,2
80,111,2
81,666,3
82,666,3
85,10,4
90,222,1
91,222,1
73,12,1

1,5,4,21 和 33 具有相同的 p 值(999，这个数字只是一个新的 id，用于将重复项分组在一起)，因为他们在同一组 (group=1) 并且 1,5 和 33 匹配 (p=1) , 5 和 4 匹配 (p=2) , 4 和 21 匹配 (p=15)

对于 90,91，即使它们在组 1 中，它们也只匹配在一起，因为它们不与该组中的另一个键链接(交叉)。

79和80在同一组(group=2)

8 保持p = 4 因为他不匹配组中的其他key = 1。

等等...我正在寻找一种在 SQL(Oracle) 或算法中执行此操作的方法...

其实，它不工作。如果你在输入中有这个:

key,p,group
55,9,6
56,10,6
56,11,6
58,9,6
58,11,6

输出将是

key,p,group
55,9,6
56,9,6
58,9,6
56,10,6
58,10,6

或者我需要:

key,p,group
55,9,6
56,9,6
58,9,6
56,9,6
58,9,6

感谢帮助

Answer 1

如果我正确理解了问题:将行视为(无向)图的节点，如果节点具有相同的 p 和组值或相同的键和组值，则边连接节点。然后找到这个图的连通分量，改变p值，使一个连通分量中的所有节点都具有相同的p值。

如果是这样，这可以通过分层查询来完成(加上它前后所需的所有处理；主要部分是分层查询)。在下面的解决方案中，我将连通分量中的所有 p 值更改为组中 p 值的最小值(而不是随机值)；如果需要“随机值”也可以做到，但这是一个不同的问题，有一个更简单的解决方案(一开始可能不需要)。

GROUP 不是一个好的列名，因为它是 Oracle 中的保留字。我将其更改为 GRP。

with
-- begin test data (this is not part of the solution)
     inputs ( key, p, grp ) as (
       select  1,  1, 1 from dual union all
       select 33,  1, 1 from dual union all
       select  5,  1, 1 from dual union all
       select  5,  2, 1 from dual union all
       select  4,  2, 1 from dual union all
       select  4, 15, 1 from dual union all
       select  8,  4, 1 from dual union all
       select 10,  5, 1 from dual union all
       select 15,  6, 1 from dual union all
       select 21, 15, 1 from dual union all
       select 78,  7, 1 from dual union all
       select 79,  8, 2 from dual union all
       select 80,  8, 2 from dual union all
       select 81,  9, 3 from dual union all
       select 82,  9, 3 from dual union all
       select 85, 10, 4 from dual union all
       select 90, 11, 1 from dual union all
       select 91, 11, 1 from dual union all
       select 73, 12, 1 from dual union all
       select 55,  9, 6 from dual union all
       select 56, 10, 6 from dual union all
       select 56, 11, 6 from dual union all
       select 58,  9, 6 from dual union all
       select 58, 11, 6 from dual
     ),
-- end of test data; solution (SQL query) continues below this line
     prep ( grp, parent, child ) as (
       select distinct a.grp, a.p, b.p
       from   inputs a inner join inputs b
                       on a.grp = b.grp and a.key = b.key
     ),
     h ( grp, rt, child ) as (
       select grp, connect_by_root parent, child
       from   prep
       connect by nocycle grp = prior grp and parent = prior child
     )
select distinct i.key, g.new_p as p, i.grp
from   inputs i join (
                       select grp, rt, min(child) as new_p
                       from   h
                       group by grp, rt
                     ) g
                 on g.grp = i.grp and g.rt = i.p
order by grp, p, key   --   optional
;

输出:

       KEY          P        GRP
---------- ---------- ----------
         1          1          1
         4          1          1
         5          1          1
        21          1          1
        33          1          1
         8          4          1
        10          5          1
        15          6          1
        78          7          1
        90         11          1
        91         11          1
        73         12          1
        79          8          2
        80          8          2
        81          9          3
        82          9          3
        85         10          4
        55          9          6
        56          9          6
        58          9          6

20 rows selected.