Блог

Главная - Вопросы по программированию - Кластеризация / сходство между текстовыми ячейками в агрегате postgres

Кластеризация / сходство между текстовыми ячейками в агрегате postgres

#sql #string #postgresql #group-by #count

#sql #строка #postgresql #группировать по #количество

Вопрос:

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

 CREATE TABLE data_test (
Id serial primary key,
Name VARCHAR(70) NOT NULL,
Job VARCHAR(100) NOT NULL);

INSERT INTO data_test
    (Name, Job)
VALUES
    ('John', 'Astronaut'),
    ('John', 'Astronaut'),
    ('Ann', 'Sales'),
    ('Jon', 'Astronaut'),
    ('Jason', 'Sales'),
    ('Pranav', 'Sales'),
    ('Todd', 'Sales'),
    ('John', 'Astronaut');

Я хотел бы выполнить запрос, который был бы чем-то вроде:

 select 
  Job,
  count(Name),
  Similarity_Agg(Name)
from data_test
group by Job;

и получаем

 Job        count   Similarity 
Sales      4       0.1 
Astronaut  4       0.9

По сути, это показывает, что имена астронавтов очень похожи (или, что более вероятно в моих данных, все строки относятся к одному астронавту), а названия продавцов — нет (в продажах работает больше людей, чем в космосе). Я вижу, что есть модуль Postgres, который может обрабатывать сравнение двух строк, но, похоже, в нем нет никаких агрегатных функций.

Есть идеи?

Ответ №1:

Одним из вариантов является самосоединение:

 select 
    d.job,
    count(distinct d.id) cnt,
    avg(similarly(d.name, d1.name)) avg_similarity
from data_test d
inner join data_test d1 on d1.job = d.job
group by d.job

Комментарии:

1. Это выглядит многообещающе… просто чтобы проверить мое понимание, самосоединение приведет к затратам n ^ 2 на вычисление сходства pg_trgm? В большинстве случаев это не должно быть проблемой, поскольку в большинстве случаев мои данные будут содержать 2 или 3 элемента в группе, а всего около 1 тыс.

2. Я думаю, что min это был бы лучший агрегат, чем avg для определения, является ли группа однородной.

3. Это работает. Я также думаю, что использование медианы или минимального значения вместо среднего может быть лучшей идеей, но это зависит от варианта использования, который я рассматриваю. В принципе, все, что не равно 1, вызывает беспокойство.