I would like an example of a function in BigQuery (SQL), in order to see the syntax needed to create such a function: create a function in BigQuery, using SQL (not JavaScript), where internally an array and loop is created to iterate this array, adding the values of the array and returning the resulting sum (int64).
1 Answers
Below is for BigQuery Standard SQL
example of a function in BigQuery (SQL) that "loops" through array's elements and produces sum of its elements
create temp function sum_array(a array<INT64>) as ((
select sum(element) from unnest(a) element
));
Below is example of using such function with simplified dummy data
#standardSQL
create temp function sum_array(a array<INT64>) as ((
select sum(element) from unnest(a) element
));
with `project.dataset.table` as (
select 1 id, [1, 2, 3, 4, 5,6 ] arr union all
select 2, [1, 3, 5, 7, 9] union all
select 3, [2, 4]
)
select id, sum_array(arr) sum_elements_of_array
from `project.dataset.table`
order by id
with output
Related