BigQueryで配列を展開する

次のようなテーブルがあるとします。

name	items	quantities	prices
一郎	[ "リンゴ", "ブドウ", "モモ" ]	[ 3, 5, 1 ]	[ 100, 200, 300 ]
次郎	[ "リンゴ", "バナナ", "スイカ" ]	[ 2, 1, 4 ]	[ 100, 150, 400 ]
三郎	[ "モモ", "スイカ" ]	[ 3, 1 ]	[ 300, 400 ]

以下のような形になるよう整形していきます。
※sum_all＝ユーザーごとの合計金額

name	item	price	quantity	sum	sum_all
一郎	リンゴ	100	3	300	1600
一郎	ブドウ	200	5	1000
一郎	モモ	300	1	300
三郎	モモ	300	3	900	1300
三郎	スイカ	400	1	400
次郎	リンゴ	100	2	200	1950
次郎	バナナ	150	1	150
次郎	スイカ	400	4	1600

はじめに、アイテムごとに数量と値段を展開します。

table_expand AS (
  SELECT
    offset,
    name,
    item,
    prices[OFFSET(offset)] AS price,
    quantities[OFFSET(offset)] AS quantity
  FROM
    base_table,
    UNNEST(items) AS item WITH OFFSET AS offset
  ORDER BY name, offset
)

FROM文で配列に格納されているアイテムを1行ずつに展開し、それぞれの行にOFFSETを付与します。

UNNEST(items) AS item WITH OFFSET AS offset

OFFSETの値を元に、各アイテムに対応した数量と値段を展開します。

prices[OFFSET(offset)] AS price,
quantities[OFFSET(offset)] AS quantity

offset	name	item	price	quantity
0	一郎	リンゴ	100	3
1	一郎	ブドウ	200	5
2	一郎	モモ	300	1
0	三郎	モモ	300	3
1	三郎	スイカ	400	1
0	次郎	リンゴ	100	2
1	次郎	バナナ	150	1
2	次郎	スイカ	400	4

これで配列の展開が完了しました。

次に、各ユーザー・アイテムごとに合計金額を計算します。

table_sum AS (
  SELECT
    offset,
    name,
    item,
    price,
    quantity,
    (price * quantity) AS sum
  FROM table_expand
)

offset	name	item	price	quantity	sum
0	一郎	リンゴ	100	3	300
1	一郎	ブドウ	200	5	1000
2	一郎	モモ	300	1	300
0	三郎	モモ	300	3	900
1	三郎	スイカ	400	1	400
0	次郎	リンゴ	100	2	200
1	次郎	バナナ	150	1	150
2	次郎	スイカ	400	4	1600

次に、ユーザーごとに合計金額を計算します。

table_sum_all AS (
  SELECT
    name,
    SUM(sum) AS sum_all
  FROM table_sum
  GROUP BY name
)

name	sum_all
一郎	1600
三郎	1300
次郎	1950

最後に、ユーザーごとの合計金額を元のテーブルに結合します。

table_fix AS (
  SELECT
    table_sum.*,
    table_sum_all.sum_all
  FROM table_sum LEFT OUTER JOIN table_sum_all
    -- 結合条件1：table_sum.nameとtable_sum_all.nameの値が等しい
    ON table_sum.name = table_sum_all.name
    -- 結合条件1：table_sum.offsetの値が0である
    AND table_sum.offset = 0
)

offset	name	item	price	quantity	sum	sum_all
0	一郎	リンゴ	100	3	300	1600
1	一郎	ブドウ	200	5	1000
2	一郎	モモ	300	1	300
0	三郎	モモ	300	3	900	1300
1	三郎	スイカ	400	1	400
0	次郎	リンゴ	100	2	200	1950
1	次郎	バナナ	150	1	150
2	次郎	スイカ	400	4	1600

抽出項目を整理したら完成です。