[解決済み] SQLの配列の平坦化。なぜCROSS JOIN UNNESTは、入れ子になっているすべての値とすべての行を結合しないのですか？

質問

この質問は特定の問題を解決するためではなく、配列を平坦化するために使われる一般的なSQLイディオムの舞台裏で実際に何が起こっているかを理解するためのものです。 舞台裏には何らかのマジックがあり、私は構文上の砂糖のカーテンの裏側を覗いて、何が起こっているのか見てみたいのです。

次のようなテーブルを考えてみましょう。 t1 :

という関数があるとします。 FLATTEN は、配列型のカラムを受け取ってそのカラム内の各配列を展開し、各配列の各値に対して一行ずつになるようにします。 SELECT FLATTEN(numbers_array) AS flattened_numbers FROM t1 と呼ぶことにします。 t2

SQLでは、CROSS JOINは2つのテーブルの行を結合し、最初のテーブルの各行と2番目のテーブルの各行を結合します。ですから、もし私たちが SELECT id, flattened.flattened_numbers from t1 CROSS JOIN flattened となります。

さて、flattenは単なる想像上の関数で、CROSS JOINと組み合わせても、ご覧のように、元の値のそれぞれは id 列と混在することになります。 flattened_numbers を元の行のそれぞれから取得します。 がないため、すべてが混ざり合ってしまいます。 WHERE の行だけを選択する節があります。 CROSS JOIN となります。

実際に配列を平らにするために使われるパターンは、次のようなものです。 SELECT id, flattened_numbers FROM t1 CROSS JOIN UNNEST(sequences.some_numbers) AS flattened_numbers を生成します。

しかし、なぜ CROSS JOIN UNNEST のパターンが実際に機能します。 なぜなら CROSS JOIN が含まれていないため WHERE 節と同じように動作することを期待します。 FLATTEN 関数を使用すると、非ネストの値がすべて t1 .

の中で実際に何が起こっているのか、誰か「解きほぐす」ことができますか？ CROSS JOIN UNNEST このパターンでは、各行がそれ自身のネストされた値のみと結合されるようにします (他の行のネストされた値とは結合されません)。

どのように解決するのですか？

これを考えるには、行単位で何が起こっているかを見るのが一番です。入力データをいくつか設定します。

WITH t1 AS (
  SELECT 1 AS id, [0, 1] AS numbers_array UNION ALL
  SELECT 2, [2, 4, 5]
)
...

(2行目に3つ目の要素を使って面白くしています)。そこから選択するだけなら、次のような出力が得られます。

WITH t1 AS (
  SELECT 1 AS id, [0, 1] AS numbers_array UNION ALL
  SELECT 2, [2, 4, 5]
)
SELECT * FROM t1;
+----+---------------+
| id | numbers_array |
+----+---------------+
| 1  | [0, 1]        |
| 2  | [2, 4, 5]     |
+----+---------------+

では、アンネストについて説明します。その UNNEST 関数は、配列を受け取り、その配列の要素型の値テーブルを返します。BigQuery のテーブルの多くは列の集まりとして定義された SQL テーブルですが、値テーブルの行はいくつかの 値 という型があります。については numbers_array , UNNEST(numbers_array) の値テーブルを返します。 INT64 というのは numbers_array は配列で、要素タイプは INT64 . この値表には numbers_array から現在の行に対して t1 .

を持つ行に対して id が返す値テーブルの内容は、1である。 UNNEST(numbers_array) があります。

+-----+
| f0_ |
+-----+
| 0   |
| 1   |
+-----+

これは、次のクエリで得られるものと同じです。

SELECT * FROM UNNEST([0, 1]);

UNNEST([0, 1]) この場合、quot; から値テーブルを作成することを意味します。 INT64 値 0 と 1 となります。

同様に id が 2 である場合，値テーブルの内容は UNNEST(numbers_array) があります。

+-----+
| f0_ |
+-----+
| 2   |
| 4   |
| 5   |
+-----+

では、どのように CROSS JOIN がフィットします。ほとんどの場合 CROSS JOIN 相関のない2つのテーブルの間。つまり、右側のテーブルの中身が CROSS JOIN は、左側のテーブルの現在の内容によって定義されるものではありません。

配列の場合や UNNEST によって生成される値表の内容です。 UNNEST(numbers_array) の現在の行によって変化します。 t1 . 2 つのテーブルを結合すると、現在の行のクロスプロダクトが t1 のすべての行と UNNEST(numbers_array) . 例えば

WITH t1 AS (
  SELECT 1 AS id, [0, 1] AS numbers_array UNION ALL
  SELECT 2, [2, 4, 5]
)
SELECT id, number
FROM t1
CROSS JOIN UNNEST(numbers_array) AS number;
+----+--------+
| id | number |
+----+--------+
| 1  | 0      |
| 1  | 1      |
| 2  | 2      |
| 2  | 4      |
| 2  | 5      |
+----+--------+

numbers_array は1行目に2つの要素、2行目に3つの要素を持つので 2 + 3 = 5 の行がクエリの結果に含まれます。

を平坦化することとどう違うのかという質問に答えます。 numbers_array と では を実行する CROSS JOIN このクエリの結果を見てみましょう。

WITH t1 AS (
  SELECT 1 AS id, [0, 1] AS numbers_array UNION ALL
  SELECT 2, [2, 4, 5]
), t2 AS (
  SELECT number
  FROM t1
  CROSS JOIN UNNEST(numbers_array) AS number
)
SELECT number
FROM t2;
+--------+
| number |
+--------+
| 0      |
| 1      |
| 2      |
| 4      |
| 5      |
+--------+

この場合 t2 という名前のカラムを持つSQLテーブルです。 number をそれらの値で指定します。を実行すると CROSS JOIN との間に t1 と t2 とすると、すべての行の真のクロスプロダクトが得られます。

WITH t1 AS (
  SELECT 1 AS id, [0, 1] AS numbers_array UNION ALL
  SELECT 2, [2, 4, 5]
), t2 AS (
  SELECT number
  FROM t1
  CROSS JOIN UNNEST(numbers_array) AS number
)
SELECT id, numbers_array, number
FROM t1
CROSS JOIN t2;
+----+---------------+--------+
| id | numbers_array | number |
+----+---------------+--------+
| 1  | [0, 1]        | 0      |
| 1  | [0, 1]        | 1      |
| 1  | [0, 1]        | 2      |
| 1  | [0, 1]        | 4      |
| 1  | [0, 1]        | 5      |
| 2  | [2, 4, 5]     | 0      |
| 2  | [2, 4, 5]     | 1      |
| 2  | [2, 4, 5]     | 2      |
| 2  | [2, 4, 5]     | 4      |
| 2  | [2, 4, 5]     | 5      |
+----+---------------+--------+

では、先ほどのクエリと何が違うかというと CROSS JOIN UNNEST(numbers_array) ? この場合 t2 から各行ごとに変更されることはありません。 t1 . の最初の行は t1 には5つの行があります。 t2 . の2行目については t1 には5つの行があります。 t2 . その結果 CROSS JOIN を返します。 5 + 5 = 10 行の合計です。