时间戳

首页 > 解决方案 > 在 Postgres 中对搜索结果进行排名

sql - 在 Postgres 中对搜索结果进行排名

问题描述

这可能有一个非常简单的解决方案，但我刚刚开始学习，所以我不知道。

我有一个包含一些化学数据的数据库，其中有 2 列感兴趣的搜索：

  1. 复合名称 -> 每个有一个复合名称（1 到 5 个单词）
  2. 每个中的复合同义词（最多 200 个不同的词）

1 包含化合物的名称，2 包含化合物的同义词。我用这个为这两列创建了一个向量列：

   alter table compound
    add column document_vector_weights tsvector;
   update compound
   set document_vector_weights = setweight(to_tsvector(coalesce(cmpdname, '')), 'A') ||
    setweight(to_tsvector(cmpdsynonym), 'D');
   CREATE INDEX document_weights_index
        on compound
        USING GIN (document_vector_weights)

如您所见，我希望化合物的名称具有最大的权重。

问题是，即我正在搜索术语“丙烯”，它是一种化合物，我得到以下结果：

结果表

“丙二醇”和“碳酸丙烯酯”出现在“丙烯”本身之前。发生这种情况是因为“丙烯”一词多次出现在这两种化合物的同义词栏中，即使它们的重量是“D”。

我想提出一个条件——>如果确切的搜索词出现在“名称”列中，那么无论它在其他列中出现多少次，它都应该是排名靠前的那个。

有没有办法可以做到这一点？

编辑：

示例数据 - 此处“|||” 是一个列分隔符 这是我在搜索该术语时得到的结果，Propylene它位于第 3 位。我希望它位于第 1 位。

compounddname ||| compoundsynonym
"Propylene glycol" ||| 1,2-propanediol|propylene glycol|propane-1,2-diol|57-55-6|1,2-Propylene glycol| ...etc
"Propylene carbonate" ||| PROPYLENE CARBONATE|108-32-7|4-Methyl-1,3-dioxolan-2-one|1,2-Propylene carbonate|1,2-Propanediol cyclic carbonate|...etc
"Propylene" ||| PROPYLENE|Propene|1-Propene|Methylethylene|prop-1-ene|Methylethene|1-Propylene|...etc
"1-Methoxy-2-propanol" ||| 1-Methoxy-2-propanol|107-98-2|1-Methoxypropan-2-ol|Methoxyisopropanol|PGME|...etc
"Poloxalene" ||| Poloxalene|Poloxamer 188|Pluronic|Poloxalkol|9003-11-6|Poloxamer 407|Pluronic L 61|...etc

compoundsynonym正如您在前两行（“丙二醇”和“碳酸丙烯酯”的同义词）中看到的那样，“丙烯”一词在该列中出现了多次。这会影响排名结果。

我想要的 => 如果我搜索该术语Propylene并在compoundname列中找到完全匹配，则称重系统不应影响其排名，它应该具有排名 1，因为它与任何化合物的名称完美匹配。现在它排名第 3。发生这种情况是因为该术语在排名前两个化合物的列中Propylene多次可用。compoundsynonym

您可以查看我是否复制并粘贴第一个同义词单元格，它看起来像这样： 在此处输入图像描述

标签： sqlpostgresql

解决方案

我找到了解决此问题的方法，方法是在对结果进行排名时更改 A、B、C 和 D 的默认值。我是这样做的：

 select *, ts_rank( '{0,0,0.10,1}', document_vector_weights, plainto_tsquery('propylene')) as rank_a
 from compound where document_vector_weights @@ plainto_tsquery('propylene')
 order by rank_a desc;

这里{0,0,0.10,1}分别是 D、C、B 和 A 的值。我减少了 to 的值，D因此0化合物的名称将具有最大的权重。

推荐阅读