Запрос совпадающих и наиболее близких строк

Том! Я создаю отчет, в котором надо сравнивать записи из старой и новой систем. Однако нет гарантии, что для каждой записи из старой системы найдется точное совпадение. Если точно совпадающей строки нет, мне нужно выбирать "наиболее близкую" запись, в которой совпадают два заданных поля. Если такой записи нет, значит, соответствие не найдено. Ниже представлен упрощенный пример:

amathur> create table old_table(old_meas_id varchar2(20),
  2  field1 number(11),
  3  field2 number(11),
  4  field3 number(11));

Table created.

amathur> create table new_table(new_meas_id varchar2(20),
  2  field1 number(11),
  3  field2 number(11),
  4  field3 number(11));

Table created.

amathur> insert into old_table values ('A_OLD',1,2,3);

1 row created.

amathur> insert into old_table values ('B_OLD',4,5,6);

1 row created.

amathur> insert into old_table values ('C_OLD',7,8,9);

1 row created.

amathur> insert into new_table values ('A_NEW',1,2,3);

1 row created.

amathur> insert into new_table values ('B_NEW',4,5,6);

1 row created.

amathur> insert into new_table values ('C_NEW',7,8,10);

1 row created.

amathur> commit;

amathur> select * from old_table;

OLD_MEAS_ID              FIELD1     FIELD2     FIELD3
-------------------- ---------- ---------- ----------
A_OLD                         1          2          3
B_OLD                         4          5          6
C_OLD                         7          8          9

amathur> select * from new_table;

NEW_MEAS_ID              FIELD1     FIELD2     FIELD3
-------------------- ---------- ---------- ----------
A_NEW                         1          2          3
B_NEW                         4          5          6
C_NEW                         7          8         10

Если найдено точное совпадение (значения столбцов field1, field2 и field3 совпадают), надо выдать эти записи. Пусть "наиболее близкой" будет запись, в которой совпадают значения столбцов field1 и field2, но не field3 (как станет понятно далее, предполагается, что значение в этом столбце минимально отличается - Прим. В.К. ). Надо выдать также наиболее близкую запись.

Итак, на основе этих данных мой отчет должен выявить полное совпадение A_OLD с A_NEW и B_OLD с B_NEW. Запись C_OLD не совпадает в точности ни с одной из записей таблицы new_table, но можно найти достаточно близкую запись, поскольку столбцы field1 и field2 совпадают:

amathur> l
  1  select old.old_meas_id,new.new_meas_id
  2  from old_table old,new_table new
  3  where old.field1=new.field1
  4  and old.field2=new.field2
  5  and old.field3=new.field3
  6  or
  7  (old.field1=new.field1
  8  and old.field2=new.field2
  9  and (select count(*) from new_table
 10  where field1=old.field1
 11  and field2=old.field2
 12  and field3=old.field3)=0
 13* )
amathur> /

OLD_MEAS_ID          NEW_MEAS_ID
-------------------- ------------------
C_OLD                C_NEW
A_OLD                A_NEW
B_OLD                B_NEW

В моем случае, таблица "old_table" содержит данные объемом 2-3 Гбайта и несколько дополнительных столбцов, как и таблица "new_table". Я выполнил запрос к этим таблицам как описано выше. Через два дня выбрана была только половина строк. Анализ трассировочного файла с помощью TKPROF показал множество ожиданий ввода-вывода, работы с временным пространством и т.д., так что мне понятно, почему так происходит. Но, мне интересно, нельзя ли получить требуемый отчет другим запросом?

Ответ Тома Кайта

Проблема может быть связана с коррелированным подзапросом, да и "соединение с or" такого типа требует существенных ресурсов.

Если есть индекс по old_table(field1,field2), следующий запрос будет работать намного лучше - замечательный полный просмотр старой и новой таблицы, которые соединяются хешированием, а скалярный подзапрос выполняется только для "не совпадающих" строк, по индексу.

ops$tkyte@ORA9IR2> select nt.new_meas_id,
  2         nvl( ot.old_meas_id,
  3                       (select old_meas_id
  4                                  from old_table
  5                                     where old_table.field1 = nt.field1
  6                                       and old_table.field2 = nt.field2
  7                                           and rownum = 1 ) ) old_meas_id
  8   from new_table nt left join old_table ot on
  9         ( nt.field1 = ot.field1
 10               and
 11                   nt.field2 = ot.field2
 12                   and nt.field3 = ot.field3 );
 
NEW_MEAS_ID          OLD_MEAS_ID
-------------------- --------------------
A_NEW                A_OLD
B_NEW                B_OLD
C_NEW                C_OLD

Еще можно так:

ops$tkyte@ORA9IR2> insert into old_table values ('XXXXX',1,2,44);
 
1 row created.

