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

Источник: ln

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

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

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

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

Импортирование документов Word в SQL Server.

Версия сервера, версия базы данных и уровни совместимости. Часть 1/4..

PHP скрипт скачивания файлов по временным ссылкам.

SQL Server: Управление транзакциями.

Способы подсчета количества записей в каждой из таблиц базы данных.

Главная » Sql