How to find the most common bi-grams with BigQuery?

Question

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

score 11 · Answer 1 · answered Jun 11 '14 at 21:37

11

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

answered Jun 11 '14 at 21:37

Felipe Hoffa

54,922
16
151
325

Lovely!! where can some documentation can be found? – N.N. Jun 12 '14 at 11:30
Soon in https://developers.google.com/bigquery/docs/query-reference. I couldn't wait to share the news :) – Felipe Hoffa Jun 12 '14 at 16:11
1

@FelipeHoffa This is using LegacySQL, any chance you'd be up for updating this using StandardSQL? – Arjun Mehta Mar 28 '18 at 23:46
check out the new `ML.NGRAMS()` function in my new answer below – Felipe Hoffa Jan 09 '20 at 07:55

Felipe Hoffa · Accepted Answer · 2020-01-10T09:07:16.197

6

Now with a new function: ML.NGRAMS():

WITH data AS  (
  SELECT REGEXP_EXTRACT_ALL(LOWER(title), '[a-z]+') title_arr
  FROM `fh-bigquery.reddit_posts.2019_08`  
  WHERE title LIKE '% %'
  AND score>1
)


SELECT APPROX_TOP_COUNT(bigram, 10) top
FROM (
  SELECT ML.NGRAMS(title_arr, [2,2]) x
  FROM data
), UNNEST(x) bigram
WHERE LENGTH(bigram) > 10

Docs:

https://cloud.google.com/bigquery-ml/docs/reference/standard-sql/bigqueryml-preprocessing-functions#mlngrams

edited Jan 10 '20 at 09:07

answered Jan 09 '20 at 07:55

Felipe Hoffa

54,922
16
151
325

Thanks for sharing, @Felipe The statement that you provide is nested within a row. How would you "unnest" it further so that the only output columns are top.value and top.count and not the Row column? – Jordan Choo Jul 27 '21 at 00:38

score 1 · Answer 3 · answered Nov 22 '19 at 11:21

The standard SQL version:

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 ( 
    SELECT created_utc, title, SPLIT(title, ' ') word FROM `bigquery-samples.reddit.full`), UNNEST(word) as word WITH OFFSET pos))
WHERE nextword IS NOT null
GROUP BY 1, 2
ORDER BY c DESC
LIMIT 100

When unnesting an ARRAY you can retrieve the position of that element using the following syntax:

UNNEST(word) as word WITH OFFSET pos

check out the new `ML.NGRAMS()` function in my new answer below — Felipe Hoffa, Jan 09 '20 at 07:55

How to find the most common bi-grams with BigQuery?

3 Answers3

Linked