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

>获取我感兴趣的所有reddit帖子.规范化单词(LOWER,只有字母和’,unescape一些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,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