postgresql - 两个 ts 向量列之间的 ts_rank

Question

我在两个表中有两列，我想选择前 20% 最相关的术语

Table A: items
id, 
title (string)

Table B: queries
id,
query (string)

基本上，我想找到 ts_vector(items.title) 和 ts_vector(queries.query)

的前 20% 比较排名

Answer 1

您可以使用 ts_rank_cd(tsquery, tsvector, int) 获取文本接近度。我用您的列创建了两个表 a 和 b 并在上面放了信息。 ts_rank_cd 函数接收一个要归一化的因子，在文档中，32 是要在 0 和 1 之间归一化的值。它不被视为 0% 到 100% 的索引，但它被认为是接近度的一个很好的因素，如 text search controls 中所述。 .

表一:

postgres=# select * from a;
 id |       title        
----+--------------------
  1 | a rat and a cat
  2 | a cat and a mouse
  3 | a tiger or a rhino
(3 rows)

表b:

postgres=# select * from b;
 id |      query      
----+-----------------
  4 | lorem ipsum
  3 | lorem cat
  2 | mouse rhino
  1 | a rat and a cat
  5 | rat fat cat
(5 rows)

需要使用 ts_rank_cd(tsvector, tsquery) 执行比较，因此，我们将第一个 a.title 转换为 tsvector 和 b.query 和 tsquery 进行比较

postgres=# select ts_rank_cd(to_tsvector(title), query, 32) as rank, 
                  title,
                  query 
             from a, 
                  (select plainto_tsquery('english', query) as query,
                          id
                     from b
                  ) AS b ORDER BY rank DESC; 

   rank    |       title        |         query         
-----------+--------------------+-----------------------
 0.0322581 | a rat and a cat    | 'rat' & 'cat'
         0 | a rat and a cat    | 'lorem' & 'cat'
         0 | a rat and a cat    | 'mous' & 'rhino'
         0 | a rat and a cat    | 'rat' & 'fat' & 'cat'
         0 | a cat and a mouse  | 'lorem' & 'ipsum'
         0 | a cat and a mouse  | 'lorem' & 'cat'
         0 | a cat and a mouse  | 'mous' & 'rhino'
         0 | a cat and a mouse  | 'rat' & 'cat'
         0 | a cat and a mouse  | 'rat' & 'fat' & 'cat'
         0 | a tiger or a rhino | 'lorem' & 'ipsum'
         0 | a tiger or a rhino | 'lorem' & 'cat'
         0 | a tiger or a rhino | 'mous' & 'rhino'
         0 | a tiger or a rhino | 'rat' & 'cat'
         0 | a rat and a cat    | 'lorem' & 'ipsum'
         0 | a tiger or a rhino | 'rat' & 'fat' & 'cat'
(15 rows)

如果你想获得更“宽松”的结果，可以将&替换为|，只是做一个替换。

postgres=# select ts_rank_cd(to_tsvector(title), replace(query::text, '&', '|')::tsquery, 32) as rank, 
                 title::tsvector,
                 replace(query::text, '&', '|')::tsquery from a, (select 
                 plainto_tsquery('english', query) as query, id from b) AS  b ORDER BY rank DESC; 
   rank    |          title           |        replace        
-----------+--------------------------+-----------------------
  0.166667 | 'a' 'and' 'cat' 'rat'    | 'rat' | 'cat'
  0.166667 | 'a' 'and' 'cat' 'rat'    | 'rat' | 'fat' | 'cat'
 0.0909091 | 'a' 'or' 'rhino' 'tiger' | 'mous' | 'rhino'
 0.0909091 | 'a' 'and' 'cat' 'rat'    | 'lorem' | 'cat'
 0.0909091 | 'a' 'and' 'cat' 'mouse'  | 'lorem' | 'cat'
 0.0909091 | 'a' 'and' 'cat' 'mouse'  | 'rat' | 'cat'
 0.0909091 | 'a' 'and' 'cat' 'mouse'  | 'mous' | 'rhino'
 0.0909091 | 'a' 'and' 'cat' 'mouse'  | 'rat' | 'fat' | 'cat'
         0 | 'a' 'or' 'rhino' 'tiger' | 'rat' | 'fat' | 'cat'
         0 | 'a' 'and' 'cat' 'rat'    | 'mous' | 'rhino'
         0 | 'a' 'and' 'cat' 'mouse'  | 'lorem' | 'ipsum'
         0 | 'a' 'or' 'rhino' 'tiger' | 'lorem' | 'ipsum'
         0 | 'a' 'or' 'rhino' 'tiger' | 'lorem' | 'cat'
         0 | 'a' 'or' 'rhino' 'tiger' | 'rat' | 'cat'
         0 | 'a' 'and' 'cat' 'rat'    | 'lorem' | 'ipsum'
(15 rows)