google-bigquery - 如何使用 BigQuery 查找最常见的二元组？

Question

我想在我的表格中找到最常见的二元组(单词对)。如何使用 BigQuery 执行此操作？

Answer 1

BigQuery 现在支持 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