BigQueryのwith句の使い方〜可読性の高いSQLを書こう〜

BigQuery でサブクエリを多用すると、複雑で可読性が低くなる場合があります。そういうときは、with 句が最適です。

間違いにも気づきやすいですし、他の人に見てもらうときにもわかりやすくなります。

本記事では with 句の具体的な使い方、可読性を高める方法をご紹介します。

with 句の使い方

with 句は事前にクエリを定義するものです。メイン処理の中で定義したものを呼び出せます。

with 句なしクエリ

# 商品、カテゴリごとの閲覧数
SELECT
  product_name,
  category_name,
  COUNT(fullVisitorId) AS view_count
FROM (
  SELECT
    s.fullVisitorId,
    s.product_name,
    c.category_name
  FROM
    `sample.testdata.sessions` s # サブクエリ：セッションテーブル
  INNER JOIN (
    SELECT
      product_name,
      category_name
    FROM
      `sample.testdata.category` # サブクエリ：カテゴリテーブル
    GROUP BY
      1,
      2 ) c
  ON
    c.product_name=s.product_name
  WHERE
    s.type = 'PAGE'
  GROUP BY
    1,
    2,
    3)
GROUP BY
  1,
  2

with 句ありクエリ

WITH
  sessions AS ( # セッションテーブル
  SELECT
    s.fullVisitorId,
    s.product_name
  FROM
    `sample.testdata.sessions`
  GROUP BY
    1,
    2,
    3 ),
  category AS ( # カテゴリテーブル
  SELECT
    product_name,
    category_name
  FROM
    `sample.testdata.category`
  GROUP BY
    1,
    2 )

SELECT # メインクエリ:商品、カテゴリごとの閲覧数
  s.product_name,
  c.category_name,
  COUNT(s.fullVisitorId) AS view_count
FROM
  sessions s
INNER JOIN
  category c
ON
  s.product_name=c.product_name
GROUP BY
  1,
  2

with 句なしクエリは、サブクエリが書かれてある箇所でクエリ全体の流れが中断されてしまう印象があります。

with 句ありクエリは、サブクエリが事前に書きだされ、メイン処理と独立しています。

こうすることで、サブクエリの中身も、メイン処理の流れも把握しやすくなります。

with 句は複数定義が可能です。最初に WITH を書いた後は、カンマ区切りで付け足します。

WITH
  aaa AS (
  SELECT id FROM a 
  ),
  bbb AS (
  SELECT id FROM b 
  ),
  ccc AS (
  SELECT id FROM c 
),...

可読性を高める方法

with 句以外で可読性を高める方法をご紹介します。

クエリを書式設定

インデントをそろえると可読性が良くなります。

BigQuery は書式設定ができます。

コンソールから 展開>クエリを書式設定、もしくはキーボードから command(control) + shift + F を同時に押すことで、書式が整います。

大文字・小文字をそろえる

大文字と小文字の使いわけをすると読みやすくなります。

予約語は大文字、それ以外は小文字とするパターンが多いです。

SELECT # 大文字
  event_date, # 小文字
  event_name,
  COUNT(pv)
FROM
  test_table
GROUP BY
  event_date, 
  event_name

まとめ

with 句の使い方、可読性を高める方法についてご紹介しました。

with 句は便利ですが、定義する数が多すぎると逆に見にくくなります。

そういう時はあらかじめテーブルやビューを作成しておくと良いです。

5つくらいなら with 句の定義でも良いかと個人的には思います。

この記事が参考になれば幸いです！