#oracle #algorithm #matching
#Oracle #алгоритм #сопоставление
Вопрос:
Я работаю над программой сопоставления, которая сопоставляет 2 набора данных, используя комбинацию полей, таких как имя, фамилия, ИНН, SSN, DOB, адрес… Наборы данных всегда разные, и поля, которые у меня есть для сопоставления, будут отличаться. У меня есть приложение в MS Access, где я сопоставляю имена полей, доступные в моем списке полей.
Я оценил каждое поле по силе от 0 до 100 (конечно, все это субъективно, поскольку у меня нет способа узнать, насколько уникально поле. Например, у меня DOB на уровне 50%, но действительно ли DOB уникален на 50%? Насколько уникальна фамилия? Итак, правильное ранжирование — моя первая проблема, поскольку порядок совпадений будет зависеть от этого ранга).
Ниже приведена моя таблица, в которой указаны все поля, сила, которую я им присвоил (от 0 до 100), и порядок сортировки (он привязан к силе, в основном, сортировка в порядке от самого сильного к самому слабому).
После сопоставления я запускаю код, который создает перестановки поля SortIDPracs.Итак, я получаю все возможные комбинации полей для этого набора данных. Ниже приведен пример соответствующих категорий. Как вы можете видеть, я запускаю каждую категорию 4 раза:
1. с именем и фамилией
2. с первым начальным именем
3 с именем, первые 4 буквы фамилии
4. без имени
Ниже приведен код, который создает приведенный выше список. Вы можете подключить разные значения SortIdPracs, и вы, очевидно, получите другой список комбинаций.
DECLARE
VarCountOfFields NUMBER;
VarCurrentFieldNo NUMBER;
VarFirstNo NUMBER;
VarCountOfRanked VARCHAR2(1000);
VarSQLSelect1 VARCHAR2(1000);
VarSQLSelect2 VARCHAR2(4000);
VarSQLSelect2MatchedText VARCHAR2(4000);
VarSQLSelect2SQL1 VARCHAR2(4000);
VarSQLSelect2SQL2 VARCHAR2(4000);
VarSQLSelect2MatchWorth VARCHAR2(4000);
VarSQLFrom2 VARCHAR2(1000);
VarSQLJoins2 VARCHAR2(1000);
VarSQLText CLOB;
BEGIN
TruncateTable_P('MATCH_300');
INSERT INTO MATCH_300
SELECT LEVEL Lvl
,SYS_CONNECT_BY_PATH(col1, ', ') PathNo
FROM (SELECT SortIDPracs Col1
,SortIDPracs Col2
FROM (SELECT 101 SortIDPracs FROM DUAL
UNION ALL
SELECT 102 SortIDPracs FROM DUAL
UNION ALL
SELECT 104 SortIDPracs FROM DUAL
UNION ALL
SELECT 105 SortIDPracs FROM DUAL
UNION ALL
SELECT 106 SortIDPracs FROM DUAL
UNION ALL
SELECT 107 SortIDPracs FROM DUAL)) tmp
CONNECT BY PRIOR col1 < col2
ORDER BY LEVEL
,SYS_CONNECT_BY_PATH(col1, ', ');
--can't combine, string concatenation too long error
TruncateTable_P('MATCH_301');
INSERT INTO MATCH_301
SELECT ROWNUM UniqueID
,SUBSTR(PathNo, 3, 99) Permutations
,(TRIM(LENGTH(SUBSTR(PathNo, 3, 99))) - TRIM(LENGTH(TRANSLATE(SUBSTR(PathNo, 3, 99), 'A,', 'A')))) 1 CountOfFields
FROM (SELECT DISTINCT tmp.*
FROM MATCH_300 tmp
ORDER BY 1 DESC
,2);
SELECT COUNT(DISTINCT mf.SortIDPracs)
INTO VarCountOfFields
FROM MATCH_MAPPING tmp
,MATCH_FIELDS mf
WHERE 1 = 1
AND mf.SortIDPracs IS NOT NULL
AND tmp.MatchFieldID = mf.MatchFieldID
AND tmp.ProjectCriteriaID = 6;
VarCurrentFieldNo := 1;
WHILE VarCurrentFieldNo <= VarCountOfFields LOOP
IF VarCurrentFieldNo <= 2 THEN
VarFirstNo := 1;
ELSE
VarFirstNo := 2;
END IF;
IF VarSQLSelect1 IS NULL THEN
VarSQLSelect1 := ',REGEXP_SUBSTR(tmp.Permutations, ''[^ ] '', ' || VarFirstNo || ', ' || VarCurrentFieldNo || ') Permutations' || VarCurrentFieldNo;
VarSQLSelect2 :=
',mt'
|| VarCurrentFieldNo
|| '.FieldName FieldName'
|| VarCurrentFieldNo
|| ',mt'
|| VarCurrentFieldNo
|| '.FieldStrengthPracs FieldStrengthPracs'
|| VarCurrentFieldNo
|| ',mt'
|| VarCurrentFieldNo
|| '.StrengthRankPracs StrengthRankPracs'
|| VarCurrentFieldNo;
VarSQLFrom2 := ',MATCH_FIELDS mt' || VarCurrentFieldNo;
VarSQLJoins2 := 'AND tmp.Permutations' || VarCurrentFieldNo || ' = mt' || VarCurrentFieldNo || '.SortIDPracs( )';
VarSQLSelect2MatchedText := 'FieldName' || VarCurrentFieldNo;
VarSQLSelect2SQL1 := 'FieldName' || VarCurrentFieldNo;
VarSQLSelect2SQL2 := 'mt.'' || FieldName' || VarCurrentFieldNo || ' || '' = pr.'' || FieldName' || VarCurrentFieldNo;
VarSQLSelect2MatchWorth := ' NVL(FieldStrengthPracs' || VarCurrentFieldNo || ',0) ';
VarCountOfRanked := ' NVL(StrengthRankPracs' || VarCurrentFieldNo || ',0) ';
ELSE
VarSQLSelect1 :=
VarSQLSelect1
|| ',REGEXP_SUBSTR(tmp.Permutations, ''[^ ] '', '
|| VarFirstNo
|| ', '
|| VarCurrentFieldNo
|| ') Permutations'
|| VarCurrentFieldNo;
VarSQLSelect2 :=
VarSQLSelect2
|| ',mt'
|| VarCurrentFieldNo
|| '.FieldName FieldName'
|| VarCurrentFieldNo
|| ',mt'
|| VarCurrentFieldNo
|| '.FieldStrengthPracs FieldStrengthPracs'
|| VarCurrentFieldNo
|| ',mt'
|| VarCurrentFieldNo
|| '.StrengthRankPracs StrengthRankPracs'
|| VarCurrentFieldNo;
VarSQLFrom2 := VarSQLFrom2 || ',MATCH_FIELDS mt' || VarCurrentFieldNo;
VarSQLJoins2 := VarSQLJoins2 || 'AND tmp.Permutations' || VarCurrentFieldNo || ' = mt' || VarCurrentFieldNo || '.SortIDPracs( )';
VarSQLSelect2MatchedText :=
VarSQLSelect2MatchedText
|| ' || CASE WHEN FieldName'
|| VarCurrentFieldNo
|| ' IS NOT NULL THEN '', '' || FieldName'
|| VarCurrentFieldNo
|| ' END';
VarSQLSelect2SQL1 :=
VarSQLSelect2SQL1 || ' || CASE WHEN FieldName' || VarCurrentFieldNo || ' IS NOT NULL THEN '', '' || FieldName' || VarCurrentFieldNo || ' END';
VarSQLSelect2SQL2 :=
VarSQLSelect2SQL2
|| ' || CASE WHEN FieldName'
|| VarCurrentFieldNo
|| ' IS NOT NULL THEN '' AND mt.'' || FieldName'
|| VarCurrentFieldNo
|| ' || '' = pr.'' || FieldName'
|| VarCurrentFieldNo
|| ' END ';
VarSQLSelect2MatchWorth := VarSQLSelect2MatchWorth || ' NVL(FieldStrengthPracs' || VarCurrentFieldNo || ',0) ';
VarCountOfRanked := VarCountOfRanked || ' NVL(StrengthRankPracs' || VarCurrentFieldNo || ',0) ';
END IF;
VarCurrentFieldNo := VarCurrentFieldNo 1;
COMMIT;
END LOOP;
DropObject_P('TABLE', 'MATCH_302');
VarSQLText := 'CREATE TABLE MATCH_302
AS
SELECT UniqueID, CountOfFields ' || VarSQLSelect1 || '
FROM (SELECT DISTINCT UniqueID
,CountOfFields
,REPLACE(Permutations, '', '', '' '') Permutations
FROM MATCH_301) tmp
ORDER BY 1';
-- DBMS_OUTPUT.put_line(VarSQLText);
EXECUTE IMMEDIATE VarSQLText;
COMMIT;
DropObject_P('TABLE', 'MATCH_303');
VarSQLText := 'CREATE TABLE MATCH_303
AS
SELECT UniqueID, CountOfFields ' || VarSQLSelect2 || '
FROM MATCH_302 tmp ' || VarSQLFrom2 || '
WHERE 1=1 ' || VarSQLJoins2 || '
ORDER BY 1';
-- DBMS_OUTPUT.put_line(VarSQLText);
EXECUTE IMMEDIATE VarSQLText;
COMMIT;
DropObject_P('TABLE', 'MATCH_304');
VarSQLText :=
'CREATE TABLE MATCH_304
AS
SELECT UniqueID, CountOfFields 2 CountOfFields
,1 PassNo, ''First Name, Last Name, '' || '
|| VarSQLSelect2MatchedText
|| ' MatchedText, ''FN, LN, '' || '
|| VarSQLSelect2SQL1
|| ' SQL1, ''mt.FN = pr.FN AND mt.LN = pr.LN AND '
|| VarSQLSelect2SQL2
|| ' SQL2, (30'
|| VarSQLSelect2MatchWorth
|| ') MatchWorth, (0'
|| VarCountOfRanked
|| ') CountOfRanked
FROM MATCH_303 tmp
UNION ALL SELECT UniqueID, CountOfFields 2
,2 PassNo, ''First Initial, Last Name, '' || '
|| VarSQLSelect2MatchedText
|| ' MatchedText, ''FN1, LN, '' || '
|| VarSQLSelect2SQL1
|| ' SQL1, ''mt.FN1 = pr.FN1 AND mt.LN = pr.LN AND '
|| VarSQLSelect2SQL2
|| ' SQL2, (25'
|| VarSQLSelect2MatchWorth
|| ') MatchWorth, (0'
|| VarCountOfRanked
|| ') CountOfRanked
FROM MATCH_303 tmp
UNION ALL SELECT UniqueID, CountOfFields 2
,3 PassNo, ''First Name, First 4 Letters Of Last Name, '' || '
|| VarSQLSelect2MatchedText
|| ' MatchedText, ''FN, LN4, '' || '
|| VarSQLSelect2SQL1
|| ' SQL1, ''mt.FN = pr.FN AND mt.LN4 = pr.LN4 AND '
|| VarSQLSelect2SQL2
|| ' SQL2, (10'
|| VarSQLSelect2MatchWorth
|| ') MatchWorth, (0'
|| VarCountOfRanked
|| ') CountOfRanked
FROM MATCH_303 tmp
UNION ALL SELECT UniqueID, CountOfFields
,4 PassNo, '
|| VarSQLSelect2MatchedText
|| ' MatchedText, '
|| VarSQLSelect2SQL1
|| ' SQL1, '''
|| VarSQLSelect2SQL2
|| ' SQL2, (0'
|| VarSQLSelect2MatchWorth
|| ') MatchWorth, (0'
|| VarCountOfRanked
|| ') CountOfRanked
FROM MATCH_303 tmp ';
-- DBMS_OUTPUT.put_line(VarSQLText);
EXECUTE IMMEDIATE VarSQLText;
COMMIT;
END;
Этот запрос предоставит вам данные из скриншота выше
SELECT DISTINCT tmp.*
FROM MATCH_304 tmp
ORDER BY 1,3;
Затем я спускаюсь и перебираю каждую соответствующую категорию, используя соединения из поля SQL2. Поле SQL1 — это список полей. Итак, у меня есть процедура, которая перебирает каждую категорию и вставляет значения из этих 2 полей в запрос
Пример запроса для одной из категорий выглядит следующим образом (итак, это цикл для сопоставления FN, LN, SSN, адресов).
INSERT INTO MATCH_MATCHED
SELECT -- append ordered use_hash(mt,pr)
DISTINCT mt.UniqueID
,pr.UniqueID
,3040
,2
FROM MATCH_MATCH mt
,(SELECT DISTINCT UniqueID
,FN
,LN
,SSN
,Address
FROM MATCH_PROVS pr) pr
WHERE 1 = 1
AND mt.FN = pr.FN
AND mt.LN = pr.LN
AND mt.Address = pr.Address
AND mt.SSN = pr.SSN;
У меня есть несколько проблем
- Нужна помощь в назначении силы каждого поля (я почти уверен, что разум неадекватен). Если я знаю, что всего существует 100 TIN (например), и я знаю, что в худшем случае 20 поставщиков будут совместно использовать TIN, как мне вычислить%, что совпадение только по TIN является правильным совпадением, это 80% в этом примере?
- Необходимо найти способ присвоить оценку strengths% каждой соответствующей категории. Я думал о суммировании оценок по всем полям и делении на количество полей, но результаты не везде верны. Например, SSN получит более высокий балл, чем FN, LN, SSN, DOB, City.
- Мне нужно найти способ, чтобы этот показатель менялся в зависимости от каждого набора данных. Например, если мне предоставлен набор данных, содержащий FN, LN, SSN, DOB, адрес, Город, штат, почтовый индекс, я не буду слишком доволен совпадением по FN, LN, Address, поскольку оно также должно было совпадать по SSN и / или DOB. Таким образом, счет в этом матче не должен быть слишком высоким, поскольку было гораздо больше полей, на которых он мог бы совпасть. Но если мне предоставляется набор, содержащий FN, LN, address, то совпадение по FN, LN, Address становится 100% совпадением. Таким образом, этот счет должен учитывать поля, доступные в начале матча
- Оценка должна быть достаточно точной, чтобы я мог доверять ей для динамического исключения некоторых категорий во время выполнения. Например, я никогда не хочу доверять только совпадению городов и штатов. Поэтому в идеале я хочу посмотреть на результат во время запуска и даже не запускать эту строку, потому что результат для нее был бы ниже некоторого порога
Я не могу придумать систему подсчета очков, которая позволила бы мне достичь всего этого.
Комментарии:
1. Не могли бы вы привести небольшой пример? Может быть, два набора данных, в каждом из которых всего 4 записи, и объясните, как вы бы их сопоставили?
2. Конечно!! Будет сделано через некоторое время
3. @Stef привет, я обновил оригинал. надеюсь, это даст вам достаточно информации, если нет, дайте мне знать. спасибо, что вы вообще проявили интерес!