怎样行使SQL(BigQuery)计较TF / IDF

我正在对reddit注释举办文天职析,我想在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

（编辑：湖南网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!