BigQueryのUNNESTは空配列のレコードを出力しない

5688 ワード
BigQuery unnest StandardSQL BigQuery テキストリンク

背景

BigQueryを使っている。
BigQueryはテーブルの列の型としてARRAY型が指定できる。
例えば以下のようなテーブルだ。

  SELECT
    1 AS id,
    'Alex' AS name,
    [1] AS book_ids
  UNION ALL
  SELECT
    2 AS id,
    'Brian' AS name,
    [] AS book_ids
  UNION ALL
  SELECT
    3 AS id,
    'Carol' AS name,
    [1,
    2,
    3] AS book_ids
行 id name book_ids
1 1 Alex 1
2 2 Brian
3 3 Carol 1
4 2
5 3

book_ids列がARRAY型の列だ。Alexが [1]、Brianが []、Carolが [1, 2, 3] という配列を持っていることを表す。

さて、このテーブル（以降、 users テーブル）は（R）DB理論からすると行儀の悪い設計になっていて、ユーザー情報とそのユーザーがこれまでに読んだ本のIDを同じテーブルで管理してしまっている
（book_ids列がこれまで読んだ本のIDを表す）。

今回、各ユーザーがどんな本を読んできたかのリストを表示したくなった。つまり、 users テーブルと、本の一覧を管理する books テーブルをJOINしたくなった。

users と books テーブルをJOINすればいいのだが、ARRAY型はそのままではON句に書けないので、1レコード1冊の本になるようにテーブルを平坦にしてやる必要がある
（いわゆる第一正規化というやつだ）。

問題

BigQueryではARRAY型の列を平坦にする操作はUNNESTという関数で行うため、下記のようなSQLを書いて実行した。

 SELECT
   id,name,book_id
 FROM
   users,
   UNNEST(book_ids) as book_id
行 id name book_id
1 1 Alex 1
2 3 Carol 1
3 3 Carol 2
4 3 Carol 3

Brianがいなくなってる……。

Brianはこれまで本を全く読んでいなかったため、 users テーブルにおいてbook_idsは要素数0の空配列であった。

UNNESTはARRAY型カラムと他のカラムとの純粋な掛け合わせらしい。
0に何を掛けても0ということでBrian行は消滅してしまった。

本を読んだことがあるユーザーに絞ったリストが欲しいのであればこの挙動でもよいのだが、本を読んだことのないユーザーも表示して欲しい（book_id=nullにして表示して欲しい）ので、困ったことになった。

解決方法

サブクエリーで第一正規系を作って、それを改めて users とJOINする方法を取った。

SELECT
  users.id,
  users.name,
  book_id
FROM
  users
LEFT OUTER JOIN (
  SELECT
    id,
    book_id
  FROM
    users,
    UNNEST(book_ids) AS book_id ) AS sub
ON
  sub.id = users.id
行 id name book_id
1 1 Alex 1
2 2 Brian null
3 3 Carol 1
4 3 Carol 2
5 3 Carol 3

おわりに

解決できたが、もっとよいクエリーの発行の仕方があったかもしれない。
まあそもそも users テーブルの設計を改めろという話ではありますが。