Postgres pg_trgm如何比较字符串数组的相似性

Question

我正试图使用pg_trgm进行字符串模糊匹配，我知道它可以这样使用:

SELECT * FROM artists WHERE SIMILARITY(name, 'Claud Monay') > 0.4;

，其中标量值可用于比较相似性。但是，我已经看到了使用SIMILARITY和字符串数组的这种方式:

SELECT * FROM artists WHERE 'Cadinsky' % ANY(STRING_TO_ARRAY(name, ' '));

使用%操作符，这是与0.3的默认值进行比较的简写。我试图找到正确的语法来使用ANY(STRING_TO_ARRAY(...))，但与第一种形式，其中可以给出任意标量值来比较相似性。

这很可能只是正确使用ANY语法的一个简单问题，但我无法理解正确的形式是什么。

Answer 1

没有语法可以使用带有3个参数的ANY(字符串、字符串数组和相似性阈值)。这样做的方法是设置pg_trgm。将similarity_threshold设置为您想要的值，而不是默认值0.3，然后使用% ANY。

如果您想在查询的不同部分使用不同的阈值，那么使用ANY构造就不太合适了。

你总是可以定义你自己的函数，但是你可能无法让它使用索引。

create or replace function most_similar(text, text[]) returns double precision 
language sql as $$ 
select max(similarity($1,x)) from unnest($2) f(x) 
$$;
SELECT * FROM artists WHERE most_similar('Cadinsky', STRING_TO_ARRAY(name, ' '))>0.4;

Answer 2

我不是数据库专家，也不擅长SQL，但这是我的解决方案。

我基本上使用了一个叫做unnest()的函数。因此，我可以遍历数组并检查每个项目的相似性值，然后将其与相似性进行比较。输入，它是一个float。

使用set pg_trgm.similarity_threshold=0.6;之类的东西是一个全局设置，据我所知。这个问题特别要求一个显式阈值.

同样，如果您创建了一个函数来完成这项工作，并且该函数不是VOLATILE而是STABLE，则不能使用set pg_trgm.similarity_threshold。(至少这是发生在我身上的事)。

注意:我没有比较我的方法(ANY)方法在性能方面。

示例代码:

CREATE OR REPLACE FUNCTION your_function_name (input text, similarity float) RETURNS
SELECT * FROM your_table_name
WHERE EXISTS
(SELECT
FROM unnest(ARRAY['item','anotherItem', 'third-ish']) element
WHERE SIMILARITY (input, element) > similarity
);
$ function $