Запит співпадаючих і найбільш близьких рядків, Інші СУБД, Бази даних, статті

Том! Я створюю звіт, в якому треба порівнювати записи зі старої і нової систем. Однак немає гарантії, що для кожного запису зі старої системи знайдеться точний збіг. Якщо точно збігається рядка немає, мені потрібно вибирати “найбільш близьку” запис, в якій збігаються два заданих поля. Якщо такого запису немає, значить, відповідність не знайдено. Нижче представлений спрощений приклад:

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

Відповідь Тома Кайта


… тому що він не видає “найкращої відповідності”? Я думав, завдання полягає саме в тому, щоб знайти найкраще соотвествие.

Схожі статті:


Сподобалася стаття? Ви можете залишити відгук або підписатися на RSS , щоб автоматично отримувати інформацію про нові статтях.

Коментарів поки що немає.

Ваш отзыв

Поділ на параграфи відбувається автоматично, адреса електронної пошти ніколи не буде опублікований, допустимий HTML: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

*

*