CC BY
16Применение метода нечеткого поиска в операции соединения реляционных таблиц баз данных
Федоркова Г.О. (galina@lipetsk.ru)
Липецкий государственный технический университет
Введение
Во многих организациях функционируют информационные системы, основанные на концепции баз данных (БД) и организованные в среде распространенных реляционных СУБД. Одна из наиболее часто встречающихся операций - соединение таблиц, происходящее по условию равенства значений полей. Когда ввод данных в таблицы осуществляется независимо, ошибки операторского ввода приводят к тому, что часть записей не удается соотнести, и они выпадают из рассмотрения или их приходится просматривать вручную. В частности, это происходит при обеспечении взаимодействия распределенных БД в ситуации обмена данными между организациями [1, 2, 3].
Расстояние, характеризуемое количеством ошибок, необходимых для получения одной строки из другой, называется метрикой Левенштейна (допускается вставка символа, удаление и замена). Задача поиска слова в тексте или словаре с точностью до h ошибок называется задачей нечеткого поиска. В предлагаемой статье рассматривается применение одного из методов нечеткого поиска для осуществления соединения таблиц по условию непревышения расстоянием Левенштейна заданного порогового значения.
1. Описание задачи
Пусть заданы две таблицы - Я1 и Я2, в каждой из которых присутствует поле atr, содержащее строковое значение. Значения атрибута (строки) будем обозначать строчными буквами латинского алфавита Ь,
с, ...), множество символов строки - квадратными скобками (например, - множество символов строки a). Требуется найти все пары строк, для которых расстояние Левенштейна между значениями поля в одной и в другой таблице р^лй", я2^г) не превосходило бы заданного порогового значения ^
^(я^гд^г) < h(r1 х ^2). (1)
При вычислении расстояния для всех пар записей из таблиц размерами т и п и последующего сравнения результата с h потребуется вызвать функцию вычисления расстояния т-п раз. Воспользовавшись методами нечеткого поиска в словаре для каждого из слов одной таблицы можно добиться времени т-О(п), где О(п) - время нечеткого поиска в словаре размера п, зависящее от метода. Когда одна из таблиц доступна заранее (например, если Я1 - это данные организации, решающей задачу, а Я2 - поступающие периодически данные другой организации), время вычисления любого выражения, не затрагивающего Я2, можно не учитывать, поскольку эти вычисления могут быть проведены предварительно.
Наиболее быстро СУБД выполняет операцию соединения по условию равенства. Однако подобрать функцию Да) такую, чтобы
ОД = ОД р(^ Ь) < h (2)
невозможно: при несущественных ограничениях на размер алфавита и длину слов найдутся a, Ь и с такие, что р^, Ь) < h, р(Ь, с) < h, но р^, с) > h, а справедливость (2) означала бы р^, с) < h вследствие транзитивности отношения равенства.
Сократить количество проверяемых пар все же можно при помощи предложенного в [4] метода нечеткого поиска хэширования по сигнатуре. При этом хэш-функция работает следующим образом. Алфавит разбивается на к непересекающихся множеств и каждый бит к-разрядного
(в двоичном представлении) значения функции определяется наличием в строке символа из соответствующего множества:
к Г1, И п Si ф 0, ед = 22i-1 хГ i (3)
" [0 иначе,
где к - количество бит в значении хэш-функции (и количество множеств в разбиении алфавита); Si - ье множество в разбиении алфавита, i = 1, к. При таком определении различие исходных слов на одну операцию вставки или удаления означает отличия хэш-функции не более чем в одном бите, а на одну операцию замены - не более чем в двух. При нечетком поиске это позволяет вначале выбрать все потенциальные термины из словаря, а затем осуществлять поиск только среди них.
Рассмотрим применение хэширования по сигнатуре к задаче условного соединения таблиц (1). Пусть для значения хэш-функции используется к двоичных разрядов. Размеры таблиц представляются как
т = т1 + т2 + ... + т2к,
п = п1 + п2 + ... + п2к,
2к 2к
тп = 22т^
i=и=1
где mi (nj) - количество записей Я1 (Я2), хэш-функция значения atr которых равна i (|). Не сложно найти все пары значений (^ j) для которых
3 a, Ь, т.ч. f(a) = ^ f(b) = I и р^, Ь) < h. Положим | е {0; 1} равным единице для таких пар и нулю - в противном случае. Тогда
с^)ль) = 0 ^ Р<ХЬ) > h и для этих пар строк р^, Ь) можно не вычислять. Таким образом, останется рассмотреть К(1) пар строк,
2к 2к 2к 2к ^ = 22^4^ <211 • minj = тп. ±=11=1 ±=1|=1
Подобное уменьшение времени достигается в результате выполнения эквисоединения Я1 М С М Я2, которое выполняется достаточно быстро (С - таблица, содержащая пары (^ Д для которых Су(Ь) = 1). Если Я1 доступна заранее, то требуется выполнить только второе соединение, так как Я1 М С может быть уже вычислена.
Рассмотрим теперь величину
2к 2к 2к 2к
ЕЕ « шпXXcíjh)PiPj . (4)
i=lj=l i=lj=l
Способ разбиения алфавита определяет хэш-функцию и, соответственно, значения mi (п|): шi « pi ш. Принцип построения этой функции не позволяет добиться равномерности распределения значений, и перекос становится сильнее с ростом длины слов. На рисунке приведены значения частот для наилучшей при к = 5 хэш-функции для фамилий длиной 17 символов. Су(Ь) зависят только от порогового расстояния h и от количества разрядов к. Размер промежуточной таблицы С для h = 1 составляет1 2к 2к
ЕЕ41 = (1 + к)(2к -1) + к(к -1) • 2к-2
i=и=1
(сравнить с 22к возможных пар (^ j)).
1 Если хэш-функция не принимает нулевых значений, то 1 + (2к — 2)(1 + к) + 2к 2к(к — 1)
0.00
0.04
0.00 0.04/"
0.00
0.00
0.00
0.00
0.00
7*0.08
0.04
0.00
0.00
0.10
0.00 •■X 0.09 X 0.00
0.00 ' 0.03 • '
0.00
0.00
'0.0.9
0
0.0.3
'0.09
0.00
0.06
Рисунок. Частоты появления значений хэш-функции
2. Нахождение наилучшей хэш-функции
Для нахождения оптимальной в смысле минимизации (4) хэш-функции при заданном значении к можно воспользоваться отдельной программой, расчитывающей Су и оценивающей pi по достаточно большому словарю. При первых испытаниях оптимальное значение находилось простым случайным перебором, однако кодирование разбиения в виде вектора длиной в размер алфавита позволяет легко применить генетический алгоритм для нахождения оптимального разбиения. Множества, на которые разбивается алфавит, нумеруются от 1 до к и ья позиция вектора показывает, какому множеству принадлежит ья буква алфавита. Правда, такое кодирование не взаимно-однозначно, и одно и то же разбиение будут задавать к! различных векторов.
Общая схема генетического алгоритма описана, например, в [5], и реализации отличаются только функциями кроссовера и мутации.
Простейшая реализация кроссовера, ставящего на ью позицию нового вектора с равной вероятностью значение ьй позиции одного из родителей, будет давать практически случайное (не близкое родителям) потомство, если множества разбиений пронумерованы различным образом. Пример для первых 10 букв алфавита приведен в таблице 1.
Таблица 1. Результат работы кроссовера при различном кодировании
разбиений
Особь Разбиение Кодировка 1 Кодировка 2
Родитель 1 Родитель 2 АБВ ГДЕ ЖЗИК АБВ ГДЖЗ ЕИК 1112223333 1112232233 1112223333 2223313311
Потомок 11122???33 ??????????
В таблице 1 показано образование потомка от пары родителей для двух вариантов кодирования родителя 2. Можно видеть, что в случае кодировки 1 потомок унаследует присутствующие у обоих родителей сочетания букв, а в случае кодировки 2 - нет, поскольку используются другие обозначения множеств, на который разбивается алфавит.
Для нахождения наилучшей хэш-функции была написана программа, которая по количеству бит к, пороговому значению ! и набору строк рассчитывает матрицу С и с помощью генетического алгоритма находит оптимальное для заданного набора строк разбиение алфавита. В программе был реализован кроссовер, приводящий обозначения одного из родителей к такому виду, в котором вектора отличаются друг от друга в наименьшем числе позиций. Для этого составлялась матрица Б = ^ | = 1,..., к, dij -число позиций, на которых в первом векторе стоит ^ а во втором - Как следствие, dij е {0, 1, . ..,32}, X dij = 32 (32 - число букв алфавита за вычетом 'Ё', которая не использовалась в тестовых БД).
Для квадратной матрицы Б е Як х к решалась задача о назначении методом Мака [6] и получившийся результат применялся для перенумерации. В приведенном примере вторая кодировка дает
Б =
0 3 0'
1 0 2 2 0 2
(оптимальное решение выделено подчеркиванием), то есть замену 1 ^ 2, 2 ^ 3, 3 ^ 1, что приводит нас к кодировке 1.
Мутация с заданной фиксированной вероятностью заменяет каждую позицию вектора произвольным значением от 1 до к.
Таким образом были найдены разбиения алфавита на 4, 5, 6 и 7
2к 2к
с(ь) = уу^-. множеств, оптимизирующие выражения ¿-¡¿.^ 1) гч^, = 1, 2 в
1=1|=1
двух случаях: когда хэш-функция вычисляется по фамилии и когда хэш-функция вычисляется как исключающее ИЛИ функций по фамилии, имени и отчеству. Б(1) показывают, какая доля из т-п пар записей подлежит проверке при использовании хэширования по сигнатуре.
3. Анализ эффективности метода
При использовании хэширования по сигнатуре время, затрачиваемое на проверку условия р^лй", к2^г) < уменьшается до доли в б(1)-ю часть. Если обозначить |Я1| - количество записей в таблице Я1,
|Я1 М С(1)| — количество записей соединения Я1 М С(Ь), то время на выполнение соединения с Я2 увеличивается в худшем случае в
К(1) = |Я1 М С(Ь)| / |Я1| раз.
Значения целевой функции в программе являлись оценкой величины Б(1), а не точным ее значением, поскольку частоты Pj значений функции среди записей таблицы Я2 нам не известны. Предполагалось, что они не сильно отличаются от частот таблицы Я1. Действительные значения
Б(1) = |Я1 М С М Я2| / |Я2 х Я2| и К(1), полученные из БД, приведены в таблицах 2, 3.
Таблица 2. Хэш-функция оптимизирована для фамилий из polis, для
одного различия
Размер хэша k 4 5 6 7
Ri 55693
R2 573
C(1) 119 341 915 2353
|R1 M C(1) M R2| 19842613 15073251 10806546 7282966
|R1 M C(1)| 418822 590684 796434 1030877
Оценка (D(1))* 0.618 0.469 0.334 0.228
D(1) 0.622 0.472 0.339 0.228
K(1) 7.5 10.6 14.3 18.5
C(2) 209 781 2685 8695
|R1 M C(2) M R2| 29712896 27514815 24634164 20973696
|R1 M C(2)| 752980 1358352 2330336 3807715
Оценка D(2) 0.929 0.859 0.764 0.655
D(2) 0.931 0.862 0.772 0.657
K(2) 13.5 24.4 41.8 68.4
Таблица 3. Хэш-функция оптимизирована для ФИО из polis (xor от функций для фамилии, имени и отчества), для одного различия
Размер хэша k 4 5 6 7
R1 55693
R2 573
C(1) 128 352 928 2368
|R1 M C(1) M R2| 18259112 12020842 7750235 4855894
|R1 M C(1)| 426641 597462 799049 1026232
Оценка (D(1))* 0.559 0.367 0.238 0.150
DU) 0.572 0.377 0.243 0.152
K(1) 7.7 10.7 14.3 18.4
C(2) 209 781 2685 8695
|R1 M C(2) M R2| 29392284 25850685 21710100 17327153
|R1 M C(2)| 757595 1406056 2342839 3791967
Оценка D(2) 0.914 0.802 0.666 0.535
D(2) 0.921 0.810 0.680 0.543
K(2) 13.6 25.2 42.1 68.1
Таблица 4. Относительное изменение времени (K() + T D(h)) / (1 + T)
K(1) 7.5 10.6 14.3 18.5 K(1) 7.5 10.6 14.3 18.5
D(D 0.622 0.472 0.339 0.228 D(i) 0.622 0.472 0.339 0.228
T k = 4 k = 5 k = 6 k = 7 T k = 4 k = 5 k = 6 k = 7
10 1.247 1.393 1.608 1.889 30 0.844 0.799 0.789 0.817
15 1.052 1.105 1.211 1.370 35 0.813 0.754 0.726 0.736
20 0.949 0.955 1.003 1.098 40 0.790 0.719 0.679 0.674
25 0.886 0.862 0.876 0.931 45 0.771 0.692 0.642 0.625
50 0.757 0.671 0.612 0.586
Если время выполнения соединения относится к времени проверки условия как 1:T, то общее время выполнения операции составит K(h) + T D(h) при использовании хэширования по сигнатуре против 1 + T при вычислении расстояния для всех пар записей. Результаты расчетов приведены в таблице 4, где выделены оптимальные значения. Строки, не имеющие выделения (все значения больше единицы), отвечают такому соотношению времен, при котором хеширование по сигнатуре применять нецелесообразно.
4. Апробация применяемого метода
Апробация метода проводилась на тестовой базе данных под управлением СУБД Oracle8 Enterprise Edition release 8.0.5. Функция lev определяет расстояние между двумя строковыми значениями при помощи алгоритма Укконена или возвращает значение '-1', если расстояние превышает заданное первым аргументом пороговое значение. CREATE FUNCTION lev
(h IN NUMBER, strl IN VARCHAR2, str2 IN VARCHAR2) RETURN NUMBER IS ... — объявления переменных BEGIN
... — тело функции — алгоритм Укконена END;
Тестирование проводилось при k = 5. Использованные таблицы и представления сведены в таблицу 5.
Таблица 5. Использованные таблицы и представления БД
Имя и комментарий Обозначение Тип Объем
hash функция, определенная f(-) FUNCTION
формулой (3)
polis содержит поля TABLE 55693
fam VARCHAR(17),
name VARCHAR(20),
namesecond VARCHAR(15),
hs fio NUMBER
по этой таблице оптимизирова
лась хэш-функция
pacin содержит поля R2 TABLE 573
fam VARCHAR(20),
im VARCHAR(10),
ot VARCHAR(10)
pacin copy точная копия R2 TABLE 573
таблицы pacin
polis small содержит 5-10 Rl TABLE 4999
записей из таблицы polis
hs5 ext c(1) TABLE 352
(a NUMBER, b NUMBER)
polis ext Ri X C(1) TABLE 53943
polisext x pacincopy R1 X C(1) X R2 VIEW 1081302
Копия таблицы pacin была введена для того, чтобы на время выполнения одного запроса не оказывали влияния сохранившиеся в кэше СУБД в ходе выполнения другого запроса записи.
Таблица polis_ext заполнялась при помощи запроса INSERT INTO polis_ext
SELECT Fam, Name, Namesecond, hs.b AS hsext, ... — остальные поля таблицы polis FROM polis_small, hs5_ext hs WHERE polis_small.hs_fio = hs.a;
Представление polisext_x_pacincopy определялось запросом
CREATE VIEW polisext_x_pacincopy AS (SELECT
polis ext.polis rowid as polis rowid,
polis_ext.fam as polis_fam, polis_ext.name as polis_im, polis_ext.namesecond as polis_ot, polis_ext.hsext as polis_hsext, pacin_copy.fam as pacin_fam, pacin_copy.im as pacin_im, pacin_copy.ot as pacin_ot, pacin_copy.hs_fio as pacin_hsfio, pacin_copy.ROWID as pacin_rowid FROM polis_ext, pacin_copy
WHERE polis_ext.hsext = pacin_copy.hs_fio);
Предварительно вычислялись значения хэш-функции
UPDATE pacin
SET hs_fio = xor(xor(hash(fam),hash(im)),hash(ot)); UPDATE polis SET hs_fio =
xor(xor(hash(fam),hash(name)),hash(namesecond));
и строились индексы
CREATE INDEX idx_polissmall_hsfio ON polis_small(hs_fio);
CREATE INDEX idx_polisext_hsext ON polis_ext(hsext);
В результате запрос, производящий выбор из R1 х R2 SELECT *
FROM polis_small p, pacin
WHERE lev(1, p.fam||' ,||p.name||, 'iip.namesecond, pacin.fam||' '||pacin.im||' '||pacin.ot) >= 0;
показал время выполнения 2517039 мс, а запрос, использующий
хэширование по сигнатуре R1 М C(1) М R2 SELECT *
FROM polisext_x_pacincopy -- (polis_ext , pacin_copy) WHERE lev(1,polis_fam||' '||polis_im||' '||polis_ot, pacin_fam||' '||pacin_im||' '||pacin_ot) >= 0;
- время 1020507 мс, то есть 0.4 от времени выполнения запроса, не использующего этот метод. Таким образом, предложен для практического использования метод, который позволяет сокращать время выполнения операции соединения таблиц БД, учитывающего ошибки операторского
ввода.
Список литературы
1. Иванова Г.О. Конгруэнции алгебраических систем в задачах различения объектов при неполной информированности // СПИ: Труды VI международной открытой научной конференции. Воронеж: ВЭПИ, 2001. С 66-67.
2. Иванова Г. О. Операция нечеткого реляционного соединения для интеграции данных разнородных локально автономных БД // СПИ: Сб. Трудов. Вып. 8. Воронеж: Центрально-черноземное книжное изд-во, 2003. С 106-107.
3. Солодков А.Ю. Идентификация сложных объектов нечисловой природы в субд с наличием ошибок и пропусков данных. Молодежная науч.-техн. конф. учащихся, студентов, аспирантов и молодых ученых к 65-летию кафедры ИУ4 (П8) «Наукоемкие технологии и интеллектуальные системы - 2003». Источник: http://iu4.Ьшstu.ru/konf/2003/sЬornik/s2_29.doc
4. Бойцов Л.М. Использование хеширования по сигнатуре для поиска по сходству // Прикладная математика и информатика. М. Изд-во факультета ВМиК, МГУ 2000, № 7.
5. Рутковскиая Д., Пилиньский М., Рутковский Л. Нейронные сети, генетические алгоритмы и нечеткие системы: Пер. с польск. Изд-во: "Горячая линия-Телеком, Радио и связь", 2004. - 452 с.
6. Банди Б. Основы линейного программирования: Пер. с англ. -М.: Радио и связь, 1989. - 176 с.
