UNNESTとは何ぞや
BigQueryでUNNESTとCROSS JOINというものを見て気になったので調べてみた。
BigQueryではRDBと違って、非構造化することが可能なのだ。つまり列定義で配列型や構造体を指定することができる。テーブルとしては配列の中身が幾つあろうが1行（カラム）として取り扱います。
ここでUNNESTはどう機能するかというと、その配列を展開して1行ずつとして取り扱います。
WITH
Sequences AS (
SELECT [0, 1, 1, 2, 3, 5] AS some_numbers UNION ALL
SELECT [2, 4, 8, 16, 32] UNION ALL
SELECT [5, 10]
)
SELECT * FROM Sequences
/*---------------------*
| some_numbers |
+---------------------+
| [0, 1, 1, 2, 3, 5] |
| [2, 4, 8, 16, 32] |
| [5, 10] |
*---------------------*/CROSS JOINもなんぞや？
名前の通りクロス結合ですが、UNNESTと組み合わせることで相関クロス結合を意味します。
テーブルの各行（1行）ごとに配列型列のデータ（複数）をクロス結合する、クロス結合となる訳です。
WITH Sequences AS
(SELECT 1 AS id, [0, 1, 1, 2, 3, 5] AS some_numbers
UNION ALL SELECT 2 AS id, [2, 4, 8, 16, 32] AS some_numbers
UNION ALL SELECT 3 AS id, [5, 10] AS some_numbers)
SELECT id, flattened_numbers
FROM Sequences
CROSS JOIN UNNEST(Sequences.some_numbers) AS flattened_numbers;
/*------+-------------------*
| id | flattened_numbers |
+------+-------------------+
| 1 | 0 |
| 1 | 1 |
| 1 | 1 |
| 1 | 2 |
| 1 | 3 |
| 1 | 5 |
| 2 | 2 |
| 2 | 4 |
| 2 | 8 |
| 2 | 16 |
| 2 | 32 |
| 3 | 5 |
| 3 | 10 |
*------+-------------------*/書き方としては以下のように省略することも可能です。
WITH Sequences AS
(SELECT 1 AS id, [0, 1, 1, 2, 3, 5] AS some_numbers
UNION ALL SELECT 2 AS id, [2, 4, 8, 16, 32] AS some_numbers
UNION ALL SELECT 3 AS id, [5, 10] AS some_numbers)
SELECT id, flattened_numbers
FROM Sequences, Sequences.some_numbers AS flattened_numbers;