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

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

1. 相関クロス結合とは
- 1.1. クロス結合（直積）
- 1.2. 相関クロス結合
2. 相関クロス結合のSQLサンプル
- 2.1. このクエリでやりたいこと
3. 実行結果

相関クロス結合とは

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

クロス結合（直積）

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

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

テーブルA

列A
1
2
3

テーブルB

列B
a
b
c

クロス結合の結果

全ての組み合わせ結果となるので、３行×３行＝９行の出力となります。

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

相関クロス結合

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

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

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

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

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

相関クロス結合の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句を用いていますが、実テーブルでも同じです。