BigQuery 相関クロス結合について

はじめに

こんにちは！Takoです。
今回は相関クロス結合について実際の作業で使ったのでアウトプットとして書きたいと思います！

相関クロス結合とは？

通常のクロス結合は単純な掛け算なので、3行のレコードのテーブル同士を 3x3 で以下のような結果になります。(詳しいコードの説明は割愛)

cross_join.sql

with user as (
  select
    *
  from
    unnest([
      "A", "B", "C"
    ]) as name
)

, purchase_date as (
  select
    *
  from
    unnest([
      "2023/01/01", "2023/01/02", "2023/01/03"
    ]) as date
)

select
  *
from
  user,purchase_date -- カンマを cross join と書き換えても同じ処理をする

ただ、上記のように単純な掛け算でテーブルを縦に伸ばしたい例は少ないのかなと感じます(行数が無駄に増えちゃうし)。ユーザーごとやカテゴリごとなどに紐づけたいなどがあるかと思います。そんな悩みを解決するのが相関クロス結合です！

相関クロス結合を使ってみる

ユーザーごとにインストール日～アンインストール日までの日付を持ったテーブルを相関クロス結合を使って作りたいと思います。先ほどのクエリを少しいじって試してみます(インストール日とアンインストール日は予め追加しています)。まずはコードと結果から

relational_cross_join.sql

with user as (
  select
    *
  from
    unnest(ARRAY<STRUCT<name string, install_date date, uninstall_date date>>[
      ("A", "2023-01-01", "2023-01-02"),
      ("B", "2023-01-02", "2023-01-03"),
      ("C", "2023-01-01", "2023-01-04" )
    ])
)

, user_with_date as (
select
  name,
  generate_date_array(install_date, uninstall_date) as array_date
-- generate_date_array は日付の配列を作る関数
-- e.x. generate_date_array('2023-01-01, '2023-01-03')
-- だと ['2023-01-01, '2023-01-02, '2023-01-03'] が返ってくる
from
  user
)

select
  name,
  date
from
  user_with_date
cross join
  unnest(array_date) as date

少し解説をしていきます！が、個人的には相関は「？？」となることが多いので実際に試して理解する方がいいと思います。

cross join
  unnest(array_date) as date

このコードのように 配列 を unnest してクロス結合することで上記のようなテーブルを作成することができます。array を分解してその値が元々紐づいているユーザーのみに結合されているイメージでいいかと思います。相関クロス結合をする際には必ず同一のテーブル同士の結合をしてください。違うテーブル同士で上記のような結合をしても通常のクロス結合になります。

まとめると

• 配列カラムを含んだテーブルを用意
• 1のテーブルと unnest(1のテーブルの配列カラム) を cross join する

です！

ちなみに cross join 上で配列を作成すると少しクエリを省略できます。結果は先ほど書いた相関クロス結合のクエリと同じです。

relational_cross_join2.sql

with user as (
  select
    *
  from
    unnest(ARRAY<STRUCT<name string, install_date date, uninstall_date date>>[
      ("A", "2023-01-01", "2023-01-02"),
      ("B", "2023-01-02", "2023-01-03"),
      ("C", "2023-01-01", "2023-01-04" )
    ])
)

select
  name,
  date
from
  user
cross join
  unnest(generate_date_array(install_date, uninstall_date)) as date

さいごに

相関クロス結合は意外と使える場面があるかと思います。個人的には日付を指定の日まで1日ごとor1月ごとに追加したい！って場面は有用かなと思っています。是非使ってみてください！