Sql 获取每个帖子的匹配关键字列表

不支持索引的简单正则表达式

没有索引支持(只有对琐碎的基数是合理的！)这是Postgr最快的，同时做exactly个你要求的:

SELECT post_id, COALESCE(string_agg(topic_id::text, ',' ), 'Vague!') AS topic
FROM  (
   SELECT p.post_id, k.topic_id
   FROM   post p 
   LEFT   JOIN keyword k ON p.content ~* ('\m' || k.keyword || '\M')
   ORDER  BY p.post_id, k.topic_id
   ) sub
GROUP  BY post_id
ORDER  BY post_id;

~*是不区分大小写的正则表达式匹配运算符.参见:

• LOWER LIKE vs iLIKE

关于我的正则表达式，我引用the manual:

\m ... matches only at the beginning of a word
\M ... matches only at the end of a word

这隐含地覆盖了字符串的开始(^)和结束($).\W(如另一个答案中所建议的)匹配任何非单词字符，对于任务来说是错误的.

请注意我如何在子查询中应用一次ORDER BY而不是每个聚合，因为这样更快.参见:

• How to combine ORDER BY and LIMIT with an aggregate function?

在这个星座中，一个简单的COALESCE捕捉到没有匹配的情况.

严格匹配的FTS索引

上面的简单(朴素)方法以O(N * M)为尺度，也就是说，每个表中的行数非常不平凡.通常，您需要索引支持.

虽然严格匹配关键字，但最好的索引应该是100 with the 101 dictionary，以及一个可以实际使用该索引的查询:

CREATE INDEX post_content_fts_simple_idx ON post USING gin (to_tsvector('simple', content));


SELECT post_id, COALESCE(topics, 'Vague!') AS topics
FROM  (
   SELECT post_id, string_agg(topic_id::text, ',') AS topics
   FROM  (
      SELECT p.post_id, k.topic_id
      FROM   keyword k
      JOIN   post    p ON to_tsvector('simple', p.content) @@ to_tsquery('simple', k.keyword)
      ORDER  BY p.post_id, k.topic_id
      ) sub
   GROUP  BY post_id
   ) sub1
RIGHT  JOIN post p USING (post_id)
ORDER  BY post_id;

英文单词匹配FTS索引

要使用内置词干匹配自然语言单词，请在示例中使用匹配dictionary, 100:

CREATE INDEX post_content_fts_en_idx ON post USING gin (to_tsvector('english', content));

SELECT post_id, COALESCE(topics, 'Vague!') AS topics
FROM  (
   SELECT post_id, string_agg(topic_id::text, ',') AS topics
   FROM  (
      SELECT p.post_id, k.topic_id
      FROM   keyword k
      JOIN   post    p ON to_tsvector('english', p.content) @@ to_tsquery('english', k.keyword)
      ORDER  BY p.post_id, k.topic_id
      ) sub
   GROUP  BY post_id
   ) sub1
RIGHT  JOIN post p USING (post_id)
ORDER  BY post_id;

fiddle

对于模糊匹配，考虑三元索引.参见:

• PostgreSQL LIKE query performance variations
• How to create simple fuzzy search with PostgreSQL only?

相关内容:

• Pattern matching with LIKE, SIMILAR TO or regular expressions
• Get partial match from GIN indexed TSVECTOR column