- Як позбутися від дублікатів в базі даних проблеми Для виконання всіх перерахованих вимог знадобиться...
- Як позбутися від дублікатів в базі даних
- Як позбутися від дублікатів в базі даних
- Як позбутися від дублікатів в базі даних
Як позбутися від дублікатів в базі даних
проблеми
Для виконання всіх перерахованих вимог знадобиться цілий комплекс заходів, однією зі складових якого буде забезпечення відсутності дублікатів.
дублювання атрибутів, що мають жорстко задану структуру (формат) змісту;
дублювання атрибутів, які не мають жорстко заданої структури (формату) змісту, т. е. слабоструктурованих.
У першому випадку мова йде про різні кодах по галузевим, муніципальним, федеральним і міжнародним довідників і класифікаторів, ідентифікатори сутностей, які використовуються в якості ключових атрибутів пошуку (номери документів, рахунків, телефонів, ПІН-коди і т. Д.). У другому випадку ми маємо справу з різноманітними іменами власними, використовуваними людьми для ідентифікації: прізвища, назви книг, фільмів, пісень, фірм і т. Д. Сюди ж відносяться адреси, що містять назви вулиць, номери будівель і т. П., Що зберігаються в одному атрибуті типу строкового поля «Адреса».
Проблема дублювання жорстко структурованих атрибутів вирішується обмеженням на введення даних у відповідні поля. Наприклад, можна дозволити введення тільки дозволених символів в заданому форматі або вибирати всі допустимі значення атрибута з довідника. Пошук дублікатів в цьому випадку ведеться за точним збігом і не викликає складнощів.
Ситуація зі слабоструктурованих полями трохи складніше, тому що неможливо використовувати обмеження формату (інакше це буде перший випадок) і часто не можна застосовувати словники-довідники, по-кільки їх необхідний обсяг може вийти за розумні межі і багаторазово перевищити розмір основної інформації АІС. Уявіть собі розмір географічного довідника адрес для невеликого відділу, що займається міжнародною розсилкою листів. Ще важче оцінити витрати на підтримку такого довідника в актуальному стані, вони можуть перевищити витрати на утримання всього відділу.
рішення
Для перевірки дублювання слабоструктурованої інформації нам допоможуть алгоритми нечіткого пошуку, що дозволяють знаходити дані на підставі неповного збіги і оцінки їх релевантності - кількісного критерію схожості. Слід враховувати, що дані алгоритми не дають 100% -ної гарантії від помилок, т. Е. Зберігається ймовірність того, що будуть пропущені дублюючі дані або, навпаки, дані будуть розпізнані як дублікати, не будучи такими. Тому для досягнення найкращого результату може бути необхідна участь людини.
Загальний принцип застосування алгоритмів для пошуку дублікатів наступний:
Проводиться обчислення деякого показника відповідності ( «схожості») двох символьних рядків, наприклад дистанції (відстані) або релевантності.
Даний показник наводиться до відносної шкалою відповідності в інтервалі від 0 до 1 (0 - повне неспівпадання, 1 - повний збіг). Надалі ця шкала легко може бути приведена до процентному вигляду, зручного для сприйняття людиною.
Експериментальним шляхом для тестового масиву даних визначається нижній поріг автоматичної обробки (Па), за яким кількість помилок розпізнавання дублікатів стає неприйнятним. Визначається також нижній поріг ручної обробки (Пр), за яким пошук видає практично одні помилки.
Па може бути використаний для подальшої уточнюючої обробки дублікатів в автоматичному режимі, залишаючи знайдені елементи зі значеннями відповід-наслідком нижче Па, але вище Пр для обробки людиною.
Основними шляхами внесення і зміни інформації в АІС є:
При введенні вручну потрібно забезпечити мінімальний час відгуку системи, так що використовуваний на цьому етапі алгоритм повинен працювати не стільки точно, скільки гранично швидко. При цьому параметр Па для даної операції може бути змінений відповідно до вимог по швидкості пошуку. Так як система розпізнавання не може надати 100% -ву точність, користувач повинен також мати можливість ігнорувати підказку системи і ввести дані. При таких умовах в базу даних неминуче буде потрапляти частина неякісної інформації, яка повинна бути виявлена в подальшому.
Таким чином, завдання виявлення і усунення дублікатів можна розбити на три етапи:
Виявлення дублікатів на рівні введення інформації користувачами та їх відхилення.
Виявлення дублікатів шляхом порівняння і аналізу вже введених даних відповідно до заданого Па і автоматичне видалення дублюючої інформації.
Аналіз і обробка людиною результатів попереднього етапу, які не можуть бути оброблені автоматично (показник відповідності нижче Па, але вище Пр).
можливі реалізації
В якості основи для реалізації системи розпізнавання був обраний метод N-gram, який забезпечує швидкий пошук на основі словника грамов (подстрок) і може бути використаний на всіх етапах. На першому кроці всі атрибути пошуку «склеюються» в один рядок. Наприклад, прізвище, ім'я та по батькові будуть оброблятися як один рядок ПІБ. Даний спосіб формування рядків для порівняння дає недостатньо точний, але швидкий результат при пошуку.
На другому етапі результати пошуку етапу 1 уточнюються, проходячи додаткову перевірку шляхом обчислення релевантності та відстаней вже для окремих атрибутів пошуку з урахуванням різних вагових коефіцієнтів, що підбираються експериментально. Наприклад, якщо два рядки з ПІБ збіглися з релевантностью 90%, але при порівнянні прізвищ отримана релевантність 60%, то, незважаючи на 100% -ве збіг імен та по батькові, можна відкинути результат першого етапу як помилковий.
Слід згадати про нестачу методу N-gram, який в деяких випадках може виявитися сущест-венним: великий розмір похідного безлічі подстрок (N-grams) щодо кількості вихідних рядків. Так, якщо N = 3, то для кожного рядка буде сформовано M = L - N + 1 словникових елементів, де L - довжина рядка, більше або дорівнює N (при L
Наприклад, якщо у вас є база даних на 50 тис. Абонентів, середня довжина ПІБ включає 50 символів, то потужність безлічі подстрок становитиме приблизно 50 000 * (50 - 3) = 2 350 000 елементів. При цьому кількість унікальних елементів, т. Е. Елементів словника, яке безпосередньо залежить від довжини підрядка N, буде істотно нижче (експериментально була отримана різниця приблизно на два порядки), і, отже, вам буде важко організувати швидкий пошук по такому безлічі, використовуючи стандартні засоби реляційної СУБД у вигляді індексів. Вибірковість (selectivity) такого індексу буде мала, що може привести до відмови від його використання оптимізатором запитів. Рішенням даної проблеми при великій кількості даних для словника є збільшення довжини N.
Як несуворого докази застосовності даного рішення можна розглянути функцію максимальної кількості елементів словника (унікальних елементів безлічі) в залежності від розмірів подстрок і алфавіту. Ця функція буде давати верхнє значення в припущенні, що символи алфавіту розміщені по N рівномірним розподілом по рядках. Чим менше рівномірність, тим менше буде кількість унікальних елементів. На практиці рівномірність при невеликих N далека від ідеальної, але вона збільшується зі зростанням N, і в граничному випадку, коли N дорівнює довжині рядка, при відсутності точних дублів рядків ми отримуємо повністю рівномірний розподіл. Нехай N - довжина підрядка, m - кількість символів в алфавіті, включаючи пробіл і знаки пунктуації. Тоді кількість можливих розміщень з повтореннями з m символів алфавіту по N обчислюється відповідно до відомої формули комбінаторики і одно mN. Це значення і буде найвищої і грубої оцінкою (нехтуємо ще й тим, що далеко не всі розміщення зустрічаються і допустимі в мові словника) кількості унікальних елементів словника. Таким чином, при збільшенні N кількість унікальних елементів зростає, як статечна функція.
Розглянемо більш докладно приклад реалізації системи розпізнавання дублікатів для списку фірм з використанням СУБД MS SQL 2000. Нехай фірма характеризується наступним набором атрибутів:
Для розпізнавання дублікатів на першому етапі ми будемо використовувати рядок, складений з усіх перерахованих атрибутів, загальна довжина якої може досягати 260 символів. Припустимо, що в базі даних є 20 000 записів із середньою довжиною рядка 150 символів. При таких параметрах вибираємо довжину подстроки N = 4. Таблиці для зберігання списку фірм і словника будуть виглядати так, як описано в лістингу 1.
Для початкового заповнення безлічі подстрок таблиці CompanyNGrams і подальшого її підтримки в узгодженому зі списком фірм стані (наприклад, використовуючи тригер) нам буде потрібно функція розбиття рядка на підрядка (грам). Ось приклад функції, яка повертає безліч подстрок (грамов) початкового рядка у вигляді таблиці:
Для початкового заповнення таблиці множе-ства подстрок CompanyNGrams можна скористатися наведеною вище функцією розбиття на грам наступним чином:
Для пошуку дубліката слід сформувати рядок для пошуку, розбити її на грам (підрядка) і обчислити релевантність по відношенню до інших рядках, що зберігаються в БД, які ми попередньо розділили на підрядка.
Релевантність (R) двох рядків S1 і S2 при довжині грама (підрядка) N обчислюється за такою формулою:
R = [NGramMatch (S1, S2) + NGramMatch (S2, S1)] / [NGramCount (S1) + NGramCount (S2)]
NGramCount (S) = [len (S) - N + 1],
де len (S) - довжина рядка S;
NGramMatch (S1, S2) - сума збігів всіх подстрок з S1 в рядку S2.
Тоді для пошуку схожих рядків можна використовувати наступну функцію, що складається всього з одного оператора SELECT:
приклади
Для реалізації підсистеми пошуку дублікатів в одній з CRM-систем (CRM - Customer Relationship Management - управління відносинами з клієнтами) була використана наступна схема (див. Малюнок).
Схема бази даних в системі була виконана відповідно до принципу об'єктно-реляційного підходу, описаного нами раніше в серії статей «Проектування ядра інформаційної системи» (див. «Мир ПК», №7 , 8 , 9 / 07).
Загальний для всіх класів предок Object відбивається на однойменну таблицю. Зв'язок з нащадками (узагальнення) та інші типи асоціацій між класами уніфіковані по ключу OID.
Службові таблиці CompanyNGrams і PersonNGrams зберігають відповідно подстроки-грам об'єктів класів «Компанія» і «Контактна особа». Таблиця ObjDuplicates завдяки уніфікованої зв'язку з OID зберігає зв'язку між потенційними дублікатами обох класів.
система розширювана . Для додавання нового типу пошуку дублікатів, наприклад, в географічний довідник або каталог товарів вам буде потрібно тільки внести в систему таблицю зберігання грамов і методи обчислення релевантності даного класу. Реалізація за допомогою збережених процедур і функцій заданої сигнатури дозволяє «включити» клас в підсистему нечіткого пошуку без додаткових витрат.
Зрозуміло, за універсальність потрібно платити. Наприклад, необхідно писати код перевірки семантики класів об'єктів, що додаються в таблицю дублікатів.
Розглянута в прикладі з фірмами і контактними особами підсистема буде функціонувати в наступному режимі. На першому етапі, при грубому порівнянні без урахування семантики такі записи будуть розпізнані як дублікати:
Однак і такі ось записи теж потраплять в число «підозрілих»:
Але на другому етапі при аналізі з урахуванням семантики серед «підозрілих» записи з другого прикладу будуть відбраковані внаслідок низької релевантності назв компаній та прізвищ.
Корисні ресурси
Як позбутися від дублікатів в базі даних
проблеми
Для виконання всіх перерахованих вимог знадобиться цілий комплекс заходів, однією зі складових якого буде забезпечення відсутності дублікатів.
У будь-якій АІС можна виділити два основних типи дублювання інформації:
дублювання атрибутів, що мають жорстко задану структуру (формат) змісту;
дублювання атрибутів, які не мають жорстко заданої структури (формату) змісту, т. е. слабоструктурованих.
У першому випадку мова йде про різні кодах по галузевим, муніципальним, федеральним і міжнародним довідників і класифікаторів, ідентифікатори сутностей, які використовуються в якості ключових атрибутів пошуку (номери документів, рахунків, телефонів, ПІН-коди і т. Д.). У другому випадку ми маємо справу з різноманітними іменами власними, використовуваними людьми для ідентифікації: прізвища, назви книг, фільмів, пісень, фірм і т. Д. Сюди ж відносяться адреси, що містять назви вулиць, номери будівель і т. П., Що зберігаються в одному атрибуті типу строкового поля «Адреса».
Проблема дублювання жорстко структурованих атрибутів вирішується обмеженням на введення даних у відповідні поля. Наприклад, можна дозволити введення тільки дозволених символів в заданому форматі або вибирати всі допустимі значення атрибута з довідника. Пошук дублікатів в цьому випадку ведеться за точним збігом і не викликає складнощів.
Ситуація зі слабоструктурованих полями трохи складніше, тому що неможливо використовувати обмеження формату (інакше це буде перший випадок) і часто не можна застосовувати словники-довідники, по-кільки їх необхідний обсяг може вийти за розумні межі і багаторазово перевищити розмір основної інформації АІС. Уявіть собі розмір географічного довідника адрес для невеликого відділу, що займається міжнародною розсилкою листів. Ще важче оцінити витрати на підтримку такого довідника в актуальному стані, вони можуть перевищити витрати на утримання всього відділу.
рішення
Для перевірки дублювання слабоструктурованої інформації нам допоможуть алгоритми нечіткого пошуку, що дозволяють знаходити дані на підставі неповного збіги і оцінки їх релевантності - кількісного критерію схожості. Слід враховувати, що дані алгоритми не дають 100% -ної гарантії від помилок, т. Е. Зберігається ймовірність того, що будуть пропущені дублюючі дані або, навпаки, дані будуть розпізнані як дублікати, не будучи такими. Тому для досягнення найкращого результату може бути необхідна участь людини.
Загальний принцип застосування алгоритмів для пошуку дублікатів наступний:
Проводиться обчислення деякого показника відповідності ( «схожості») двох символьних рядків, наприклад дистанції (відстані) або релевантності.
Даний показник наводиться до відносної шкалою відповідності в інтервалі від 0 до 1 (0 - повне неспівпадання, 1 - повний збіг). Надалі ця шкала легко може бути приведена до процентному вигляду, зручного для сприйняття людиною.
Експериментальним шляхом для тестового масиву даних визначається нижній поріг автоматичної обробки (Па), за яким кількість помилок розпізнавання дублікатів стає неприйнятним. Визначається також нижній поріг ручної обробки (Пр), за яким пошук видає практично одні помилки.
Па може бути використаний для подальшої уточнюючої обробки дублікатів в автоматичному режимі, залишаючи знайдені елементи зі значеннями відповід-наслідком нижче Па, але вище Пр для обробки людиною.
Основними шляхами внесення і зміни інформації в АІС є:
При введенні вручну потрібно забезпечити мінімальний час відгуку системи, так що використовуваний на цьому етапі алгоритм повинен працювати не стільки точно, скільки гранично швидко. При цьому параметр Па для даної операції може бути змінений відповідно до вимог по швидкості пошуку. Так як система розпізнавання не може надати 100% -ву точність, користувач повинен також мати можливість ігнорувати підказку системи і ввести дані. При таких умовах в базу даних неминуче буде потрапляти частина неякісної інформації, яка повинна бути виявлена в подальшому.
Таким чином, завдання виявлення і усунення дублікатів можна розбити на три етапи:
Виявлення дублікатів на рівні введення інформації користувачами та їх відхилення.
Виявлення дублікатів шляхом порівняння і аналізу вже введених даних відповідно до заданого Па і автоматичне видалення дублюючої інформації.
Аналіз і обробка людиною результатів попереднього етапу, які не можуть бути оброблені автоматично (показник відповідності нижче Па, але вище Пр).
можливі реалізації
В якості основи для реалізації системи розпізнавання був обраний метод N-gram, який забезпечує швидкий пошук на основі словника грамов (подстрок) і може бути використаний на всіх етапах. На першому кроці всі атрибути пошуку «склеюються» в один рядок. Наприклад, прізвище, ім'я та по батькові будуть оброблятися як один рядок ПІБ. Даний спосіб формування рядків для порівняння дає недостатньо точний, але швидкий результат при пошуку.
На другому етапі результати пошуку етапу 1 уточнюються, проходячи додаткову перевірку шляхом обчислення релевантності та відстаней вже для окремих атрибутів пошуку з урахуванням різних вагових коефіцієнтів, що підбираються експериментально. Наприклад, якщо два рядки з ПІБ збіглися з релевантностью 90%, але при порівнянні прізвищ отримана релевантність 60%, то, незважаючи на 100% -ве збіг імен та по батькові, можна відкинути результат першого етапу як помилковий.
Слід згадати про нестачу методу N-gram, який в деяких випадках може виявитися сущест-венним: великий розмір похідного безлічі подстрок (N-grams) щодо кількості вихідних рядків. Так, якщо N = 3, то для кожного рядка буде сформовано M = L - N + 1 словникових елементів, де L - довжина рядка, більше або дорівнює N (при L
Наприклад, якщо у вас є база даних на 50 тис. Абонентів, середня довжина ПІБ включає 50 символів, то потужність безлічі подстрок становитиме приблизно 50 000 * (50 - 3) = 2 350 000 елементів. При цьому кількість унікальних елементів, т. Е. Елементів словника, яке безпосередньо залежить від довжини підрядка N, буде істотно нижче (експериментально була отримана різниця приблизно на два порядки), і, отже, вам буде важко організувати швидкий пошук по такому безлічі, використовуючи стандартні засоби реляційної СУБД у вигляді індексів. Вибірковість (selectivity) такого індексу буде мала, що може привести до відмови від його використання оптимізатором запитів. Рішенням даної проблеми при великій кількості даних для словника є збільшення довжини N.
Як несуворого докази застосовності даного рішення можна розглянути функцію максимальної кількості елементів словника (унікальних елементів безлічі) в залежності від розмірів подстрок і алфавіту. Ця функція буде давати верхнє значення в припущенні, що символи алфавіту розміщені по N рівномірним розподілом по рядках. Чим менше рівномірність, тим менше буде кількість унікальних елементів. На практиці рівномірність при невеликих N далека від ідеальної, але вона збільшується зі зростанням N, і в граничному випадку, коли N дорівнює довжині рядка, при відсутності точних дублів рядків ми отримуємо повністю рівномірний розподіл. Нехай N - довжина підрядка, m - кількість символів в алфавіті, включаючи пробіл і знаки пунктуації. Тоді кількість можливих розміщень з повтореннями з m символів алфавіту по N обчислюється відповідно до відомої формули комбінаторики і одно mN. Це значення і буде найвищої і грубої оцінкою (нехтуємо ще й тим, що далеко не всі розміщення зустрічаються і допустимі в мові словника) кількості унікальних елементів словника. Таким чином, при збільшенні N кількість унікальних елементів зростає, як статечна функція.
Розглянемо більш докладно приклад реалізації системи розпізнавання дублікатів для списку фірм з використанням СУБД MS SQL 2000. Нехай фірма характеризується наступним набором атрибутів:
Для розпізнавання дублікатів на першому етапі ми будемо використовувати рядок, складений з усіх перерахованих атрибутів, загальна довжина якої може досягати 260 символів. Припустимо, що в базі даних є 20 000 записів із середньою довжиною рядка 150 символів. При таких параметрах вибираємо довжину подстроки N = 4. Таблиці для зберігання списку фірм і словника будуть виглядати так, як описано в лістингу 1.
Для початкового заповнення безлічі подстрок таблиці CompanyNGrams і подальшого її підтримки в узгодженому зі списком фірм стані (наприклад, використовуючи тригер) нам буде потрібно функція розбиття рядка на підрядка (грам). Ось приклад функції, яка повертає безліч подстрок (грамов) початкового рядка у вигляді таблиці:
Для початкового заповнення таблиці множе-ства подстрок CompanyNGrams можна скористатися наведеною вище функцією розбиття на грам наступним чином:
Для пошуку дубліката слід сформувати рядок для пошуку, розбити її на грам (підрядка) і обчислити релевантність по відношенню до інших рядках, що зберігаються в БД, які ми попередньо розділили на підрядка.
Релевантність (R) двох рядків S1 і S2 при довжині грама (підрядка) N обчислюється за такою формулою:
R = [NGramMatch (S1, S2) + NGramMatch (S2, S1)] / [NGramCount (S1) + NGramCount (S2)]
NGramCount (S) = [len (S) - N + 1],
де len (S) - довжина рядка S;
NGramMatch (S1, S2) - сума збігів всіх подстрок з S1 в рядку S2.
Тоді для пошуку схожих рядків можна використовувати наступну функцію, що складається всього з одного оператора SELECT:
приклади
Для реалізації підсистеми пошуку дублікатів в одній з CRM-систем (CRM - Customer Relationship Management - управління відносинами з клієнтами) була використана наступна схема (див. Малюнок).
Схема бази даних в системі була виконана відповідно до принципу об'єктно-реляційного підходу, описаного нами раніше в серії статей «Проектування ядра інформаційної системи» (див. «Мир ПК», №7 , 8 , 9 / 07).
Загальний для всіх класів предок Object відбивається на однойменну таблицю. Зв'язок з нащадками (узагальнення) та інші типи асоціацій між класами уніфіковані по ключу OID.
Службові таблиці CompanyNGrams і PersonNGrams зберігають відповідно подстроки-грам об'єктів класів «Компанія» і «Контактна особа». Таблиця ObjDuplicates завдяки уніфікованої зв'язку з OID зберігає зв'язку між потенційними дублікатами обох класів.
система розширювана . Для додавання нового типу пошуку дублікатів, наприклад, в географічний довідник або каталог товарів вам буде потрібно тільки внести в систему таблицю зберігання грамов і методи обчислення релевантності даного класу. Реалізація за допомогою збережених процедур і функцій заданої сигнатури дозволяє «включити» клас в підсистему нечіткого пошуку без додаткових витрат.
Зрозуміло, за універсальність потрібно платити. Наприклад, необхідно писати код перевірки семантики класів об'єктів, що додаються в таблицю дублікатів.
Розглянута в прикладі з фірмами і контактними особами підсистема буде функціонувати в наступному режимі. На першому етапі, при грубому порівнянні без урахування семантики такі записи будуть розпізнані як дублікати:
Однак і такі ось записи теж потраплять в число «підозрілих»:
Але на другому етапі при аналізі з урахуванням семантики серед «підозрілих» записи з другого прикладу будуть відбраковані внаслідок низької релевантності назв компаній та прізвищ.
Корисні ресурси
Як позбутися від дублікатів в базі даних
проблеми
Для виконання всіх перерахованих вимог знадобиться цілий комплекс заходів, однією зі складових якого буде забезпечення відсутності дублікатів.
У будь-якій АІС можна виділити два основних типи дублювання інформації:
дублювання атрибутів, що мають жорстко задану структуру (формат) змісту;
дублювання атрибутів, які не мають жорстко заданої структури (формату) змісту, т. е. слабоструктурованих.
У першому випадку мова йде про різні кодах по галузевим, муніципальним, федеральним і міжнародним довідників і класифікаторів, ідентифікатори сутностей, які використовуються в якості ключових атрибутів пошуку (номери документів, рахунків, телефонів, ПІН-коди і т. Д.). У другому випадку ми маємо справу з різноманітними іменами власними, використовуваними людьми для ідентифікації: прізвища, назви книг, фільмів, пісень, фірм і т. Д. Сюди ж відносяться адреси, що містять назви вулиць, номери будівель і т. П., Що зберігаються в одному атрибуті типу строкового поля «Адреса».
Проблема дублювання жорстко структурованих атрибутів вирішується обмеженням на введення даних у відповідні поля. Наприклад, можна дозволити введення тільки дозволених символів в заданому форматі або вибирати всі допустимі значення атрибута з довідника. Пошук дублікатів в цьому випадку ведеться за точним збігом і не викликає складнощів.
Ситуація зі слабоструктурованих полями трохи складніше, тому що неможливо використовувати обмеження формату (інакше це буде перший випадок) і часто не можна застосовувати словники-довідники, по-кільки їх необхідний обсяг може вийти за розумні межі і багаторазово перевищити розмір основної інформації АІС. Уявіть собі розмір географічного довідника адрес для невеликого відділу, що займається міжнародною розсилкою листів. Ще важче оцінити витрати на підтримку такого довідника в актуальному стані, вони можуть перевищити витрати на утримання всього відділу.
рішення
Для перевірки дублювання слабоструктурованої інформації нам допоможуть алгоритми нечіткого пошуку, що дозволяють знаходити дані на підставі неповного збіги і оцінки їх релевантності - кількісного критерію схожості. Слід враховувати, що дані алгоритми не дають 100% -ної гарантії від помилок, т. Е. Зберігається ймовірність того, що будуть пропущені дублюючі дані або, навпаки, дані будуть розпізнані як дублікати, не будучи такими. Тому для досягнення найкращого результату може бути необхідна участь людини.
Загальний принцип застосування алгоритмів для пошуку дублікатів наступний:
Проводиться обчислення деякого показника відповідності ( «схожості») двох символьних рядків, наприклад дистанції (відстані) або релевантності.
Даний показник наводиться до відносної шкалою відповідності в інтервалі від 0 до 1 (0 - повне неспівпадання, 1 - повний збіг). Надалі ця шкала легко може бути приведена до процентному вигляду, зручного для сприйняття людиною.
Експериментальним шляхом для тестового масиву даних визначається нижній поріг автоматичної обробки (Па), за яким кількість помилок розпізнавання дублікатів стає неприйнятним. Визначається також нижній поріг ручної обробки (Пр), за яким пошук видає практично одні помилки.
Па може бути використаний для подальшої уточнюючої обробки дублікатів в автоматичному режимі, залишаючи знайдені елементи зі значеннями відповід-наслідком нижче Па, але вище Пр для обробки людиною.
Основними шляхами внесення і зміни інформації в АІС є:
При введенні вручну потрібно забезпечити мінімальний час відгуку системи, так що використовуваний на цьому етапі алгоритм повинен працювати не стільки точно, скільки гранично швидко. При цьому параметр Па для даної операції може бути змінений відповідно до вимог по швидкості пошуку. Так як система розпізнавання не може надати 100% -ву точність, користувач повинен також мати можливість ігнорувати підказку системи і ввести дані. При таких умовах в базу даних неминуче буде потрапляти частина неякісної інформації, яка повинна бути виявлена в подальшому.
Таким чином, завдання виявлення і усунення дублікатів можна розбити на три етапи:
Виявлення дублікатів на рівні введення інформації користувачами та їх відхилення.
Виявлення дублікатів шляхом порівняння і аналізу вже введених даних відповідно до заданого Па і автоматичне видалення дублюючої інформації.
Аналіз і обробка людиною результатів попереднього етапу, які не можуть бути оброблені автоматично (показник відповідності нижче Па, але вище Пр).
можливі реалізації
В якості основи для реалізації системи розпізнавання був обраний метод N-gram, який забезпечує швидкий пошук на основі словника грамов (подстрок) і може бути використаний на всіх етапах. На першому кроці всі атрибути пошуку «склеюються» в один рядок. Наприклад, прізвище, ім'я та по батькові будуть оброблятися як один рядок ПІБ. Даний спосіб формування рядків для порівняння дає недостатньо точний, але швидкий результат при пошуку.
На другому етапі результати пошуку етапу 1 уточнюються, проходячи додаткову перевірку шляхом обчислення релевантності та відстаней вже для окремих атрибутів пошуку з урахуванням різних вагових коефіцієнтів, що підбираються експериментально. Наприклад, якщо два рядки з ПІБ збіглися з релевантностью 90%, але при порівнянні прізвищ отримана релевантність 60%, то, незважаючи на 100% -ве збіг імен та по батькові, можна відкинути результат першого етапу як помилковий.
Слід згадати про нестачу методу N-gram, який в деяких випадках може виявитися сущест-венним: великий розмір похідного безлічі подстрок (N-grams) щодо кількості вихідних рядків. Так, якщо N = 3, то для кожного рядка буде сформовано M = L - N + 1 словникових елементів, де L - довжина рядка, більше або дорівнює N (при L
Наприклад, якщо у вас є база даних на 50 тис. Абонентів, середня довжина ПІБ включає 50 символів, то потужність безлічі подстрок становитиме приблизно 50 000 * (50 - 3) = 2 350 000 елементів. При цьому кількість унікальних елементів, т. Е. Елементів словника, яке безпосередньо залежить від довжини підрядка N, буде істотно нижче (експериментально була отримана різниця приблизно на два порядки), і, отже, вам буде важко організувати швидкий пошук по такому безлічі, використовуючи стандартні засоби реляційної СУБД у вигляді індексів. Вибірковість (selectivity) такого індексу буде мала, що може привести до відмови від його використання оптимізатором запитів. Рішенням даної проблеми при великій кількості даних для словника є збільшення довжини N.
Як несуворого докази застосовності даного рішення можна розглянути функцію максимальної кількості елементів словника (унікальних елементів безлічі) в залежності від розмірів подстрок і алфавіту. Ця функція буде давати верхнє значення в припущенні, що символи алфавіту розміщені по N рівномірним розподілом по рядках. Чим менше рівномірність, тим менше буде кількість унікальних елементів. На практиці рівномірність при невеликих N далека від ідеальної, але вона збільшується зі зростанням N, і в граничному випадку, коли N дорівнює довжині рядка, при відсутності точних дублів рядків ми отримуємо повністю рівномірний розподіл. Нехай N - довжина підрядка, m - кількість символів в алфавіті, включаючи пробіл і знаки пунктуації. Тоді кількість можливих розміщень з повтореннями з m символів алфавіту по N обчислюється відповідно до відомої формули комбінаторики і одно mN. Це значення і буде найвищої і грубої оцінкою (нехтуємо ще й тим, що далеко не всі розміщення зустрічаються і допустимі в мові словника) кількості унікальних елементів словника. Таким чином, при збільшенні N кількість унікальних елементів зростає, як статечна функція.
Розглянемо більш докладно приклад реалізації системи розпізнавання дублікатів для списку фірм з використанням СУБД MS SQL 2000. Нехай фірма характеризується наступним набором атрибутів:
Для розпізнавання дублікатів на першому етапі ми будемо використовувати рядок, складений з усіх перерахованих атрибутів, загальна довжина якої може досягати 260 символів. Припустимо, що в базі даних є 20 000 записів із середньою довжиною рядка 150 символів. При таких параметрах вибираємо довжину подстроки N = 4. Таблиці для зберігання списку фірм і словника будуть виглядати так, як описано в лістингу 1.
Для початкового заповнення безлічі подстрок таблиці CompanyNGrams і подальшого її підтримки в узгодженому зі списком фірм стані (наприклад, використовуючи тригер) нам буде потрібно функція розбиття рядка на підрядка (грам). Ось приклад функції, яка повертає безліч подстрок (грамов) початкового рядка у вигляді таблиці:
Для початкового заповнення таблиці множе-ства подстрок CompanyNGrams можна скористатися наведеною вище функцією розбиття на грам наступним чином:
Для пошуку дубліката слід сформувати рядок для пошуку, розбити її на грам (підрядка) і обчислити релевантність по відношенню до інших рядках, що зберігаються в БД, які ми попередньо розділили на підрядка.
Релевантність (R) двох рядків S1 і S2 при довжині грама (підрядка) N обчислюється за такою формулою:
R = [NGramMatch (S1, S2) + NGramMatch (S2, S1)] / [NGramCount (S1) + NGramCount (S2)]
NGramCount (S) = [len (S) - N + 1],
де len (S) - довжина рядка S;
NGramMatch (S1, S2) - сума збігів всіх подстрок з S1 в рядку S2.
Тоді для пошуку схожих рядків можна використовувати наступну функцію, що складається всього з одного оператора SELECT:
приклади
Для реалізації підсистеми пошуку дублікатів в одній з CRM-систем (CRM - Customer Relationship Management - управління відносинами з клієнтами) була використана наступна схема (див. Малюнок).
Схема бази даних в системі була виконана відповідно до принципу об'єктно-реляційного підходу, описаного нами раніше в серії статей «Проектування ядра інформаційної системи» (див. «Мир ПК», №7 , 8 , 9 / 07).
Загальний для всіх класів предок Object відбивається на однойменну таблицю. Зв'язок з нащадками (узагальнення) та інші типи асоціацій між класами уніфіковані по ключу OID.
Службові таблиці CompanyNGrams і PersonNGrams зберігають відповідно подстроки-грам об'єктів класів «Компанія» і «Контактна особа». Таблиця ObjDuplicates завдяки уніфікованої зв'язку з OID зберігає зв'язку між потенційними дублікатами обох класів.
система розширювана . Для додавання нового типу пошуку дублікатів, наприклад, в географічний довідник або каталог товарів вам буде потрібно тільки внести в систему таблицю зберігання грамов і методи обчислення релевантності даного класу. Реалізація за допомогою збережених процедур і функцій заданої сигнатури дозволяє «включити» клас в підсистему нечіткого пошуку без додаткових витрат.
Зрозуміло, за універсальність потрібно платити. Наприклад, необхідно писати код перевірки семантики класів об'єктів, що додаються в таблицю дублікатів.
Розглянута в прикладі з фірмами і контактними особами підсистема буде функціонувати в наступному режимі. На першому етапі, при грубому порівнянні без урахування семантики такі записи будуть розпізнані як дублікати:
Однак і такі ось записи теж потраплять в число «підозрілих»:
Але на другому етапі при аналізі з урахуванням семантики серед «підозрілих» записи з другого прикладу будуть відбраковані внаслідок низької релевантності назв компаній та прізвищ.
Корисні ресурси
Як позбутися від дублікатів в базі даних
проблеми
Для виконання всіх перерахованих вимог знадобиться цілий комплекс заходів, однією зі складових якого буде забезпечення відсутності дублікатів.
У будь-якій АІС можна виділити два основних типи дублювання інформації:
дублювання атрибутів, що мають жорстко задану структуру (формат) змісту;
дублювання атрибутів, які не мають жорстко заданої структури (формату) змісту, т. е. слабоструктурованих.
У першому випадку мова йде про різні кодах по галузевим, муніципальним, федеральним і міжнародним довідників і класифікаторів, ідентифікатори сутностей, які використовуються в якості ключових атрибутів пошуку (номери документів, рахунків, телефонів, ПІН-коди і т. Д.). У другому випадку ми маємо справу з різноманітними іменами власними, використовуваними людьми для ідентифікації: прізвища, назви книг, фільмів, пісень, фірм і т. Д. Сюди ж відносяться адреси, що містять назви вулиць, номери будівель і т. П., Що зберігаються в одному атрибуті типу строкового поля «Адреса».
Проблема дублювання жорстко структурованих атрибутів вирішується обмеженням на введення даних у відповідні поля. Наприклад, можна дозволити введення тільки дозволених символів в заданому форматі або вибирати всі допустимі значення атрибута з довідника. Пошук дублікатів в цьому випадку ведеться за точним збігом і не викликає складнощів.
Ситуація зі слабоструктурованих полями трохи складніше, тому що неможливо використовувати обмеження формату (інакше це буде перший випадок) і часто не можна застосовувати словники-довідники, по-кільки їх необхідний обсяг може вийти за розумні межі і багаторазово перевищити розмір основної інформації АІС. Уявіть собі розмір географічного довідника адрес для невеликого відділу, що займається міжнародною розсилкою листів. Ще важче оцінити витрати на підтримку такого довідника в актуальному стані, вони можуть перевищити витрати на утримання всього відділу.
рішення
Для перевірки дублювання слабоструктурованої інформації нам допоможуть алгоритми нечіткого пошуку, що дозволяють знаходити дані на підставі неповного збіги і оцінки їх релевантності - кількісного критерію схожості. Слід враховувати, що дані алгоритми не дають 100% -ної гарантії від помилок, т. Е. Зберігається ймовірність того, що будуть пропущені дублюючі дані або, навпаки, дані будуть розпізнані як дублікати, не будучи такими. Тому для досягнення найкращого результату може бути необхідна участь людини.
Загальний принцип застосування алгоритмів для пошуку дублікатів наступний:
Проводиться обчислення деякого показника відповідності ( «схожості») двох символьних рядків, наприклад дистанції (відстані) або релевантності.
Даний показник наводиться до відносної шкалою відповідності в інтервалі від 0 до 1 (0 - повне неспівпадання, 1 - повний збіг). Надалі ця шкала легко може бути приведена до процентному вигляду, зручного для сприйняття людиною.
Експериментальним шляхом для тестового масиву даних визначається нижній поріг автоматичної обробки (Па), за яким кількість помилок розпізнавання дублікатів стає неприйнятним. Визначається також нижній поріг ручної обробки (Пр), за яким пошук видає практично одні помилки.
Па може бути використаний для подальшої уточнюючої обробки дублікатів в автоматичному режимі, залишаючи знайдені елементи зі значеннями відповід-наслідком нижче Па, але вище Пр для обробки людиною.
Основними шляхами внесення і зміни інформації в АІС є:
При введенні вручну потрібно забезпечити мінімальний час відгуку системи, так що використовуваний на цьому етапі алгоритм повинен працювати не стільки точно, скільки гранично швидко. При цьому параметр Па для даної операції може бути змінений відповідно до вимог по швидкості пошуку. Так як система розпізнавання не може надати 100% -ву точність, користувач повинен також мати можливість ігнорувати підказку системи і ввести дані. При таких умовах в базу даних неминуче буде потрапляти частина неякісної інформації, яка повинна бути виявлена в подальшому.
Таким чином, завдання виявлення і усунення дублікатів можна розбити на три етапи:
Виявлення дублікатів на рівні введення інформації користувачами та їх відхилення.
Виявлення дублікатів шляхом порівняння і аналізу вже введених даних відповідно до заданого Па і автоматичне видалення дублюючої інформації.
Аналіз і обробка людиною результатів попереднього етапу, які не можуть бути оброблені автоматично (показник відповідності нижче Па, але вище Пр).
можливі реалізації
В якості основи для реалізації системи розпізнавання був обраний метод N-gram, який забезпечує швидкий пошук на основі словника грамов (подстрок) і може бути використаний на всіх етапах. На першому кроці всі атрибути пошуку «склеюються» в один рядок. Наприклад, прізвище, ім'я та по батькові будуть оброблятися як один рядок ПІБ. Даний спосіб формування рядків для порівняння дає недостатньо точний, але швидкий результат при пошуку.
На другому етапі результати пошуку етапу 1 уточнюються, проходячи додаткову перевірку шляхом обчислення релевантності та відстаней вже для окремих атрибутів пошуку з урахуванням різних вагових коефіцієнтів, що підбираються експериментально. Наприклад, якщо два рядки з ПІБ збіглися з релевантностью 90%, але при порівнянні прізвищ отримана релевантність 60%, то, незважаючи на 100% -ве збіг імен та по батькові, можна відкинути результат першого етапу як помилковий.
Слід згадати про нестачу методу N-gram, який в деяких випадках може виявитися сущест-венним: великий розмір похідного безлічі подстрок (N-grams) щодо кількості вихідних рядків. Так, якщо N = 3, то для кожного рядка буде сформовано M = L - N + 1 словникових елементів, де L - довжина рядка, більше або дорівнює N (при L
Наприклад, якщо у вас є база даних на 50 тис. Абонентів, середня довжина ПІБ включає 50 символів, то потужність безлічі подстрок становитиме приблизно 50 000 * (50 - 3) = 2 350 000 елементів. При цьому кількість унікальних елементів, т. Е. Елементів словника, яке безпосередньо залежить від довжини підрядка N, буде істотно нижче (експериментально була отримана різниця приблизно на два порядки), і, отже, вам буде важко організувати швидкий пошук по такому безлічі, використовуючи стандартні засоби реляційної СУБД у вигляді індексів. Вибірковість (selectivity) такого індексу буде мала, що може привести до відмови від його використання оптимізатором запитів. Рішенням даної проблеми при великій кількості даних для словника є збільшення довжини N.
Як несуворого докази застосовності даного рішення можна розглянути функцію максимальної кількості елементів словника (унікальних елементів безлічі) в залежності від розмірів подстрок і алфавіту. Ця функція буде давати верхнє значення в припущенні, що символи алфавіту розміщені по N рівномірним розподілом по рядках. Чим менше рівномірність, тим менше буде кількість унікальних елементів. На практиці рівномірність при невеликих N далека від ідеальної, але вона збільшується зі зростанням N, і в граничному випадку, коли N дорівнює довжині рядка, при відсутності точних дублів рядків ми отримуємо повністю рівномірний розподіл. Нехай N - довжина підрядка, m - кількість символів в алфавіті, включаючи пробіл і знаки пунктуації. Тоді кількість можливих розміщень з повтореннями з m символів алфавіту по N обчислюється відповідно до відомої формули комбінаторики і одно mN. Це значення і буде найвищої і грубої оцінкою (нехтуємо ще й тим, що далеко не всі розміщення зустрічаються і допустимі в мові словника) кількості унікальних елементів словника. Таким чином, при збільшенні N кількість унікальних елементів зростає, як статечна функція.
Розглянемо більш докладно приклад реалізації системи розпізнавання дублікатів для списку фірм з використанням СУБД MS SQL 2000. Нехай фірма характеризується наступним набором атрибутів:
Для розпізнавання дублікатів на першому етапі ми будемо використовувати рядок, складений з усіх перерахованих атрибутів, загальна довжина якої може досягати 260 символів. Припустимо, що в базі даних є 20 000 записів із середньою довжиною рядка 150 символів. При таких параметрах вибираємо довжину подстроки N = 4. Таблиці для зберігання списку фірм і словника будуть виглядати так, як описано в лістингу 1.
Для початкового заповнення безлічі подстрок таблиці CompanyNGrams і подальшого її підтримки в узгодженому зі списком фірм стані (наприклад, використовуючи тригер) нам буде потрібно функція розбиття рядка на підрядка (грам). Ось приклад функції, яка повертає безліч подстрок (грамов) початкового рядка у вигляді таблиці:
Для початкового заповнення таблиці множе-ства подстрок CompanyNGrams можна скористатися наведеною вище функцією розбиття на грам наступним чином:
Для пошуку дубліката слід сформувати рядок для пошуку, розбити її на грам (підрядка) і обчислити релевантність по відношенню до інших рядках, що зберігаються в БД, які ми попередньо розділили на підрядка.
Релевантність (R) двох рядків S1 і S2 при довжині грама (підрядка) N обчислюється за такою формулою:
R = [NGramMatch (S1, S2) + NGramMatch (S2, S1)] / [NGramCount (S1) + NGramCount (S2)]
NGramCount (S) = [len (S) - N + 1],
де len (S) - довжина рядка S;
NGramMatch (S1, S2) - сума збігів всіх подстрок з S1 в рядку S2.
Тоді для пошуку схожих рядків можна використовувати наступну функцію, що складається всього з одного оператора SELECT:
приклади
Для реалізації підсистеми пошуку дублікатів в одній з CRM-систем (CRM - Customer Relationship Management - управління відносинами з клієнтами) була використана наступна схема (див. Малюнок).
Схема бази даних в системі була виконана відповідно до принципу об'єктно-реляційного підходу, описаного нами раніше в серії статей «Проектування ядра інформаційної системи» (див. «Мир ПК», №7 , 8 , 9 / 07).
Загальний для всіх класів предок Object відбивається на однойменну таблицю. Зв'язок з нащадками (узагальнення) та інші типи асоціацій між класами уніфіковані по ключу OID.
Службові таблиці CompanyNGrams і PersonNGrams зберігають відповідно подстроки-грам об'єктів класів «Компанія» і «Контактна особа». Таблиця ObjDuplicates завдяки уніфікованої зв'язку з OID зберігає зв'язку між потенційними дублікатами обох класів.
система розширювана . Для додавання нового типу пошуку дублікатів, наприклад, в географічний довідник або каталог товарів вам буде потрібно тільки внести в систему таблицю зберігання грамов і методи обчислення релевантності даного класу. Реалізація за допомогою збережених процедур і функцій заданої сигнатури дозволяє «включити» клас в підсистему нечіткого пошуку без додаткових витрат.
Зрозуміло, за універсальність потрібно платити. Наприклад, необхідно писати код перевірки семантики класів об'єктів, що додаються в таблицю дублікатів.
Розглянута в прикладі з фірмами і контактними особами підсистема буде функціонувати в наступному режимі. На першому етапі, при грубому порівнянні без урахування семантики такі записи будуть розпізнані як дублікати:
Однак і такі ось записи теж потраплять в число «підозрілих»:
Але на другому етапі при аналізі з урахуванням семантики серед «підозрілих» записи з другого прикладу будуть відбраковані внаслідок низької релевантності назв компаній та прізвищ.
Корисні ресурси
Як позбутися від дублікатів в базі даних
проблеми
Для виконання всіх перерахованих вимог знадобиться цілий комплекс заходів, однією зі складових якого буде забезпечення відсутності дублікатів.
У будь-якій АІС можна виділити два основних типи дублювання інформації:
дублювання атрибутів, що мають жорстко задану структуру (формат) змісту;
дублювання атрибутів, які не мають жорстко заданої структури (формату) змісту, т. е. слабоструктурованих.
У першому випадку мова йде про різні кодах по галузевим, муніципальним, федеральним і міжнародним довідників і класифікаторів, ідентифікатори сутностей, які використовуються в якості ключових атрибутів пошуку (номери документів, рахунків, телефонів, ПІН-коди і т. Д.). У другому випадку ми маємо справу з різноманітними іменами власними, використовуваними людьми для ідентифікації: прізвища, назви книг, фільмів, пісень, фірм і т. Д. Сюди ж відносяться адреси, що містять назви вулиць, номери будівель і т. П., Що зберігаються в одному атрибуті типу строкового поля «Адреса».
Проблема дублювання жорстко структурованих атрибутів вирішується обмеженням на введення даних у відповідні поля. Наприклад, можна дозволити введення тільки дозволених символів в заданому форматі або вибирати всі допустимі значення атрибута з довідника. Пошук дублікатів в цьому випадку ведеться за точним збігом і не викликає складнощів.
Ситуація зі слабоструктурованих полями трохи складніше, тому що неможливо використовувати обмеження формату (інакше це буде перший випадок) і часто не можна застосовувати словники-довідники, по-кільки їх необхідний обсяг може вийти за розумні межі і багаторазово перевищити розмір основної інформації АІС. Уявіть собі розмір географічного довідника адрес для невеликого відділу, що займається міжнародною розсилкою листів. Ще важче оцінити витрати на підтримку такого довідника в актуальному стані, вони можуть перевищити витрати на утримання всього відділу.
рішення
Для перевірки дублювання слабоструктурованої інформації нам допоможуть алгоритми нечіткого пошуку, що дозволяють знаходити дані на підставі неповного збіги і оцінки їх релевантності - кількісного критерію схожості. Слід враховувати, що дані алгоритми не дають 100% -ної гарантії від помилок, т. Е. Зберігається ймовірність того, що будуть пропущені дублюючі дані або, навпаки, дані будуть розпізнані як дублікати, не будучи такими. Тому для досягнення найкращого результату може бути необхідна участь людини.
Загальний принцип застосування алгоритмів для пошуку дублікатів наступний:
Проводиться обчислення деякого показника відповідності ( «схожості») двох символьних рядків, наприклад дистанції (відстані) або релевантності.
Даний показник наводиться до відносної шкалою відповідності в інтервалі від 0 до 1 (0 - повне неспівпадання, 1 - повний збіг). Надалі ця шкала легко може бути приведена до процентному вигляду, зручного для сприйняття людиною.
Експериментальним шляхом для тестового масиву даних визначається нижній поріг автоматичної обробки (Па), за яким кількість помилок розпізнавання дублікатів стає неприйнятним. Визначається також нижній поріг ручної обробки (Пр), за яким пошук видає практично одні помилки.
Па може бути використаний для подальшої уточнюючої обробки дублікатів в автоматичному режимі, залишаючи знайдені елементи зі значеннями відповід-наслідком нижче Па, але вище Пр для обробки людиною.
Основними шляхами внесення і зміни інформації в АІС є:
При введенні вручну потрібно забезпечити мінімальний час відгуку системи, так що використовуваний на цьому етапі алгоритм повинен працювати не стільки точно, скільки гранично швидко. При цьому параметр Па для даної операції може бути змінений відповідно до вимог по швидкості пошуку. Так як система розпізнавання не може надати 100% -ву точність, користувач повинен також мати можливість ігнорувати підказку системи і ввести дані. При таких умовах в базу даних неминуче буде потрапляти частина неякісної інформації, яка повинна бути виявлена в подальшому.
Таким чином, завдання виявлення і усунення дублікатів можна розбити на три етапи:
Виявлення дублікатів на рівні введення інформації користувачами та їх відхилення.
Виявлення дублікатів шляхом порівняння і аналізу вже введених даних відповідно до заданого Па і автоматичне видалення дублюючої інформації.
Аналіз і обробка людиною результатів попереднього етапу, які не можуть бути оброблені автоматично (показник відповідності нижче Па, але вище Пр).
можливі реалізації
В якості основи для реалізації системи розпізнавання був обраний метод N-gram, який забезпечує швидкий пошук на основі словника грамов (подстрок) і може бути використаний на всіх етапах. На першому кроці всі атрибути пошуку «склеюються» в один рядок. Наприклад, прізвище, ім'я та по батькові будуть оброблятися як один рядок ПІБ. Даний спосіб формування рядків для порівняння дає недостатньо точний, але швидкий результат при пошуку.
На другому етапі результати пошуку етапу 1 уточнюються, проходячи додаткову перевірку шляхом обчислення релевантності та відстаней вже для окремих атрибутів пошуку з урахуванням різних вагових коефіцієнтів, що підбираються експериментально. Наприклад, якщо два рядки з ПІБ збіглися з релевантностью 90%, але при порівнянні прізвищ отримана релевантність 60%, то, незважаючи на 100% -ве збіг імен та по батькові, можна відкинути результат першого етапу як помилковий.
Слід згадати про нестачу методу N-gram, який в деяких випадках може виявитися сущест-венним: великий розмір похідного безлічі подстрок (N-grams) щодо кількості вихідних рядків. Так, якщо N = 3, то для кожного рядка буде сформовано M = L - N + 1 словникових елементів, де L - довжина рядка, більше або дорівнює N (при L
Наприклад, якщо у вас є база даних на 50 тис. Абонентів, середня довжина ПІБ включає 50 символів, то потужність безлічі подстрок становитиме приблизно 50 000 * (50 - 3) = 2 350 000 елементів. При цьому кількість унікальних елементів, т. Е. Елементів словника, яке безпосередньо залежить від довжини підрядка N, буде істотно нижче (експериментально була отримана різниця приблизно на два порядки), і, отже, вам буде важко організувати швидкий пошук по такому безлічі, використовуючи стандартні засоби реляційної СУБД у вигляді індексів. Вибірковість (selectivity) такого індексу буде мала, що може привести до відмови від його використання оптимізатором запитів. Рішенням даної проблеми при великій кількості даних для словника є збільшення довжини N.
Як несуворого докази застосовності даного рішення можна розглянути функцію максимальної кількості елементів словника (унікальних елементів безлічі) в залежності від розмірів подстрок і алфавіту. Ця функція буде давати верхнє значення в припущенні, що символи алфавіту розміщені по N рівномірним розподілом по рядках. Чим менше рівномірність, тим менше буде кількість унікальних елементів. На практиці рівномірність при невеликих N далека від ідеальної, але вона збільшується зі зростанням N, і в граничному випадку, коли N дорівнює довжині рядка, при відсутності точних дублів рядків ми отримуємо повністю рівномірний розподіл. Нехай N - довжина підрядка, m - кількість символів в алфавіті, включаючи пробіл і знаки пунктуації. Тоді кількість можливих розміщень з повтореннями з m символів алфавіту по N обчислюється відповідно до відомої формули комбінаторики і одно mN. Це значення і буде найвищої і грубої оцінкою (нехтуємо ще й тим, що далеко не всі розміщення зустрічаються і допустимі в мові словника) кількості унікальних елементів словника. Таким чином, при збільшенні N кількість унікальних елементів зростає, як статечна функція.
Розглянемо більш докладно приклад реалізації системи розпізнавання дублікатів для списку фірм з використанням СУБД MS SQL 2000. Нехай фірма характеризується наступним набором атрибутів:
Для розпізнавання дублікатів на першому етапі ми будемо використовувати рядок, складений з усіх перерахованих атрибутів, загальна довжина якої може досягати 260 символів. Припустимо, що в базі даних є 20 000 записів із середньою довжиною рядка 150 символів. При таких параметрах вибираємо довжину подстроки N = 4. Таблиці для зберігання списку фірм і словника будуть виглядати так, як описано в лістингу 1.
Для початкового заповнення безлічі подстрок таблиці CompanyNGrams і подальшого її підтримки в узгодженому зі списком фірм стані (наприклад, використовуючи тригер) нам буде потрібно функція розбиття рядка на підрядка (грам). Ось приклад функції, яка повертає безліч подстрок (грамов) початкового рядка у вигляді таблиці:
Для початкового заповнення таблиці множе-ства подстрок CompanyNGrams можна скористатися наведеною вище функцією розбиття на грам наступним чином:
Для пошуку дубліката слід сформувати рядок для пошуку, розбити її на грам (підрядка) і обчислити релевантність по відношенню до інших рядках, що зберігаються в БД, які ми попередньо розділили на підрядка.
Релевантність (R) двох рядків S1 і S2 при довжині грама (підрядка) N обчислюється за такою формулою:
R = [NGramMatch (S1, S2) + NGramMatch (S2, S1)] / [NGramCount (S1) + NGramCount (S2)]
NGramCount (S) = [len (S) - N + 1],
де len (S) - довжина рядка S;
NGramMatch (S1, S2) - сума збігів всіх подстрок з S1 в рядку S2.
Тоді для пошуку схожих рядків можна використовувати наступну функцію, що складається всього з одного оператора SELECT:
приклади
Для реалізації підсистеми пошуку дублікатів в одній з CRM-систем (CRM - Customer Relationship Management - управління відносинами з клієнтами) була використана наступна схема (див. Малюнок).
Схема бази даних в системі була виконана відповідно до принципу об'єктно-реляційного підходу, описаного нами раніше в серії статей «Проектування ядра інформаційної системи» (див. «Мир ПК», №7 , 8 , 9 / 07).
Загальний для всіх класів предок Object відбивається на однойменну таблицю. Зв'язок з нащадками (узагальнення) та інші типи асоціацій між класами уніфіковані по ключу OID.
Службові таблиці CompanyNGrams і PersonNGrams зберігають відповідно подстроки-грам об'єктів класів «Компанія» і «Контактна особа». Таблиця ObjDuplicates завдяки уніфікованої зв'язку з OID зберігає зв'язку між потенційними дублікатами обох класів.
система розширювана . Для додавання нового типу пошуку дублікатів, наприклад, в географічний довідник або каталог товарів вам буде потрібно тільки внести в систему таблицю зберігання грамов і методи обчислення релевантності даного класу. Реалізація за допомогою збережених процедур і функцій заданої сигнатури дозволяє «включити» клас в підсистему нечіткого пошуку без додаткових витрат.
Зрозуміло, за універсальність потрібно платити. Наприклад, необхідно писати код перевірки семантики класів об'єктів, що додаються в таблицю дублікатів.
Розглянута в прикладі з фірмами і контактними особами підсистема буде функціонувати в наступному режимі. На першому етапі, при грубому порівнянні без урахування семантики такі записи будуть розпізнані як дублікати:
Однак і такі ось записи теж потраплять в число «підозрілих»:
Але на другому етапі при аналізі з урахуванням семантики серед «підозрілих» записи з другого прикладу будуть відбраковані внаслідок низької релевантності назв компаній та прізвищ.
Корисні ресурси
