Как сгруппировать по сходным значениям с pg_trgm

У меня есть следующая таблица

id error
-  ----------------------------------------
1  Error 1234eee5, can not write to disk
2  Error 83457qwe, can not write to disk
3  Error 72344ee, can not write to disk
4  Fatal barier breach on object 72fgsff
5  Fatal barier breach on object 7fasdfa
6  Fatal barier breach on object 73456xcc5

Я хочу иметь возможность получить результат, который считается по сходству, где сходство> 80% означает, что две ошибки равны. Я использовал расширение pg_trgm, и его функция сходства отлично работает для меня, единственное, что я могу выяснить, как получить результат группировки ниже.

Error                                  Count
-------------------------------------  ------
Error 1234eee5, can not write to disk, 3
Fatal barier breach on object 72fgsff, 3
Источник

2 ответа

Решение

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

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

select id, regexp_replace(error, '\d\w+', 'xxxxx') as error
from errors;

 id |                error                
----+-------------------------------------
  1 | Error xxxxx, can not write to disk
  2 | Error xxxxx, can not write to disk
  3 | Error xxxxx, can not write to disk
  4 | Fatal barier breach on object xxxxx
  5 | Fatal barier breach on object xxxxx
  6 | Fatal barier breach on object xxxxx
(6 rows)

так что вы можете легко сгруппировать данные по сообщению об ошибке:

select regexp_replace(error, '\d\w+', 'xxxxx') as error, count(*)
from errors
group by 1;

                error                | count 
-------------------------------------+-------
 Error xxxxx, can not write to disk  |     3
 Fatal barier breach on object xxxxx |     3
(2 rows)

Приведенный выше запрос является лишь примером, поскольку конкретное решение зависит от формата данных.

Использование pg_trgm

Решение основано на идее ОП (см. Комментарии ниже). Предел 0,8 для similarity() конечно слишком высоко. Кажется, это должно быть где-то около 0,6.

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

create temp table if not exists unique_errors(
    id serial primary key, 
    error text, 
    ids int[]);

ids колонка для хранения id строк базовой таблицы, которые содержат похожие ошибки.

do $$
declare
    e record;
    found_id int;
begin
    truncate unique_errors;
    for e in select * from errors loop
        select min(id)
        into found_id
        from unique_errors u
        where similarity(u.error, e.error) > 0.6;
        if found_id is not null then
            update unique_errors
            set ids = ids || e.id
            where id = found_id;
        else
            insert into unique_errors (error, ids)
            values (e.error, array[e.id]);
        end if;
    end loop;
end $$;

Окончательные результаты:

select *, cardinality(ids) as count
from unique_errors;

 id |                 error                 |   ids   | count 
----+---------------------------------------+---------+-------
  1 | Error 1234eee5, can not write to disk | {1,2,3} |     3
  2 | Fatal barier breach on object 72fgsff | {4,5,6} |     3
(2 rows)
Источник

В этом конкретном случае вы можете просто сгруппировать по левому (ошибка, 5), что приведет к двум группам, одна из которых будет содержать все строки, начинающиеся с Error, а другая — все строки, начинающиеся с Fatal. Эти критерии необходимо будет обновить, если вы планируете добавить больше типов ошибок.

Источник
Другие вопросы по тегам