mysql - INNER JOIN 在同一个表上计算单词出现次数

Question

http://sqlfiddle.com/#!2/e6382

id   id_news  word
 1    6       superman
 2    6       movie
 3    6       review
 4    6       excellent
 5    7       review
 6    7       guardians of the galaxy
 7    7       great
 8    8       review
 9    8       superman
10    8       movie
11    8       great

我有一个小问题，我正在尝试通过具有阈值设置的单词将不同的新闻联系起来，在提供的示例中，id_news 6 应该与 8 相关，但是不是 7，因为 7 只有 2 个单词，我只想检测那些至少有 3 的单词> 共同的词。

Answer 1

这将使您接近您的需要:

  SELECT wa1.id_news id, wa2.id_news related
    FROM word_analysis wa1
    JOIN word_analysis wa2
      ON wa2.id_news != wa1.id_news
     AND wa2.word = wa1.word
GROUP BY wa1.id_news, wa2.id_news
  HAVING COUNT(*)>2
ORDER BY wa1.id_news, wa2.id_news

如果你不想要反向关系:

  SELECT wa1.id_news id, wa2.id_news related
    FROM word_analysis wa1
    JOIN word_analysis wa2
      ON wa2.id_news > wa1.id_news
     AND wa2.word = wa1.word
GROUP BY wa1.id_news, wa2.id_news
  HAVING COUNT(*)>2
ORDER BY wa1.id_news, wa2.id_news

如果您只想调查一个 wa1.id_news (6):

  SELECT wa2.id_news related
    FROM word_analysis wa1
    JOIN word_analysis wa2
      ON wa2.id_news != wa1.id_news
     AND wa2.word = wa1.word
   WHERE wa1.id_news = 6
GROUP BY wa1.id_news, wa2.id_news
  HAVING COUNT(*)>2
ORDER BY wa2.id_news

如果您只想调查一个关系 (6->8)，其中有结果表示相关，没有结果表示不相关:

  SELECT 1
    FROM word_analysis wa1
    JOIN word_analysis wa2
      ON wa2.id_news = 8
     AND wa2.word = wa1.word
   WHERE wa1.id_news = 6
GROUP BY wa1.id_news, wa2.id_news
  HAVING COUNT(*)>2