CODEWITHSUNDEEP
sqlgoogle-bigquery
TLD
TLD

Reputation: 8135

Perform loop and calculation on BigQuery Array type

My original data, B is an array of INT64:

enter image description here

And I want to calculate the difference between B[n+1] - B[n], hence result in a new table as follow:

enter image description here

I figured out I can somehow achieve this by using LOOP and IF condition:

DECLARE x INT64 DEFAULT 0;
LOOP
 SET x = x + 1
 IF(x < array_length(table.B))
 THEN INSERT INTO newTable (SELECT A, B[OFFSET(x+1)] - B[OFFSET(x)]) from table
 END IF;
END LOOP;

The problem is that the above idea doesn't work on each row of my data, cause I still need to loop through each row in my data table, but I can't find a way to integrate my scripting part into a normal query, where I can

SELECT A, [calculation script] from table

Can someone point me how can I do it? Or any better way to solve this problem?

Thank you.

Upvotes: 0

Views: 828

Answers (2)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172993

Below actually works - BigQuery

select * replace(
  array(select diff from (
      select offset, lead(el) over(order by offset) - el as diff
      from unnest(B) el with offset
    ) where not diff is null
    order by offset
  ) as B
)
from `project.dataset.table` t

if to apply to sample data in your question - output is

enter image description here

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269803

You can use unnest() with offset for this purpose:

select id, a,
       array_agg(b_el - prev_b_el order by n) as b_diffs
from (select t.*, b_el, lag(b_el) over (partition by t.id order by n) as prev_b_el
      from t cross join
           unnest(b) b_el with offset n
     ) t
where prev_b_el is not null
group by t.id, t.a

Upvotes: 1

Related Questions