массив_агг значений с расстоянием Жарова-Винклера >= N

Содержание

Вопрос или проблема
Ответ или решение
Объединение схожих значений имен клиентов с использованием расстояния Джаро-Уинклера в PostgreSQL
Алгоритм объединения
SQL Реализация
Объяснение кода:
Заключение

Вопрос или проблема

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

with data(names) as (
    select * from values
        ('Название другой компании'), 
        ('другое название компании'), 
        ('ДРУГАЯ КОМПАНИЯ name, LLC')
)

Я хотел бы объединить схожие имена на основе расстояния по методу Яро-Уинклер. Я могу сделать это, обработав каждое имя клиента и сопоставив его на основе расстояния J-W, а затем объединив в некоторые массивы:

similars as (
    select 
    distinct a.names as custname1, b.names custname2
    from data a, data b
    where jarowinkler_similarity(regexp_replace(lower(trim(a.names)), '[^a-z0-9]'), regexp_replace(lower(trim(b.names)),'[^a-z0-9]')) >= 95
    order by 1
), 

tmp as (
    select 
    distinct array_sort(similar_cust_names) names
    from (
        select
        distinct custname1, array_distinct(array_append(array_agg(custname2) within group(order by custname2), custname1)) similar_cust_names
        from similars
        group by 1
    )
)

select
*
from tmp

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

Как я могу программно объединить все значения в один массив, отбросив любые дубликаты или почти дубликаты? Обратите внимание, что расстояние JW между ДРУГАЯ КОМПАНИЯ name, LLC и другое название компании меньше 95, но поскольку каждый из них имеет расстояние JW больше или равно 95 к Название другой компании, они должны появиться в одном массиве.

Ответ или решение

Объединение схожих значений имен клиентов с использованием расстояния Джаро-Уинклера в PostgreSQL

Ваша задача заключается в том, чтобы объединить имена клиентов, которые имеют схожесть по расстоянию Джаро-Уинклера (Jaro-Winkler Distance) выше указанного порога, в одну массивную структуру. Мы рассмотрим, как можно эффективно программно объединить все значения в один массив, удаляя дублирующие или близкие к дублированию записи.

Алгоритм объединения

Идентификация похожих имен:
Сначала мы должны получить пары имен, где расстояние Джаро-Уинклера превышает заданное значение, например, 95. Это позволит нам установить связи между схожими именами.
Создание групп схожих имен:
После того как мы получили все схожие пары имен, нам необходимо сгруппировать их таким образом, чтобы все схожие названия оказывались в одной группе. Это можно сделать путем выполнения слияния связанных имен, что позволяет устранить дублирование.
Построение итогового массива:
На финальном этапе мы объединяем все уникальные имена в один массив, обеспечивая отсутствие дубликатов.

SQL Реализация

Прежде чем перейти к окончательному запросу, вы можете использовать Common Table Expressions (CTE) для обработки ваших данных. Давайте рассмотрим, как это может быть реализовано в SQL:

WITH RECURSIVE similar_groups AS (
    -- Начинаем с выборки всех пар схожих имен
    SELECT 
        custname1,
        array_agg(custname2) AS similar_names
    FROM (
        SELECT DISTINCT 
            a.names AS custname1, 
            b.names AS custname2
        FROM data a, data b
        WHERE jarowinkler_similarity(
                regexp_replace(lower(trim(a.names)), '[^a-z0-9]', ''), 
                regexp_replace(lower(trim(b.names)), '[^a-z0-9]', '')
            ) >= 95
    ) AS similars
    GROUP BY custname1

    UNION ALL

    -- Рекурсивно объединяем все схожие группы
    SELECT 
        sg.custname1,
        array_agg(DISTINCT sg2.custname2) AS similar_names
    FROM similar_groups sg
    JOIN (
        SELECT 
            a.names AS custname1, 
            b.names AS custname2
        FROM data a, data b
        WHERE jarowinkler_similarity(
                regexp_replace(lower(trim(a.names)), '[^a-z0-9]', ''),
                regexp_replace(lower(trim(b.names)), '[^a-z0-9]', '')
            ) >= 95
    ) AS sg2 ON sg.similar_names @> ARRAY[sg2.custname1]
    GROUP BY sg.custname1
)

SELECT DISTINCT 
    array_sort(array_agg(DISTINCT unnest(similar_names))) AS combined_names
FROM similar_groups;

Объяснение кода:

CTE: similar_groups используется для начального выбора всех имен с расстоянием Джаро-Уинклера выше 95. Затем мы выполняем рекурсивное объединение для группировки всех связанных имен.
UNION ALL: Этот оператор используется для объединения текущих групп с новыми похожими именами на этапе рекурсии.
array_agg и unnest: В конце мы собираем все уникальные имена в один общий массив без повторений.

Заключение

Таким образом, вы получите один массив с именами клиентов, которые имеют Jaro-Winkler расстояние >= N, исключая дубликаты и обеспечивая слияние близких вариантов. Это решение эффективно справляется с вашей задачей по оптимизации данных клиентов, делая их легко доступными для дальнейшего анализа или отчетности.