CODEWITHSUNDEEP
google-bigquery
cloudexplorer
cloudexplorer

Reputation: 55

Standard SQL - Sum arrays in a column

I have a question regarding BigQuery (using Standard SQL).

Suppose I have the following data:

Id  Arrays
1   [1, 4]
2   [2]
1   [3, 4, 6, 91]
2   [0, 9, 1, 4, 0, 37]

I want to sum the arrays to the following output:

Id  Arrays
1   [4, 8, 6, 91]
2   [2, 9, 1, 4, 0, 37]

The number of elements in each array can vary. And the order of the summed result matters a lot.

I considered user defined functions in Standard SQL, but failed.

Can anyone tell me if this is possible to achieve by using standard SQL and if yes, how?

It would be great if you can show me a sample code. Thanks in advance.

Upvotes: 1

Views: 2044

Answers (1)

Elliott Brossard
Elliott Brossard

Reputation: 33765

This should work:

SELECT
  id, ARRAY_AGG(x ORDER BY idx) AS arrays
FROM (
  SELECT id, idx, SUM(x) AS x
  FROM table,
  UNNEST(arrays) AS x WITH OFFSET idx
  GROUP BY id, idx
)
GROUP BY id

It first sums the array elements based on index, then re-aggregates into arrays using the original element order.

Upvotes: 1

Related Questions