CODEWITHSUNDEEP
google-bigquery
Canovice
Canovice

Reputation: 10441

Use window function to compute a sequential/cumulative array_agg() over a column

with
  t1 as (
    select 1 as poss, 1 as sequ, 'jon' as name union all
    select 1 as poss, 2 as sequ, 'nick' as name union all
    select 1 as poss, 3 as sequ, null as name union all
    select 1 as poss, 4 as sequ, null as name union all
    select 1 as poss, 5 as sequ, 'tom' as name union all
    select 2 as poss, 1 as sequ, null as name union all
    select 2 as poss, 2 as sequ, null as name union all
    select 2 as poss, 3 as sequ, 'bil' as name union all
    select 2 as poss, 4 as sequ, 'kev' as name union all
    select 2 as poss, 5 as sequ, null as name
  ),

  output as (
    select 1 as poss, 1 as sequ, 'jon' as name, ['jon'] as arrayCol union all
    select 1 as poss, 2 as sequ, 'nick' as name, ['jon', 'nick'] as arrayCol union all
    select 1 as poss, 3 as sequ, null as name, ['jon', 'nick'] as arrayCol union all
    select 1 as poss, 4 as sequ, null as name, ['jon', 'nick'] as arrayCol union all
    select 1 as poss, 5 as sequ, 'tom' as name, ['jon', 'nick', 'tom'] as arrayCol union all
    select 2 as poss, 1 as sequ, null as name, [] as arrayCol union all
    select 2 as poss, 2 as sequ, null as name, [] as arrayCol union all
    select 2 as poss, 3 as sequ, 'bil' as name, ['bil'] as arrayCol union all
    select 2 as poss, 4 as sequ, 'kev' as name, ['bil', 'kev'] as arrayCol union all
    select 2 as poss, 5 as sequ, null as name, ['bil', 'kev'] as arrayCol
  )

select * from output

Within each poss, we are looking to array_agg() over the name column. The tough part - we are looking to do this in a sequential/cumulative way, using sequ as an ordering column. The column we are trying to create is arrayCol.

However, array_agg() is an aggregate function, not a window function, so it doesn't quite work in this case, as we do not want to groupby the table. We need to maintain the same number of rows.

Upvotes: 0

Views: 182

Answers (1)

Jaytiger
Jaytiger

Reputation: 12264

ARRAY_AGG() can be used as an window function but doesn't support IGNORE NULLS inside, so you might consider below workaround.

SELECT *, SPLIT(STRING_AGG(name) OVER (PARTITION BY poss ORDER BY sequ)) AS arrayCol
  FROM t1;

Output will be same as your expected output.

Upvotes: 1

Related Questions