Книга: «Нечеткое сопоставление данных в SQL»04.07.2024 16:15

Привет, Хаброжители!

Если бы вам предоставили два разных, но связанных между собой набора данных, какие инструменты вы бы использовали для поиска совпадений? А если все, что у вас есть, — это доступ к базе данных через SQL SELECT? Джим Лемер предлагает лучшие практики, методики и приемы, которые помогут вам импортировать, очищать, сопоставлять, оценивать и осмысливать разнообразные данные с помощью SQL.

Администраторы баз данных, программисты, бизнес-аналитики и специалисты по исследованию данных узнают, как выявлять и удалять дубликаты, разбирать строки, извлекать данные из XML и JSON, генерировать SQL с помощью SQL, упорядочивать данные и готовить наборы данных, а также применять подходы к качеству данных и ETL для поиска сходств и различий между различными выражениями одних и тех же данных.

Примеры, приведенные в книге, полны реальных приемов и содержат рабочий код.

DECLARE @FullName VARCHAR(100)
SET @FullName = 'Mortimer Snedley'
SELECT
    @FullName [Full Name],
    -- LEFT для имени
    LEFT(@FullName,
         CHARINDEX(' ', @FullName) — 1) [First Name],
    -- Подстрока для извлечения фамилии
    SUBSTRING(@FullName,
              CHARINDEX(' ', @FullName) + 1,
              PATINDEX('%, %', @FullName) -.
                CHARINDEX(' ', @FullName) — 1) [Last Name],
    -- RIGHT извлекает суффикс
    RIGHT(@FullName,
          LEN(@FullName) — 1 — PATINDEX('%, %', @FullName)) [Suffix];

Все целиком

Пришло время объединить полученные знания. В этой главе мы возьмем наборы данных, с которыми уже работали, и примеры из каждой главы и посмотрим, что мы можем выяснить, чтобы ответить на следующие вопросы:

• Сколько существующих клиентов содержится в импортируемых данных?
• И сколько потенциальных клиентов содержится в импортируемых данных?

Чтобы ответить на эти вопросы, нам также придется провести большую работу по очистке и нормализации данных. И конечно же, вы снова встретитесь со Снедли.

Подход

Код ниже может напоминать функциональное программирование. Знакомо?

