Поиск дубликатов записей с использованием каскадных критериев с последующим объединением в одну запись

Question

Я использую MS SQL Server 2012 и выполнил простые запросы и загрузку данных, но не зацикливал и не делал операторы case или вложенные выборки. Я ищу некоторую помощь, чтобы начать меня на подходе.

Мы находимся в проекте, где мы объединяем список клиентов из нескольких унаследованных систем. У меня есть необработанная таблица клиентов в промежуточной базе данных, которая содержит записи из этих нескольких источников. Нам нужно сделать следующее перед записью финальной таблицы в витрину данных. Я бы подумал, что это будет довольно распространенный сценарий в мире очистки данных / золотой записи, но после долгих поисков не удалось найти подобный пост.

Во-первых, нам нужно найти записи, которые представляют одного и того же клиента. Эти записи поступают из нескольких источников, поэтому может быть более двух записей, которые представляют одного и того же клиента. Каждый источник использует похожую модель. Критерии, определяющие, представляют ли записи (записи) одного и того же клиента, изменяются в каскадной иерархии в зависимости от доступных значений. Первый критерий, который мы хотим использовать для записи, это DOB и SSN. Но если SSN отсутствует, то критериями для этой строки становятся Фамилия, Имя и DOB. Если и SSN, и DOB отсутствуют, дубликат теста изменится на фамилию + имя + другое поле критерия. Существуют и другие критерии даже после этого, если одно из имен отсутствует. А поскольку записи, представляющие одного и того же клиента, могут иметь разные доступные поля, нам придется использовать тест, который могут использовать обе записи. Не может быть повторяющихся записей, если окажется, что данный клиент существует только в одной системе.

2. После того, как дублированные записи будут идентифицированы, мы хотим объединить эти записи, представляющие клиента, чтобы в итоге мы получили 1 запись, представляющую клиента, записанную в новую таблицу, с использованием тех же полей. Объединение осуществляется путем сравнения значений одинаковых полей. Если SSN отсутствует в одном источнике, но доступен в другом, то этот SSN используется. Если существует более 2 записей, которые представляют клиента, и более 1 имеет SSN, и эти номера SSN отличаются, существует иерархия, основанная на том, из какой системы поступила запись, и мы хотим записать значение SSN из Система самая высокая в иерархии. Такая логика будет применяться к каждой области, которую мы должны исследовать.

Я думаю, что часть, которую мне сложнее всего осмыслить, это как вы сохраняете значения одной записи, чтобы вы могли сравнить ее с одной или несколькими другими записями в той же таблице, выполнить логику фактического сравнения, а затем записать "выигрышное" значение в новый стол? Если бы я мог получить некоторую помощь с этим, это было бы очень ценно.

Answer 1

Основные требования, которые у вас есть, удовлетворяются этим запросом

SELECT a.ID,
    DENSE_RANK() OVER( PARTITION BY DOB, SSN ) AS Match1,
    DENSE_RANK() OVER( PARTITION BY [Last Name], [First Name], DOB ) AS Match2,
    DENSE_RANK() OVER( PARTITION BY [Last Name], [First Name], [Another criteria] ) AS Match3
INTO #Matchmaking
FROM tCustStaging

Однако вы, вероятно, обнаружите, что вам сначала нужно будет "подготовить" (очистить) данные, то есть убедиться, что они все в одном формате, и удалить "мусор". Распространенной проблемой могут быть телефонные номера, в которых могут использоваться различные форматы, например, "02 1234 1234", "0212341234", "[phone]" и т. Д. Имена также могут иметь различия в написании, особенно для составных имен.

Еще один способ сделать сопоставление, это рассчитать совпадения по всем полям индивидуально

SELECT a.ID,
    DENSE_RANK() OVER( PARTITION BY SSN ) AS SSNMatch,
    DENSE_RANK() OVER( PARTITION BY DOB ) AS DOBMatch,
    DENSE_RANK() OVER( PARTITION BY LEFT( [Last Name], 10 ) ) AS LNMatch10,
    DENSE_RANK() OVER( PARTITION BY LEFT( [Last Name], 9 ) ) AS LNMatch9,
    DENSE_RANK() OVER( PARTITION BY LEFT( [Last Name], 9 ) ) AS LNMatch8,
    etc.
    DENSE_RANK() OVER( PARTITION BY LEFT( [Last Name], 3 ) ) AS LNMatch8,
    DENSE_RANK() OVER( PARTITION BY LEFT( [First Name], 10 ) ) AS FNMatch10,
    etc.
    DENSE_RANK() OVER( PARTITION BY [Other criteria1] ) AS OC1,
    DENSE_RANK() OVER( PARTITION BY [Other criteria2] ) AS OC2,
INTO #Matchmaking
FROM tCustStaging

Затем вы создаете самое сильное совпадение (SSN, DOB). Вы также можете поэкспериментировать с различными комбинациями полей, чтобы увидеть, что вы получите.

-- You can play around with various combinations to see what results you get
SELECT c.*
FROM #Matchmaking AS a
    INNER JOIN #Matchmaking AS b ON a.SSNMatch = b.SSNMatch AND a.DOBMatch = b.DOBMatch AND a.LNMatch10 = b.LNMatch10
    INNER JOIN tCustStaging AS C ON a.ID = c.ID

После каждой итерации соответствия вы сохраняете результаты.

Затем вы продолжаете ослаблять критерии соответствия, тщательно проверяя наличие ложных совпадений, пока критерии соответствия не станут настолько слабыми, что вы больше не получите полезных результатов.

В конечном итоге вы получите набор результатов, основанный на разной степени соответствия критериям.

В конце концов, количество "сомнительных совпадений" (если вы не уверены, что два клиента одинаковы или нет) будет зависеть от исходного качества данных и качества их после "подготовки". Вам, вероятно, все равно придется анализировать некоторые данные вручную.