sql - PostgreSQL中的全文搜索，根据每个关键字的不同分数进行自定义排名

问题描述

有什么方法可以ts_rank在 PostgreSQL 中“扩展”功能或创建自定义setweight？

我有 2 个表，records并且tags，records可以有多个标签。使用 table 是多对多关联records_tags。records_tags有列score，这意味着每个记录的相同标签的分数是不同的，并且在 PostgreSQL 的 setweight 中，除了 4 个级别之外，还有更多的级别可用作权重。

简化数据示例：

records 桌子

 id |         title          |             description              | privacy
----+------------------------+--------------------------------------+---------
  1 | 'The best record ever' | 'Long and meaningful description...' |       1
  2 |       'Another record' | 'Description of the other record...' |       2

tags 桌子

 id |           name
----+---------------------------
  1 | 'artificial intelligence'
  2 |        'machine learning'
  3 |            'life science'

records_tags桌子

 record_id | tag_id | score
-----------+--------+-------
         1 |      1 |    87
         1 |      2 |    23
         2 |      1 |    54
         2 |      2 |    67
         2 |      3 |    90

来自这些表的数据合并到另一个表中，该表search_documents的列body类型为 jsonb，并包含每条记录的聚合标记名称。

search_documents.body看起来像这样：

{
  title: 'The best record ever',
  description: 'Long and meaningful description...',
  tags: ['artificial intelligence', 'machine learning']
}

现在我已经使用 tsvector 和 setweight 实现了全文搜索，如下所示：

setweight(to_tsvector('simple', (body ->> 'tags')), 'A') || ' ' ||
setweight(to_tsvector('english', (body ->> 'title')), 'B') || ' ' ||
setweight(to_tsvector('english', (body ->> 'description')), 'C')

并像这样搜索查询：

SELECT
  ts_rank(sd.tsv, to_tsquery('english', ''' ' || :query || ' ''' || ':*'), 1) +
  ts_rank(sd.tsv, to_tsquery('simple', ''' ' || :query || ' ''' || ':*'), 1) AS rank
  sd.id AS id
FROM
  search_documents sd
WHERE
  sd.tsv @@ to_tsquery('english', ''' ' || :query || ' ''' || ':*') OR
  sd.tsv @@ to_tsquery('simple', ''' ' || :query || ' ''' || ':*')

但它根本不允许我使用标签的分数。

我的想法是我有一个标签分数的归一化函数，它返回从 0 到 1 范围内的分数，我用它来乘以 A 权重。它看起来像这样 -(x_i − min(x)) / (max(x) − min(x))

score在计算时，除了当前的实现之外，还有什么方法可以使用标签rank？

编辑：

这search_documents是一个表，不是物化视图，并且在搜索过程开始时具有（或者如果必须添加某些东西以使其工作，则将具有）所有数据。它包含我想要搜索的所有项目，不仅包括其他项目，还包括其他项目records-accounts和speakers. 当源表更新时，search_documents. 还有隐私列，因为每个用户都有不同的权限，我不希望他们在无法访问时看到搜索结果中的项目。

search_documents表格示例：

 tsv | searchable_id | searchable_type | privacy | body
-----+---------------+-----------------+---------+-----------------------------------------------------
 ... |             1 |        'record' |       1 | { title: '...', description: '...', tags: ['...'] }
 ... |             1 |       'account' |       1 | { name: '...', description: '...' }
 ... |             1 |       'speaker' |       1 | { name: '...', description: '...' }

tsv是在插入/更新表时使用触发器创建的 ts_vector。它是这样创建的：

IF NEW.searchable_type = 'record' THEN
  NEW.tsv := (
    setweight(to_tsvector('simple', (NEW.body ->> 'tags')), 'A') || ' ' ||
    setweight(to_tsvector('english', (NEW.body ->> 'title')), 'B') || ' ' ||
    setweight(to_tsvector('english', (NEW.body ->> 'description')), 'C')
  )::tsvector;
ELSE
  NEW.tsv := (
    setweight(to_tsvector('simple', (NEW.body ->> 'name')), 'A') || ' ' ||
    setweight(to_tsvector('english', (NEW.body ->> 'description')), 'C')
  )::tsvector;
END IF;
return NEW;

这就是在records 以下位置创建数据的方式search_documents：

SELECT GREATEST(MAX(r.privacy), MAX(f.privacy), MAX(a.privacy)) AS privacy,
  'record' AS searchable_type,
  r.id AS searchable_id,
  json_build_object(
    'tags', array_remove(array_agg(t.name), NULL),
    'title', r.title,
    'description', r.description
  ) AS body
FROM records r
  LEFT JOIN folders f ON r.folder_id = f.id
  LEFT JOIN accounts a ON r.account_id = a.id
  LEFT JOIN records_tags rt ON r.id = rt.record_id
  LEFT JOIN tags t ON rt.tag_id = t.id
WHERE r.id = :id
GROUP BY searchable_id
ON CONFLICT(searchable_type, searchable_id)
  DO UPDATE
    SET privacy = EXCLUDED.privacy,
        body = EXCLUDED.body

标签： sqlpostgresqlsearchfull-text-search

sql - PostgreSQL中的全文搜索，根据每个关键字的不同分数进行自定义排名

问题描述

解决方案

推荐阅读