BigQuery SQLサンプル - 相関クロス結合

2020年2月18日BigQueryBigQuery SQLサンプル

相関クロス結合とは

相関クロス結合の前にクロス結合(直積ともいいます)についてお復習いしておきましょう。

クロス結合(直積)

クロス結合は全ての組み合わせでテーブルを結合する方法です。

例えば、以下のような2つのテーブルをクロス結合した場合の結果はこうなります。

テーブルA

 列A 
1
2
3

テーブルB

 列B 
a
b
c

クロス結合の結果

全ての組み合わせ結果となるので、3行×3行=9行の出力となります。

 列A  列B 
1a
1b
1c
2a
2b
2c
3a
3b
3c

相関クロス結合

クロス結合では全ての組み合わせが出力されますが、こんなことを考えたことはないでしょうか?

全ての組み合わせではなく、
・列Aの「1」という値に対しては列Bの「a」と「b」の組み合わせのみ表示したい
・列Aの「2」と「3」という値に対しては列Bの「b」と「c」の組み合わせのみ表示したい

つまり、こんな結果がほしい場合です。

 列A  列B 
1a
1b
2b
2c
3b
3c

これを実現する方法として相関クロス結合を利用すると可能です。

相関クロス結合のSQLサンプル

では、実際に相関クロス結合のSQLを見てみましょう。

これまでの説明よりも少し複雑な例のサンプルを用意しました。

WITH wk AS
  (
   SELECT
     ['A', 'B'] AS kbn_1
   , ['-'] AS kbn_2
   , ['a', 'b', 'c'] AS kbn_3
   UNION ALL
   SELECT
     ['C'] AS kbn_1
   , ['C-1','C-2'] AS kbn_2
   , ['b', 'e'] AS kbn_3
  )
SELECT
  kbn_1
, kbn_2
, kbn_3
FROM 
wk
CROSS JOIN UNNEST(wk.kbn_1) AS kbn_1
CROSS JOIN UNNEST(wk.kbn_2) AS kbn_2
CROSS JOIN UNNEST(wk.kbn_3) AS kbn_3
;

データの投入としてWITH句を用いていますが、実テーブルでも同じです。

このクエリでやりたいこと

kbn_1が「A」「B」の場合は、kbn_2が「-」固定でkbn_3が「a」「b」「c」の組み合わせがほしい

また、

kbn_2が「C」の場合は、kbn_2が「C-1」「C-2」とkbn_3が「b」「e」の組み合わせがほしい

という2パターンの組み合わせを同時に処理したいということです。

ポイント

配列形式のデータをUNNEST関数で行データに展開したものをFROM句にCROSS JOINで結合すると相関クロス結合となります。

実行結果

kbn_1kbn_2kbn_3
1Aa
2Ab
3Ac
4Ba
5Bb
6Bc
7CC-1b
8CC-1e
9CC-2b
10CC-2e

想定通りの結果を得ることができました。