SQL反向索引构建异常求助：关键词‘are’文档匹配数不符预期

Here are the most likely causes and fixes for your discrepancy:

• Duplicate keyword entries for the same document
  If your insert logic doesn’t deduplicate (keyword, doc_id) pairs, a document containing "are" multiple times will create multiple rows in invert01. When counting documents, you might be counting total rows instead of distinct doc_ids.

  • Verify this by querying invert01:

    SELECT doc_id, COUNT(*) AS occurrences FROM invert01 WHERE keyword = 'are' GROUP BY doc_id;
    

  • Fix it by adding DISTINCT to your insert statement to ensure each keyword-doc pair is unique:

    INSERT INTO invert01 (keyword, doc_id)
    SELECT DISTINCT LOWER(word), doc_id
    FROM docs01, UNNEST(string_to_array(content, ' ')) AS word
    WHERE LOWER(word) = 'are';
    

• Unintended duplicate documents in docs01
  You might have inserted more documents than expected that include "are". Check the raw document data:

  SELECT doc_id, content FROM docs01 WHERE LOWER(content) LIKE '%are%';
  

  If this returns 3 rows instead of 2, you have extra duplicate or unintended documents in your table.

• Poor tokenization handling of punctuation
  If your content has words like "are," or "are." (with punctuation), splitting on spaces will treat these as separate keywords—but if your logic isn’t stripping punctuation before converting to lowercase, it might incorrectly map these to "are" (if you’re using a LIKE match without cleaning). Fix this by normalizing words first:

  INSERT INTO invert01 (keyword, doc_id)
  SELECT DISTINCT LOWER(REGEXP_REPLACE(word, '[^a-zA-Z0-9]', '', 'g')), doc_id
  FROM docs01, UNNEST(string_to_array(content, ' ')) AS word
  WHERE LOWER(REGEXP_REPLACE(word, '[^a-zA-Z0-9]', '', 'g')) = 'are';
  

• Incorrect counting logic
  Double-check how you’re calculating the number of documents for "are". If you’re using COUNT(*) instead of COUNT(DISTINCT doc_id) in your validation query, you’ll count multiple occurrences in the same document as separate entries:

  Wrong: SELECT COUNT(*) FROM invert01 WHERE keyword = 'are';
  Correct: SELECT COUNT(DISTINCT doc_id) FROM invert01 WHERE keyword = 'are';

内容的提问来源于stack exchange，提问作者Harshit Hash