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 を同時に押すことで、書式が調います。

bigqury書式設定

大文字小文字を揃える

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

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

予約後SELECT, FROM, WHERE, GROUP BY, ORDER BY,
DISTINCT, COUNT, MAX, MIN, CASE など
SELECT # 大文字
  event_date, # 小文字
  event_name,
  COUNT(pv)
FROM
  test_table
GROUP BY
  1, 2 # event_date, event_name

まとめ

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

with句は便利ですが、定義する数が多すぎると見ずらくなってしまいます。

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

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

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

  • このエントリーをはてなブックマークに追加