ops$tkyte@ORA9IR2> select *
  2    from (
  3  select nt.new_meas_id, ot.old_meas_id,
  4         count(*) over (partition by nt.field1, nt.field2) cnt1,
  5         count(decode(nt.field3,ot.field3,1)) over (partition by nt.field1, nt.field2) cnt2,
  6             nt.field3 nt_f3,
  7             ot.field3 ot_f3
  8   from new_table nt left join old_table ot on
  9         ( nt.field1 = ot.field1
 10               and
 11                   nt.field2 = ot.field2  )
 12         )
 13   where cnt1 = 1
 14      or (cnt1 > 1 and ((cnt2 > 0 and nt_f3 = ot_f3) or (cnt2 = 0)))
 15  /
 
NEW_MEAS_ID          OLD_MEAS_ID                CNT1       CNT2      NT_F3      OT_F3
-------------------- -------------------- ---------- ---------- ---------- ----------
A_NEW                A_OLD                         2          1          3          3
B_NEW                B_OLD                         1          1          6          6
C_NEW                C_OLD                         1          0         10          9

Здесь мы соединяем по столбцам f1 и f2, и:

а) считаем, сколько (cnt) строк получается для этой пары f1/f2. Если cnt = 1, мы знаем, что эту строку надо выдать;
б) если cnt > 1 (мы получили частичные совпадения), мы оставляем строку, где f3 = f3 если cnt2 больше 1 (т.е. было точное совпадение), или оставляем все частичные совпадения, если cnt2 = 0 (т.е. есть много частичных совпадений, но нет ни одного точного)

nvl?

В первом методе для замыкания надо использовать nvl или, может, decode?

Ответ Тома Кайта

Отличное замечание

Используйте

decode( c, null, ( скалярный_подзапрос ), c )

вместо NVL, - или case:

case when c is null then ( скалярный_подзапрос ) else c end

Функция nvl не дает замыкания, она будет повторно выполнять скалярный подзапрос.

Расстояние...

Как насчет такого варианта использования второго подхода, на базе аналитических функций:

SQL> insert into new_table values ('NEW'  ,99,99,99);
SQL> insert into old_table values ('XXXXX',1 ,2 ,44);
SQL> l
  1  select y.*
  2    from (
  3  select x.*, min(distance) over (partition by nt_f1, nt_f2) min_dist
  4    from (
  5  select nt.new_meas_id, ot.old_meas_id,
  6         nt.field1 nt_f1, nt.field2 nt_f2, nt.field3 nt_f3,
  7         ot.field1 ot_f1, ot.field2 ot_f2, ot.field3 ot_f3,
  8         abs (nt.field3-ot.field3) distance
  9    from new_table nt left join old_table ot on
 10           ( nt.field1 = ot.field1
 11             and
 12             nt.field2 = ot.field2
 13           )
 14         ) x
 15         ) y
 16  where (min_dist = 0 and distance = 0) -- полное совпадение
 17     or min_dist is null -- нет совпадения
 18*    or min_dist > 0 -- частичное совпадение
SQL> /

NEW_M OLD_M NT_F1 NT_F2 NT_F3 OT_F1 OT_F2 OT_F3 DISTANCE MIN_DIST
----- ----- ----- ----- ----- ----- ----- ----- -------- --------
A_NEW A_OLD     1     2     3     1     2     3        0        0
B_NEW B_OLD     4     5     6     4     5     6        0        0
C_NEW C_OLD     7     8    10     7     8     9        1        1
NEW            99    99    99

К выражению "distance" ("расстоянию между строками") есть только следующие требования:

1) возвратить 0 для точного совпадения
2) возвратить > 0 для частичного совпадения
3) возвратить null, если совпадения нет

Так что, изменив всего одну строку, мы можем обрабатывать не числовые поля, задавать более сложные правила частичного совпадения и т.д.

Такой запрос можно также легко приспособить к требованию вида "выдать только три лучших совпадения" (с помощью аналитической функции dense_rank...).

Ответ Тома Кайта

Менее процедурное, более ориентированное на множества решение.

У нас тут есть реклама пива, с таким текстом:

"tastes great, less filling"

("Отличный вкус при меньшем объеме" - я бы так перевел. Прим. В.К. )

который описывает этот подход :) Мне он нравится. Хотя я и не тестировал его производительность, но идея кажется разумной.

А почему не сделать проще?

Поскольку вас не интересует совпадение по столбцу field3, (Конечно, если значения в столбце field3 совпадают - это лучше всего. Если же нет, вы ищете строки, в которых совпадают значения в столбцах field1 и field2). Поэтому простой запрос по столбцам feild1 и feild2 решит вашу проблему. Результат запроса, в любом случае, не позволит понять, какое соответствие - наилучшее, а какое - следующее по близости.

select old.old_meas_id,new.new_meas_id
  from old_table old,new_table new
  where old.field1=new.field1
        and old.field2=new.field2 

Ответ Тома Кайта

... потому что он не выдает "наилучшего соответствия"? Я думал, задача состоит именно в том, чтобы найтит наилучшее соотвествие.

Ссылки по теме

• Подробнее о продуктах Oracle
• Приобрести продукты Oracle в электронном магазине ITShop.ru
• Курсы обучения по продуктам компании Oracle