基于多个字段的相似数据去重

Question

我想从一个原始表中创建一个可能存在重复记录的表，但是这些数据基于两个不同的属性，并且它们只能连接到相同的分组id中。此外，在某些情况下，数据看起来并不完全相同(但有相似之处)。下面是原始表的样子：

group_id| House_num | Apt    | code
----------------------------------
   45   | 1000      |  1     |  M
   45   |    1      |        |  D
   45   | 1000      |  2     |  M
   45   |    2      |        |  D
   87   | 2300      | 310    |  M
   87   | 2310      |        |  D
   87   | 2400      | 470    |  M
   87   | 2470      |        |  D

我希望返回的是这些like数字都在同一行的位置。所以就像这样：

new_id  |group_id|a.house_num|a.apt|a.code|b.house_num|b.apt| b.code
-------------------------------------------------------------------------
   1    |   45   |   1000   |  1   |  M   |    1      |     |  D
   2    |   45   |   1000   |  2   |  M   |    2      |     |  D  
   3    |   87   |   2300   | 310  |  M   | 2310      |     |  D
   4    |   87   |   2400   | 470  |  M   | 2470      |     |  D

我不确定在这里使用哪种联接；此外，我也不确定如何绕过a.house_num是基数，a.apt是后缀数字，b.housenumber是这两个数字的组合。任何帮助都将不胜感激，谢谢。

Answer 1

你可以使用下面的代码来获得你的答案。

        WITH data
         AS (SELECT * FROM YOUR_TABLE),
         data2
         AS (SELECT row_number()over 
             (order by thing1,thing2)    rw,
                    d1.group_id,
                    d1.thing1 a_thing1,
                    d1.thing2 a_thing2,
                    d1.thing3 a_thing3
             FROM   data d1
             WHERE  d1.thing3 = 'M'),
         data3
         AS (SELECT row_number()over 
                   (order by thing1,thing2)    rw,
                    d1.group_id,
                    d1.thing1 b_thing1,
                    d1.thing2 b_thing2,
                    d1.thing3 b_thing3
             FROM   data d1
             WHERE  d1.thing3 = 'D')
    SELECT d2.rw New_id,
           d2.group_id,
           d2.a_thing1,
           d2.a_thing2,
           d2.a_thing3,
           d3.b_thing1,
           d3.b_thing2,
           d3.b_thing3
    FROM   data2 d2,
           data3 d3
    WHERE  d2.group_id = d3.group_id
           AND d2.rw = d3.rw 

Answer 2

create table t (group_id, House_num , Apt    , code) as
select   45   , 1000      ,  1     ,  'M' from dual union all
select   45   ,    1      , null   ,  'D' from dual union all
select   45   , 1000      ,  2     ,  'M' from dual union all
select   45   ,    2      , null   ,  'D' from dual union all
select   87   , 2300      , 310    ,  'M' from dual union all
select   87   , 2310      , null   ,  'D' from dual union all
select   87   , 2400      , 470    ,  'M' from dual union all
select   87   , 2470      , null   ,  'D' from dual;

select rownum new_id,
  a.GROUP_ID, a.HOUSE_NUM ahn, a.APT aapt, a.CODE acode,
              b.HOUSE_NUM bhn, b.APT bapt, b.CODE bcode
from t a
join t b
on a.group_id = b.group_id
  and a.code = 'M'
  and b.code = 'D'
  and (
    b.house_num = a.apt
    or b.house_num like '%'||a.apt
  );

NEW_ID GROUP_ID AHN  AAPT ACODE BHN BAPT BCODE
1      45       1000 1    M     1        D
2      45       1000 2    M     2        D
3      87       2300 310  M     2310     D
4      87       2400 470  M     2470     D