如何使用BigQuery查找最常用的双元素？ [英] How to find the most common bi-grams with BigQuery?

问题描述

我想在我的表格中找到最常见的双元组（双字）。 BigQuery现在支持SPLIT（）：

$ b如何使用BigQuery做到这一点？

解决方案

$ b

  SELECT word，nextword，COUNT（*）c 
 FROM（
 SELECT pos，title，word，LEAD（word）OVER（PARTITION BY created_utc ，标题ORDER BY pos）nextword FROM（
）SELECT created_utc，title，word，pos FROM FLATTEN（
（SELECT created_utc，title，word，POSITION（word）pos FROM 
（SELECT created_utc， title），SPLIT（title，''）word FROM [bigquery-samples：reddit.full]）
），word）
））
其中nextword不为空
 GROUP EACH BY 1，2 
 ORDER BY c DESC 
 LIMIT 100 
  

I want to find the most common bi-grams (pair of words) in my table. How can I do this with BigQuery?

解决方案

BigQuery now supports SPLIT():

SELECT word, nextword, COUNT(*) c 
FROM (
SELECT pos, title, word, LEAD(word) OVER(PARTITION BY created_utc,title ORDER BY pos) nextword FROM (
SELECT created_utc, title, word, pos FROM FLATTEN(
  (SELECT created_utc, title, word, POSITION(word) pos FROM
   (SELECT created_utc, title, SPLIT(title, ' ') word FROM [bigquery-samples:reddit.full])
  ), word)
))
WHERE nextword IS NOT null
GROUP EACH BY 1, 2
ORDER BY c DESC
LIMIT 100

这篇关于如何使用BigQuery查找最常用的双元素？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！