SELECT
  CustomField3,
  CASE
    /*
       Если NULL — не беспокойтесь.
    */
    WHEN CustomField3 IS NULL THEN 'Адрес электронной почты не найден'
    /*
       Если в строке отсутствует знак @, адрес электронной почты не найден.
    */
    WHEN CHARINDEX('@', CustomField3) = 0 THEN 'Адрес электронной почты не
    найден'
    /*
       Если в строке есть знак @ и нет запятой, то строка содержит только
      адрес электронной почты.
    */
    WHEN CHARINDEX('@', CustomField3) > 0
      AND CHARINDEX(',', CustomField3) = 0
      THEN CustomField3
     /*
       Если адрес электронной почты находится слева, в начале строки,
       захватите его.
     */
     WHEN CHARINDEX('@', CustomField3) > 0
       AND CHARINDEX(',', CustomField3) > CHARINDEX('@', CustomField3)
       THEN LEFT(CustomField3, CHARINDEX(',', CustomField3) — 1)
      /*
        Если адрес электронной почты находится посередине строки,
        продолжайте!
      */
      WHEN CHARINDEX(',', CustomField3) > 0
        AND CHARINDEX('@', RIGHT(CustomField3, LEN(CustomField3) -.
                                 CHARINDEX(',', CustomField3)) >
             CHARINDEX(',', CustomField3)
         AND CHARINDEX('@', RIGHT(CustomField3, LEN(CustomField3) -.
                                  CHARINDEX(',', CustomField3)) <
             CHARINDEX(',', RIGHT(CustomField3, LEN(CustomField3) -.
                                  CHARINDEX(',', CustomField3))
         THEN SUBSTRING(CustomField3,
                        CHARINDEX(',', CustomField3) + 1,
                        CHARINDEX(',', RIGHT(CustomField3,
                                             LEN(CustomField3) -
                                             CHARINDEX(',', CustomField3))) — 1)
      /*
         Если адрес электронной почты находится справа, в конце строки,
         захватите его.
      */
      WHEN CHARINDEX(',', CustomField3) > 0
        AND CHARINDEX('@', CustomField3) > CHARINDEX(',', CustomField3)
        THEN RIGHT(CustomField3, LEN (CustomField3) — CHARINDEX(',', CustomField3))
      ELSE 'Адрес электронной почты не найден'
    END [Электронная почта?]
  FROM staging.CustomFields;

Считайте, что select — это анонимная (лямбда) функция, которая не принимает никаких параметров и возвращает преобразованный набор данных из известного (вызывающей стороне и функции) набора данных. Конечно, при наличии DDL-доступа к базе данных (с помощью create) можно создавать реальные SQL-функции и хранимые процедуры, которые затем можно вызывать по имени, передавать в них параметры и т. д.

Однако мы используем несколько простых операций ETL, чтобы придать данным нужную нам форму, и тогда сможем ответить на наши вопросы. Если вы придерживаетесь функционального подхода, то, возможно, согласитесь со мной, поскольку я давно считаю, что этот подход аналогичен концепции «map-reduce»:

SELECT transformed-columns /* Здесь можно проводить всевозможные преобразования. */
INTO target-table /* Целевая таблица ETL */
FROM source-table /* Исходная таблица ETL */
WHERE filter-conditions /* Различные критерии отбора.         */
ORDER BY new-order /* Отличная группа! Здесь можно повеселиться!  */

Он похож на преобразование наборов данных в новые, обобщенные наборы, с применением фильтрации и сортировки. Кроме того, при его использовании движок реляционной БД выполняет множество операций параллельной обработки и конвейеризации за вас. Помогает ли это? Посмотрим на пример из предыдущей главы:

/*
   Импортируйте данные в собственную рабочую область. Без условия WHERE
   из-за размера может оказаться непрактичным проводить фильтрацию даже
   на этом шаге. В данном случае имитируем ее с помощью TOP 10.
*/
SELECT TOP 10 *
INTO staging.Customers
FROM crm.NormalizedCustomer
ORDER BY LastName;

Это максимально просто. Обратите внимание, что, как и в функциональном программировании, мы не изменяем исходные данные; мы преобразуем их во что-то другое, а на следующем шаге работаем с результатом преобразований, и т. д.

Какая оценка?

Для своих целей мы получили данные из отдела маркетинга и импортировали их с помощью SQL Server Management Studio (SSMS) в dbo .PotentialMatches. Схема выглядит так:

/*
   Можно обсудить — обычно я не бываю так любезен.
*/
CREATE TABLE dbo.PotentialMatches
(
        first_name NVARCHAR(50) NULL,
        last_name NVARCHAR(50) NULL,
        company_name NVARCHAR(50) NULL,
        address NVARCHAR(50) NULL,
        city NVARCHAR(50) NULL,
        county NVARCHAR(50) NULL,
        state NVARCHAR(50) NULL,
        zip NVARCHAR(50) NULL,
        country NVARCHAR(50) NULL,
        phone1 NVARCHAR(50) NULL,
        phone2 NVARCHAR(50) NULL,
        email NVARCHAR(50) NULL,
        web_address NVARCHAR(50) NULL
)

Это довольно типичная схема инструментального импорта. Просто возьмите все данные, запихните в тип varchar и убедитесь, что все они допускают null, потому что доверять источнику данных не стоит, даже если вам предоставили схему и поклялись, что обязательные столбцы всегда будут ненулевыми. Возможно, если это постоянный и проверенный поставщик данных, вы измените некоторые типы данных на более точный (но ни в коем случае не импортируйте ZIP-коды как int!) и пометите некоторые столбцы not null. Но чтобы начать доверять поставщику, нужно подождать некоторое время и сначала посмотреть, как обстоят дела с качеством его данных.

Проверим несколько столбцов и строк на значение Snedley:

SELECT TOP 10
    first_name,
    last_name,
    company_name
FROM dbo.PotentialMatches
WHERE
    last_name LIKE '%Snedley%'
    OR company_name LIKE '%Snedley%'
ORDER BY last_name, first_name, company_name;

Пока никаких сюрпризов. Мы видим, что в некоторых строках нет ни имен, ни фамилий, только название компании, в некоторых — только имена и фамилии, без названия компании, а в некоторых — и то и другое.

Мы подошли к тому моменту, когда нужно решить, что именно мы будем оценивать. Глядя на столбцы входных данных, мы видим, что они совпадают с нашей CRM-системой. «Нечеткость» в том числе заключается в предположении, какие столбцы одной стороны сравнения соответствуют столбцам другой стороны по названию, но в нашем случае эта работа была проделана за нас, и все названия столбцов имеют соответствия в CRM. Итак, какие из них полезнее всего сравнивать? Некоторые, очевидно, сразу можно отбросить: например, у нескольких человек может быть один и тот же адрес сайта, если они работают в одной компании, и получится, что для большого массива данных (с обеих сторон) адреса сайта вообще не будет. Поэтому для дальнейшего сравнения он бесполезен (как всегда, с пометкой «если только нет особых требований бизнеса»).

И наоборот, работая с нашей внутренней системой CRM, мы знаем, что phone1 в ней — это номер мобильного телефона, а phone2 — номер домашнего или рабочего телефона (это отражено в представлении crm.NormalizedCustomer). Мы предполагаем, что то же самое верно и для импортируемых данных, но будем сравнивать оба номера с обоими. Телефоны, как и электронные адреса, хорошо подходят для снятия неопределенности, поэтому они автоматически попадают в список сравниваемых атрибутов.

Возможно, вы не заметили ее отсутствия, но дата рождения — это еще один «золотой стандарт» сопоставления данных. Если у вас есть (почти) полное имя и дата рождения, зачастую этого достаточно. Именно поэтому вас просят указать эти два атрибута данных, чтобы идентифицировать себя, когда вы попадаете в медицинское учреждение.

Однако при сопоставлении демографических данных во входящем наборе редко бывает дата рождения. Если она есть, оценить степень соответствия может быть очень просто при условии, что вы провели обычную очистку данных (даты рождения в будущем и т. д.). Но эта книга о «нечетком» сопоставлении данных, когда дни рождения, налоговые идентификаторы и другие совершенно естественные и искусственные ключи оказываются недоступны, поскольку эта информация позволяет идентифицировать личность или содержит персональные данные и является конфиденциальной, или, если она все же есть в наборе данных, она замаскирована, вызывая дополнительную «нечеткость»!

Из 13 столбцов мы выбрали 4. На этом этапе нужно, пожалуй, провести небольшой анализ EDA: покопаться в разных столбцах файла импорта, посмотреть на распределение значений и понять, достаточно ли их вообще. Чтобы проверить наши предположения касательно веб-адресов:

SELECT
    COUNT(*) [Всего строк],
    COUNT(web_address) [Не-NULL-значения],
    COUNT(DISTINCT web_address) [Уникальные домены]
FROM crm.Customer;

Учитывая следующий результат, кажется маловероятным, что использование этого столбца для сравнения будет плодотворным:

508 — 144 = 364 веб-адреса со значением NULL
364 / 508×100 = 71,7% значений NULL

Попробуем, однако, следующий вариант:

SELECT
    COUNT(*) [Всего строк],
    COUNT(phone1) [Не-NULL-значения],
    COUNT(DISTINCT phone1) [Уникальные номера мобильных телефонов]
FROM crm.Customer;

Получилось:

508 — 505 = 3 номера телефона со значением NULL
505 / 508×100 = 99,4% значений не NULL

Следовательно, номер мобильного телефона, или что там у нас обозначает phonel, выглядит хорошим кандидатом на сравнение, тем более что подавляющее большинство телефонных номеров уникальны.

Переходим к остальным атрибутам. Как обычно, добавим в оценку фамилию, имя и название компании. Используем хотя бы часть названия улицы. Возможно, я выгляжу провокатором, но вы должны помнить из главы 10, что я считаю большинство элементов адреса, кроме почтового индекса, бесполезными для сравнения, поэтому мы будем использовать именно индекс. Из первоначальных 13 столбцов:

• first_name
• last_name
• company_name
• address
• city
• county
• state
• zip
• country
• phone1
• phone2
• email
• web_address

мы оставим 8:

Очень хорошее начало. В процессе настройки можно будет добавить или удалить столбцы (быстрее обработка, меньше наборы данных), но обычно для сравнения важны 5–10 столбцов, которые могут увеличить степень соответствия:

/*
   Совпадения только в столбцах, выбранных для оценки.
*/
SELECT
    COUNT(*) [Всего совпадений]
FROM crm.Customer C
INNER JOIN dbo.PotentialMatches P ON
        C.first_name = P.first_name
    AND C.last_name = P.last_name
    AND C.company_name = P.company_name
    AND C.address = P.address
    AND C.zip = P.zip
    AND C.phone1 = P.phone1
    AND C.phone2 = P.phone2
    AND C.email = P.email;

Первый проход: наивное сопоставление

Начнем с наивного сопоставления имеющихся атрибутов:

Может быть, нам повезло. Может быть.

А может быть, мы забыли о значениях null и о том, как SQL их обрабатывает:

/*
    Убрать NULL.
*/
SELECT
     COUNT(*) [Всего совпадений]
 FROM crm.Customer C
 INNER JOIN dbo.PotentialMatches P ON
         COALESCE(C.first_name, '')   = COALESCE(P.first_name, '')
     AND COALESCE(C.last_name, '')    = COALESCE(P.last_name, '')
     AND COALESCE(C.company_name, '') = COALESCE(P.company_name, '')
     AND COALESCE(C.address, '')      = COALESCE(P.address, '')
     AND COALESCE(C.zip, '')          = COALESCE(P.zip, '')
     AND COALESCE(C.phone1, '')       = COALESCE(P.phone1, '')
     AND COALESCE(C.phone2, '')       = COALESCE(P.phone2, '')
     AND COALESCE(C.email, '')        = COALESCE(P.email, '');

Опять нет. Надеемся, что, заменив все and на or, мы получим хоть что-то; в конце концов, это будет основой для оценки соответствия:

/*
   Убрать NULL.
*/
SELECT
    COUNT(*) [Всего совпадений]
FROM crm.Customer C
INNER JOIN dbo.PotentialMatches P ON
       COALESCE(C.first_name, '')   = COALESCE(P.first_name, '')
    OR COALESCE(C.last_name, '')    = COALESCE(P.last_name, '')
    OR COALESCE(C.company_name, '') = COALESCE(P.company_name, '')
    OR COALESCE(C.address, '')      = COALESCE(P.address, '')
    OR COALESCE(C.zip, '')          = COALESCE(P.zip, '')
    OR COALESCE(C.phone1, '')       = COALESCE(P.phone1, '')
    OR COALESCE(C.phone2, '')       = COALESCE(P.phone2, '')
    OR COALESCE(C.email, '')        = COALESCE(P.email, '');

Хммм… учитывая, что в нашей CRM-системе всего 508 строк, а в импортируемых данных — 34, это не совсем то число, которое мы ожидали увидеть. Это соединение, несмотря на использование INNER JOIN, в итоге посчитало за отдельные «совпадения» все комбинации совпадающих пар полей. Это не полное декартово соединение (508×34 = 17 272), но близкое к нему.

Вернемся пока к использованию and и добавим немного магии нормализации:

/*
   Попробуйте нормализовать.
*/
SELECT
   COUNT(*) [Всего совпадений]
FROM crm.Customer C
INNER JOIN dbo.PotentialMatches P ON
        LEFT(C.first_name, 1) = LEFT(P.first_name, 1)
    AND TRANSLATE(C.last_name, '.,', ' ') =
        TRANSLATE(P.last_name, '.,', ' ')
    AND REPLACE(C.company_name, 'L.L.C.', 'LLC') =
        REPLACE(P.company_name, 'L.L.C.', 'LLC')
    AND LEFT(C.address, 10) = LEFT(P.address, 10)
    AND LEFT(C.zip, 5) = LEFT(P.zip, 5)
    AND REPLACE(C.phone1, '-', '') =
        REPLACE(P.phone1, '-', '')
    AND REPLACE(C.phone2, '-', '') =
        REPLACE(P.phone2, '-', '')
    AND C.email = P.email;

Это удивительно, если задуматься. Нам понадобились все эти маневры, чтобы найти хотя бы одну строку, достаточно близкую к точному совпадению, которое, очевидно, таковым не является, по крайней мере формально в отношении данных. Но с семантической точки зрения, вероятно, да, это совпадение. Перейдем от подсчета к атрибутам из таблицы Customer (псевдоним C) и посмотрим, что это за совпадение:

/*
   Что же это за таинственное совпадение?
*/
SELECT
    'CRM Data' Source,
    C.first_name,
    C.last_name,
    C.company_name,
    C.address,
    C.zip,
    C.phone1,
    C.phone2,
    C.email
FROM crm.Customer C
INNER JOIN dbo.PotentialMatches P ON
        LEFT(C.first_name, 1) = LEFT(P.first_name, 1)
    AND TRANSLATE(C.last_name, '.,', ' ') =
        TRANSLATE(P.last_name, '.,', ' ')
    AND REPLACE(C.company_name, 'L.L.C.', 'LLC') =
        REPLACE(P.company_name, 'L.L.C.', 'LLC')
    AND LEFT(C.address, 10) = LEFT(P.address, 10)
    AND LEFT(C.zip, 5) = LEFT(P.zip, 5)
    AND REPLACE(C.phone1, '-', '') =
        REPLACE(P.phone1, '-', '')
    AND REPLACE(C.phone2, '-', '') =
        REPLACE(P.phone2, '-', '')
    AND C.email = P.email
UNION
/*
   А как оно выглядит в данных импорта?
*/
SELECT
    'Import Data',
    P.first_name,
    P.last_name,
    P.company_name,
    P.address,
    P.zip,
    P.phone1,
    P.phone2,
    P.email
FROM crm.Customer C
INNER JOIN dbo.PotentialMatches P ON
        LEFT(C.first_name, 1) = LEFT(P.first_name, 1)
    AND TRANSLATE(C.last_name, '.,', ' ') =
        TRANSLATE(P.last_name, '.,', ' ')
    AND REPLACE(C.company_name, 'L.L.C.', 'LLC') =
        REPLACE(P.company_name, 'L.L.C.', 'LLC')
    AND LEFT(C.address, 10) = LEFT(P.address, 10)
    AND LEFT(C.zip, 5) = LEFT(P.zip, 5)
    AND REPLACE(C.phone1, '-', '') =
        REPLACE(P.phone1, '-', '')
    AND REPLACE(C.phone2, '-', '') =
        REPLACE(P.phone2, '-', '')
    AND C.email = P.email
ORDER BY 1;

Видим небольшие различия между данными CRM и импорта:

/*
   Выполним diff.
*/
SELECT
    C.address [Customer Address],
    P.address [Import Address],
    C.zip [Customer ZIP],
    P.zip [Import ZIP]
FROM crm.Customer C
INNER JOIN dbo.PotentialMatches P ON
        LEFT(C.first_name, 1) = LEFT(P.first_name, 1)
    AND TRANSLATE(C.last_name, '.,', ' ') =
        TRANSLATE(P.last_name, '.,', ' ')
    AND REPLACE(C.company_name, 'L.L.C.', 'LLC') =
        REPLACE(P.company_name, 'L.L.C.', 'LLC')
    AND LEFT(C.address, 10) = LEFT(P.address, 10)
    AND LEFT(C.zip, 5) = LEFT(P.zip, 5)
    AND REPLACE(C.phone1, '-', '') =
        REPLACE(P.phone1, '-', '')
    AND REPLACE(C.phone2, '-', '') =
        REPLACE(P.phone2, '-', '')
    AND C.email = P.email;

Отлично, но мы далеки от завершения.

Более подробно с книгой можно ознакомиться на сайте издательства:

» Оглавление
» Отрывок

По факту оплаты бумажной версии книги на e-mail высылается электронная книга.
Для Хаброжителей скидка 25% по купону — SQL

© Habrahabr.ru