BigQuery: UNNEST 配列をテーブルとして扱う

BigQuery: UNNEST 配列をテーブルとして扱う

February 16, 2021

BigQuery

BigQuery, SQL

配列をテーブルとして扱う #

UNNEST 演算子を利用すると配列をテーブルとして扱うことができる。

SELECT * FROM UNNEST([1,2,3,4,5])

AS でエイリアスを付ければアクセスできる。

SELECT SUM(num) FROM UNNEST([1,2,3,4,5]) AS num

STRUCT の配列をテーブルとして扱う #

SELECT * FROM UNNEST([
    (1, 'a'),
    (2, 'b'),
    (3, 'c'),
    (4, 'd'),
    (5, 'e')
  ])

ただこれだと匿名フィールドになって扱いづらい。STRUCTで型を明示するとアクセスできる。型を宣言するのは最初の要素だけで良い。

SELECT * FROM UNNEST([
    STRUCT<num INT64, str STRING>(1, 'a'),
    (2, 'b'),
    (3, 'c'),
    (4, 'd'),
    (5, 'e')
  ])

順序を保持する #

UNNEST は順序を保持しない（保持することを保証しない）とある。順序を保持する場合は WITH OFFSET を付与してそれで ORDER BY する。

SELECT * FROM UNNEST(['a','b','c','d','e']) WITH OFFSET AS offset
ORDER BY offset

WITH OFFSET でエイリアスを指定しない場合のデフォルト名は offset らしい（上記サンプルコードはデフォルトと同じ名前を指定している）

サブクエリで UNNEST を使う #

サブネストの FROM 句で利用するケースが多いかもしれない。

WITH has_int_array AS (
    SELECT nums FROM UNNEST([
        STRUCT<nums ARRAY<INT64>>([1,2,3]),
        STRUCT([4,5,6]),
        STRUCT([7,8,9])
    ])
)

SELECT
    nums,
    (SELECT SUM(num) FROM UNNEST(nums) AS num) AS sum_of_nums
FROM has_int_array