💁

BigQueryのSQLにおけるいくつかのTips

2025/06/07に公開

SQL

BigQuery

tech

BigQueryをSQLで操作する際につまずいたポイントなどについて整理してみました。
BigQuery固有の挙動ではないものも含まれているかもしれませんが、SQLを書く際の参考になれば幸いです。

今後もBigQueryを利用していく中で新たな発見があれば、随時この内容を更新していく予定です。

1. 構造体の配列を展開するときのLEFT JOIN

以下のようなテーブル user_posts があるとします。
posts カラムは構造体の配列です。

id	name	posts
1	A	[{ title: A, content: X, category_id: 1 }]
2	B	[{ title: B, content: Y, category_id: null }]

上記のテーブルに対して、posts を SELECT 句で展開し、さらに category テーブルを LEFT JOIN するケースを考えます。

SELECT
  id,
  ARRAY(
    SELECT AS STRUCT
      posts.title,
      categories.name
    FROM user_posts.posts posts
    LEFT JOIN categories ON categories.id = posts.category_id
  )
FROM user_posts

しかし、上記のクエリは以下のエラーになります。

Correlated subqueries that reference other tables are not supported unless they can be de-correlated, such as by transforming them into an efficient JOIN.

この問題への対処法はいくつか考えられますが、私は以下のように対応しています。LEFT JOIN を JOIN に変更し、NULL の場合はゼロで結合されるようにします。COALESCE 関数は、値が NULL の場合に代替のデフォルト値を指定するために利用できます。

WITH categories AS (
  SELECT id, name FROM categories
  UNION ALL
  SELECT NULL, NULL
)
SELECT
  id,
  ARRAY(
    SELECT AS STRUCT
      posts.title,
      categories.name
    FROM user_posts.posts posts
    JOIN categories ON COALESCE(categories.id, 0) = COALESCE(posts.category_id, 0)
  )
FROM user_posts

2. Range型で同じ値を扱えない

BigQueryには日付やタイムスタンプの区間を表す Range 型がありますが、開始値と終了値が同じ場合はエラーとなります。

SELECT RANGE(DATE '2025-04-01', DATE '2025-04-01')

ただし、日付を DATETIME に変換すれば、この問題を回避できます。

SELECT RANGE(DATETIME '2025-04-01', DATETIME '2025-04-01 23:59:59')

ちなみに、この Range 型を利用して特定の日付で絞り込みをしたい場合は、以下のように RANGE_CONTAINS 関数を使用できます。

# range_date が Range 型と仮定
SELECT * FROM samples WHERE RANGE_CONTAINS(range_date, CURRENT_DATE)

3. 他テーブルのカラムを利用して別テーブルを絞り込んだ結果から1行だけ取得したい場合の対処法

これは少し特殊なケースかもしれません。例えば、以下のようなデータ構造があるとします。

上記のテーブルで、「各ユーザーの登録日時点に所有していた車のうち、最も古い車を1台だけ取得したい」という要件を考えます。この場合、以下の「例1」や「例2」のようなクエリを記述したくなるかもしれませんが、これらはエラーとなるため、目的の結果は得られません。

-- 例1
SELECT
  users.name,
  (
    SELECT
      name
    FROM user_cars
    WHERE user_id = users.id
    AND (
      (user_cars.start_date <= users.registration_date AND user_cars.end_date IS NULL)
      OR (users.registration_date BETWEEN user_cars.start_date AND user_cars.end_date)
    )
    ORDER BY user_cars.start_date LIMIT 1
  )
FROM users

-- 例2
SELECT
  users.name,
  user_cars.name
FROM users
JOIN (
  SELECT
    name
  FROM user_cars
  WHERE user_cars.user_id = users.id
  AND (
    (user_cars.start_date <= users.registration_date AND user_cars.end_date IS NULL)
    OR (users.registration_date BETWEEN user_cars.start_date AND user_cars.end_date)
  )
  ORDER BY user_cars.start_date LIMIT 1
) user_cars

しかし、以下のように QUALIFY 句と ROW_NUMBER() 関数を使用することで、この問題を解決できます。user_id でグループ化し、start_date の昇順で順位を付けた結果、順位が 1 の user_cars に絞り込みを行っています。

SELECT
  users.name,
  user_cars.name
FROM users
JOIN user_cars ON user_cars.user_id = users.id
  AND (
    (user_cars.start_date <= users.registration_date AND user_cars.end_date IS NULL)
    OR (users.registration_date BETWEEN user_cars.start_date AND user_cars.end_date)
  )
QUALIFY ROW_NUMBER() OVER(PARTITION BY user_cars.user_id ORDER BY user_cars.start_date ASC) = 1

私もまだBigQueryについては学習中ですので、もし他にもっと良い方法をご存知でしたら、ぜひ教えていただけると嬉しいです。

4. 特定のカラムを除外してSELECTする

例えば、created_at と updated_at 以外のすべてのカラムを選択したい場合は、以下の構文を利用できます。

SELECT * EXCEPT(created_at, updated_at)

5. 0除算の回避

SQLで0除算を行おうとするとエラーが発生しますが、BigQueryの SAFE_DIVIDE 関数を使えば、エラーを回避できます。この関数を使用すると、0で割った結果は NULL が返されます。

SELECT SAFE_DIVIDE(x, 0)

6. GROUP BY ALL

グループ化をする場合、通常は集計関数以外の全てのカラムを GROUP BY 句に記述する必要がありました。しかし、GROUP BY ALL を利用することで、SELECT 句に指定されている集計関数以外の全てのカラムで自動的にグループ化を行うことができます。

SELECT
  id,
  name,
  SUM(number)
FROM samples
GROUP BY ALL