postgresql 如何使用pg_trgm按相似值分组

u3r8eeie  于 2022-12-03  发布在  PostgreSQL
关注(0)|答案(2)|浏览(279)

我有下表

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
jhkqcmku

jhkqcmku1#

基本上你可以将一个表与它自身连接起来,以找到相似的字符串,但是这种方法在一个更大的数据集上的查询速度会非常慢。而且,使用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

基于OP的想法的解决方案(见下面的注解)。similarity()的极限0.8肯定太高了。似乎应该在0.6左右。

  • unique* 错误的表(我使用了临时表,当然也可以是常规表):
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)
raogr8fs

raogr8fs2#

对于这种特殊情况,您可以只按left(error,5)分组,这将导致两个组,一个组包含所有以Error开头的字符串,另一个组包含所有以Fatal开头的字符串。如果您计划添加更多错误类型,则必须更新此条件。

相关问题