主页 / dba / 问题 / 313548
Accepted
Jared
Asked: 2022-06-20 20:46:35 +0800 CST

如何在子查询中删除重复项和排序？

  • 772

我有一个“标题”列的记录，我按空格分割并用每个单词执行全文搜索。我将结果存储在物化视图中。

这行得通，但是我得到了各种单词的重复结果，我需要按它们的排名对结果进行排序。我可以做一个或另一个 - 不能两者兼而有之。我该怎么做？

我的查询：

SELECT
    asset.id,
    (
        select
            jsonb_agg(resultsForWord)
        FROM
            UNNEST(
                string_to_array(TRIM(regexp_replace(asset.title, '[^a-zA-Z+]', ' ', 'g')), ' ')
            ) as word
            INNER JOIN LATERAL 
            (
                SELECT
                    searchresult.id,
                    searchresult.title,
                    ts_rank(ts, to_tsquery ('english', word)) rank
                FROM
                    assets searchresult
                WHERE
                    searchresult.id != asset.id AND
                    ts_rank(ts, to_tsquery ('english', word)) > 0.5
                LIMIT 5
            ) AS resultsForWord ON 1=1
     ) results
FROM
    assets asset
WHERE asset.id = 'abc'
GROUP BY asset.id;

为了过滤掉我刚刚做的重复

jsonb_agg(DISTINCT resultsForWord)

按等级排序，我刚刚做了

jsonb_agg(resultsForWord ORDER BY rank DESC)

当我两者都做时，我得到：

ERROR: in an aggregate with DISTINCT, ORDER BY expressions must appear in argument list

示例数据：

CREATE TABLE assets (
  id TEXT PRIMARY KEY,
  title TEXT,
  ts tsvector 
   GENERATED ALWAYS AS (setweight(to_tsvector('english', coalesce(title, '')), 'A')) STORED
)

INSERT INTO assets (id, title) VALUES ('a', 'Hello world!'),
  ('b', 'Hello sir'),
  ('c', 'I am above the world'),
  ('d', 'World hello')
postgresql full-text-search

2 个回答

  1. Best Answer

    似乎你应该翻转加入的顺序，UNNEST这样你最多只能加入一行。

    您也可以删除外部GROUP BY. 好像没必要

    SELECT
    asset.id,
    (
        select
            jsonb_agg(results ORDER BY results.rank DESC)
        FROM (
            SELECT
                searchresult.id,
                searchresult.title,
                resultsForWord.rank
            FROM
                assets searchresult
            CROSS JOIN LATERAL 
            (
                SELECT ts_rank(ts, to_tsquery ('english', word)) rank
                FROM UNNEST(
                    string_to_array(TRIM(regexp_replace(asset.title, '[^a-zA-Z+]', ' ', 'g')), ' ')
                ) as word
                WHERE ts_rank(ts, to_tsquery ('english', word)) > 0.5
                ORDER BY rank DESC
                LIMIT 1
            ) AS resultsForWord
            WHERE
                searchresult.id != asset.id
            ORDER BY rank DESC
            LIMIT 5
        ) results
     ) results
FROM
    assets asset
WHERE asset.id = 'a';

    db<>小提琴

    • 1

  2. 由于id是PRIMARY KEY，因此在外部查询中只能有一个匹配项WHERE a.id = 'abc'，因此GROUP BY绝对不需要外部（就像查理已经建议的那样）。

    可以在多个位置引入像您报告的“重复结果”：

    1. 拆分title会产生重复的单词
    2. 多个（不同的）单词可以匹配同一行

    尽早删除受骗者。

    这看起来非常复杂：

    unnest(string_to_array(trim(regexp_replace(a.title, '[^a-zA-Z+]', ' ', 'g')), ' '))

    regexp_split_to_table()改为考虑：

    regexp_split_to_table(a.title, '[^a-zA-Z]+')

    （我建议你想要'[^a-zA-Z]+'而不是'[^a-zA-Z+]'。）

    唯一的缺点：可能会产生前导或尾随的空字符串，但可以通过一个WHERE子句廉价地消除这些字符串。

    所以，我认为，你宁愿想要这个查询：

    SELECT a.id
    , (  SELECT jsonb_agg(resultsforword)
         FROM  (
            SELECT *
            FROM  (
               SELECT DISTINCT ON (r.id)
                      r.id, r.title, r.rank
               FROM  (
                  SELECT word        -- remove duplicate words early
                  FROM   regexp_split_to_table(a.title, '[^a-zA-Z]+') word
                  WHERE  word <> ''  -- trim possible leading / trailing ''
                  ) w
               CROSS  JOIN LATERAL (
                  SELECT s.id, s.title
                       , ts_rank(s.ts, to_tsquery('english', w.word)) AS rank
                  FROM   assets s
                  WHERE  s.id <> a.id
                  AND    ts_rank(s.ts, to_tsquery('english', w.word)) > 0.5
                  ORDER  BY rank DESC
                  LIMIT  5                  -- max. 5 best matches per word
                  ) r
               ORDER  BY r.id, r.rank DESC  -- take best rank for each dupe result
               ) r
            ORDER  BY r.rank DESC, r.id     -- best rank overall, id as tiebreaker
            LIMIT  5                        -- max 5 overall 
            ) resultsforword
     ) AS results
FROM   assets a
WHERE  a.id = 'e';

    db<>在这里摆弄

    这将获得所选标题中任何单词的 5 个最佳匹配项。现在看起来更复杂了，但我们必须尽职尽责地运作：

    1. 从选定的 中提取单词title。
    2. 每个单词获得最佳（最多）5 个匹配项。这可以多次匹配同一行（具有不同的等级）。
    3. 为每个结果行获得最佳排名 - 如果同一行匹配多个单词。
    4. 获得排名最高的 5 行。

    看：

    • 0

相关问题