Btukfyl

I'm looking at text sequences in BigQuery and trying to identify word completions over a number of rows (sharing an ID). The data looks like:

ID, Text

1, t

1, th

1, the

1, the

1, the c

1, the ca

1, the cat

1, the cat 

1, the cat s

...

1, the cat sat on the mat

2, r

...

For each given ID and sequence i'm trying to find the next word boundary. So the ideal output would be:

ID, Text, Boundary

1, t, the

1, th, the

1, the c, the cat

1, the ca, the cat

1, the cat s, the cat sat 

In the above the next subsequent row that both shares an ID and ends in a space gives the next (there can be multiple) word completion boundary.

Below is for BigQuery Standard SQL

Note: it is brute force approach so query is not that elegant as potentially can be - but hope this will give you good start

#standardSQL

SELECT id, item, boundary

FROM (

  SELECT id, grp, 

    STRING_AGG(IF(boundary, text, ''), '') boundary,

    ARRAY_AGG(IF(NOT boundary, text, NULL) IGNORE NULLS ORDER BY LENGTH(text)) items

  FROM (

    SELECT id, text, 

      LENGTH(text) - LENGTH(REPLACE(text, ' ', '')) - IF(SUBSTR(text, -1) = ' ', 1, 0) grp,

      SUBSTR(text, -1) = ' ' boundary

    FROM `project.dataset.table`

  )

  GROUP BY id, grp

), UNNEST(items) item WITH OFFSET pos

WHERE RTRIM(item) != RTRIM(boundary)

if to apply to dummy data in your question as below

#standardSQL

WITH `project.dataset.table` AS (

  SELECT 1 id, 't' text UNION ALL

  SELECT 1, 'th' UNION ALL

  SELECT 1, 'the' UNION ALL

  SELECT 1, 'the ' UNION ALL

  SELECT 1, 'the c' UNION ALL

  SELECT 1, 'the ca' UNION ALL

  SELECT 1, 'the cat' UNION ALL

  SELECT 1, 'the cat ' UNION ALL

  SELECT 1, 'the cat s' UNION ALL

  SELECT 1, 'the cat sat ' 

)

SELECT id, item, boundary

FROM (

  SELECT id, grp, 

    STRING_AGG(IF(boundary, text, ''), '') boundary,

    ARRAY_AGG(IF(NOT boundary, text, NULL) IGNORE NULLS ORDER BY LENGTH(text)) items

  FROM (

    SELECT id, text, 

      LENGTH(text) - LENGTH(REPLACE(text, ' ', '')) - IF(SUBSTR(text, -1) = ' ', 1, 0) grp,

      SUBSTR(text, -1) = ' ' boundary

    FROM `project.dataset.table`

  )

  GROUP BY id, grp

), UNNEST(items) item WITH OFFSET pos

WHERE RTRIM(item) != RTRIM(boundary)

ORDER BY id, grp, pos   

result is

Row     id      item        boundary     

1       1       t           the  

2       1       th          the  

3       1       the c       the cat  

4       1       the ca      the cat  

5       1       the cat s   the cat sat  

BigQuery UDF's come in handy in these situations. Here is a working solution:

#standardSQL

/*boundary function*/

create temp function boundaryf (text string, sentence string) as (

  array_to_string(array(

    select q.w from unnest(

      array(select struct(w as w, row_number() over () as i)  from unnest(split(sentence, ' ')) w

      ) 

    ) q

    -- respect the ending space

    where q.i <= array_length(split(text, ' ')) - (length(text) - length(rtrim(text)))

  ), ' ')

);



WITH items AS (

  #--your data. assuming this is already ordered

  SELECT 1 as id, 't' as text UNION ALL

  SELECT 1, 'th' UNION ALL

  SELECT 1, 'the' UNION ALL

  SELECT 1, 'the ' UNION ALL

  SELECT 1, 'the c' UNION ALL

  SELECT 1, 'the ca' UNION ALL

  SELECT 1, 'the cat' UNION ALL

  SELECT 1, 'the cat ' UNION ALL

  SELECT 1, 'the cat s' UNION ALL

  SELECT 1, 'the cat sa' union all

  SELECT 1, 'the cat sat' union all

  SELECT 1, 'the cat sat ' union all

  SELECT 1, 'the cat sat o' union all 

  SELECT 1, 'the cat sat on' union all

  SELECT 1, 'the cat sat on ' union all

  SELECT 1, 'the cat sat on a' union all

  SELECT 1, 'the cat sat on a ' union all

  SELECT 1, 'the cat sat on a m' union all

  SELECT 1, 'the cat sat on a ma' union all

  SELECT 1, 'the cat sat on a mat' union all

  select 2, 'i' union all

  select 2, 'i a' union all

  select 2, 'i am' union all

  select 2, 'i am f' union all

  select 2, 'i am fr' union all

  select 2, 'i am fre' union all

  select 2, 'i am free'

),

sentences as (

  select id, sentences[offset (array_length(sentences)-1)] as sentence from (

    select id, array_agg(text) as sentences 

    from items group by 1

  )

),

control as (

  select i.id, i.text, boundaryf(i.text, s.sentence) as boundary

  from items i

  left join sentences s on s.id  = i.id

)

select * from control