Отправляет email-рассылки с помощью сервиса Sendsay

Открыто о СУБД Oracle на русском

Рассылка закрыта

При закрытии подписчики были переданы в рассылку "СУБД Oracle "с нуля"" на которую и рекомендуем вам подписаться.

Вы можете найти рассылки сходной тематики в Каталоге рассылок.

←   Август 2004   →
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
Автор
Сергей

Статистика

10.773 подписчиков
-8 за неделю
←   Все выпуски   →

Открыто о СУБД Oracle на русском : Запрос совпадающих и наиболее близких строк


Информационный Канал Subscribe.Ru

Выпуск 72

Уважаемые подписчики рассылки!

Я вернулся из отпуска, пару дней почитал новые обсуждения на сайте Тома Кайта и нашел небольшой, но интересный материал, перевод которого и предлагаю вашему вниманию. Опять про аналитические функции...

Кстати, теперь я снова буду стараться выпускать рассылку раз в неделю. Хотя бы до конца года. Спасибо всем, кто терпеливо этого ждал.

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

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

 
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

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

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

Оригинал обсуждения этого вопроса можно найти здесь.

Copyright © 2003 Oracle Corporation

Прежние выпуски

Все вышедшие выпуски рассылки можно найти на сайте рассылки. Там же реализована возможность поиска материалов по ключевым словам (с помощью Google...)

"Полезные ссылки"

Обнаружил недавно, что рекламирующаяся на обороте книги Тома Кайта "Oracle для профессионалов" (Книга 1) компания Softline.ru имеет какое-то отношение к Oracle... И проводит (бесплатные) семинары по Oracle, Linux и другим иногда интересующим меня темам. Не только в Москве, но и в Киеве. Я на такие мероприятия не хожу, но, может, кому и пригодится.

В следующих выпусках

Поскольку я все равно редко выпускаю то, что заранее обещал, ничего конкретного обещать не буду. Разве что сообщу, что, закончив заниматься RAID-массивами в ОС FreeBSD, я снова начинаю переводить главы книги "Mastering Oracle PL/SQL: Practical Solutions". И все еще собираюсь кое-что из переведенного публиковать.

С наилучшими пожеланиями,

  В.К.

http://subscribe.ru/
http://subscribe.ru/feedback/		 Адрес подписки
 Отписаться
В избранное