CODEWITHSUNDEEP
sqlgoogle-bigquery
MartinD
MartinD

Reputation: 67

BigQuery Array Element Multiplicaton

Noob question here:

I have a BQ table with a nested structure containing 2 arrays, x and y. I want to create either a new array of xy or have the 2 arrays unnested and create a flattened table of x, y & xy values.

WITH
  tests AS (
  SELECT
  [  struct("test1" AS test,
        [0.1576,0.9706,0.9572,0.4854,0.8003,0.1419,0.4218,0.9157,0.7922,0.9595] AS x,
        [11,12,13,14,15,16,17,18,19,20] AS y),
    struct("test2" AS test,
        [0.8147,0.9058,0.1270,0.9134,0.6324,0.0975,0.2785,0.5469,0.9575, 0.9649] AS x,
        [11,12,13,14,15,16,17,18,19,20] AS y),
   struct("test3" AS test,
        [0.6557,0.0357,0.8491,0.9340,0.6787,0.7577,0.7431,0.3922,0.6555,0.1712] AS x,
        [11,12,13,14,15,16,17,18,19,20] AS y)
   ] AS measurements)


SELECT
  test, x,y
FROM
  tests t, unnest(t.measurements)

I would like extend the above query to get a flattened table with test number, x, y and x*y like this, or something similar:

tests x y x*y
test1 0.1576 11 1.7366
test1 0.9706 12 11.6472
test1 0.9572 13 12.4436
test1 0.4854 14 6.7956
... ... ... ...
test3 0.1712 20 3.4240

Upvotes: 1

Views: 740

Answers (1)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172993

Consider below approach

select test, x, y, round(x*y, 4) as x_multiply_y
from tests, unnest(measurements),
unnest(x) x with offset 
join unnest(y) y with offset 
using(offset)

if applied to sample data in your question - output is

enter image description here

Upvotes: 2

Related Questions