Big Query SQL: How do you aggregate the sum of an array contained in a field?-CodePudding

I have a field that contains a query of numbers:

Select Field from Table

Field
[5640.0,11252.0,16836.0,22393.0,27921.0,33422.0,38895.0,44341.0,49759.0,55151.0,60515.0,65853.0,71164.0,76448.0,81707.0,86938.0,92144.0,97324.0,102477.0,107605.0]
[6789.0,13543.0,20264.0,26952.0,33606.0,40226.0,46814.0,53369.0,59890.0,66380.0,72837.0,79261.0,85653.0,92014.0,98343.0,104639.0,110905.0,117139.0,123342.0,129514.0,135655.0,141766.0,147846.0,153895.0,159914.0]
[7409.0,14781.0,22115.0,29414.0,36675.0,43901.0,51090.0,58243.0,65361.0,72443.0,79490.0,86501.0,93477.0,100419.0,107325.0,114198.0,121035.0,127839.0,134609.0,141344.0,148046.0,154715.0,161350.0,167952.0,174521.0]

I would like to add up all the numbers in the resulting arrays in the results. I can't figure out how to do it unnest. Not sure if that's the only way but open to other solutions as well.

CodePudding user response：

with tbl as (Select 
[5640.0,11252.0,16836.0,22393.0,27921.0,33422.0,38895.0,44341.0,49759.0,55151.0,60515.0,65853.0,71164.0,76448.0,81707.0,86938.0,92144.0,97324.0,102477.0,107605.0] as Field,
union all select
[6789.0,13543.0,20264.0,26952.0,33606.0,40226.0,46814.0,53369.0,59890.0,66380.0,72837.0,79261.0,85653.0,92014.0,98343.0,104639.0,110905.0,117139.0,123342.0,129514.0,135655.0,141766.0,147846.0,153895.0,159914.0]
union all select
[7409.0,14781.0,22115.0,29414.0,36675.0,43901.0,51090.0,58243.0,65361.0,72443.0,79490.0,86501.0,93477.0,100419.0,107325.0,114198.0,121035.0,127839.0,134609.0,141344.0,148046.0,154715.0,161350.0,167952.0,174521.0]
)
Select #*, 
sum((select sum(X) from unnest(field) as X  )) over() as total, 
(select sum(X) from unnest(field) as X  ) as sum_array
from tbl

unnest a field:

Select sum(field)
from tbl, unnest(field)

CodePudding user response：

This is the recommended way to perform this operation as suggested by the docs.

with arrays as (Select 
[5640.0,11252.0,16836.0,22393.0,27921.0,33422.0,38895.0,44341.0,49759.0,55151.0,60515.0,65853.0,71164.0,76448.0,81707.0,86938.0,92144.0,97324.0,102477.0,107605.0] as Field,
union all select
[6789.0,13543.0,20264.0,26952.0,33606.0,40226.0,46814.0,53369.0,59890.0,66380.0,72837.0,79261.0,85653.0,92014.0,98343.0,104639.0,110905.0,117139.0,123342.0,129514.0,135655.0,141766.0,147846.0,153895.0,159914.0] AS Field
union all select
[7409.0,14781.0,22115.0,29414.0,36675.0,43901.0,51090.0,58243.0,65361.0,72443.0,79490.0,86501.0,93477.0,100419.0,107325.0,114198.0,121035.0,127839.0,134609.0,141344.0,148046.0,154715.0,161350.0,167952.0,174521.0] AS Field
)

SELECT Field,
  (SELECT SUM(x)
   FROM UNNEST(t.Field) x) AS sums
FROM arrays t;

The output will be rows like this: [x,y,z], sum