sql - 如何使用 SQL (BigQuery) 计算 TF/IDF

Question

我正在对 reddit 评论进行文本分析，我想在 BigQuery 中计算 TF-IDF。

Answer 1

此查询在 5 个阶段工作:

获取我感兴趣的所有 reddit 帖子。标准化单词(较低，只有字母和 ' ，对一些 HTML 进行转义)。将这些单词拆分成一个数组。

计算每个文档中每个单词的 tf(词频) - 计算它在每个文档中出现的次数，相对于所述文档中的单词数。

对于每个单词，计算包含它的文档数。

从(3.)中，得到idf(逆文档频率):“包含该词的文档的倒数，通过将文档总数除以包含该词的文档数，然后取该商的对数而获得"

将 tf*idf 相乘得到 tf-idf。

通过将获得的值向上传递，该查询设法在一次传递中完成此操作。

#standardSQL
WITH words_by_post AS (
  SELECT CONCAT(link_id, '/', id) id, REGEXP_EXTRACT_ALL(
    REGEXP_REPLACE(REGEXP_REPLACE(LOWER(body), '&', '&'), r'&[a-z]{2,4};', '*')
      , r'[a-z]{2,20}\'?[a-z]+') words
  , COUNT(*) OVER() docs_n
  FROM `fh-bigquery.reddit_comments.2017_07`  
  WHERE body NOT IN ('[deleted]', '[removed]')
  AND subreddit = 'movies'
  AND score > 100
), words_tf AS (
  SELECT id, word, COUNT(*) / ARRAY_LENGTH(ANY_VALUE(words)) tf, ARRAY_LENGTH(ANY_VALUE(words)) words_in_doc
    , ANY_VALUE(docs_n) docs_n
  FROM words_by_post, UNNEST(words) word
  GROUP BY id, word
  HAVING words_in_doc>30
), docs_idf AS (
  SELECT tf.id, word, tf.tf, ARRAY_LENGTH(tfs) docs_with_word, LOG(docs_n/ARRAY_LENGTH(tfs)) idf
  FROM (
    SELECT word, ARRAY_AGG(STRUCT(tf, id, words_in_doc)) tfs, ANY_VALUE(docs_n) docs_n
    FROM words_tf
    GROUP BY 1
  ), UNNEST(tfs) tf
)    


SELECT *, tf*idf tfidf
FROM docs_idf
WHERE docs_with_word > 1
ORDER BY tfidf DESC
LIMIT 1000