BigQueryの標準SQL -with句って便利だね- 編

BigQueryを使うシステムの開発中、レガシーSQLでだらだらと1000行を超えるようなクエリを目の前にして絶望を感じることはありませんか？

レガシーSQLのFROM句の中にSELECT文によるクエリが50個くらいカンマ区切りで並んでいて、圧倒されることはありませんか？

ありますよね、きっと。

それ、標準SQLで解決できるかもしれません。（注：できないことも多々あります）

下のようなレガシーSQLがあるとしましょう。

SELECT
  *
FROM (
  SELECT
    *
  FROM (
  -- 任意のSELECT文 (a)
  )
  LEFT JOIN (
  -- 任意のSELECT文 (b)
  ) ON a.a = b.a
  LEFT JOIN (
  -- 任意のSELECT文 (c)
  ) ON a.pk = c.pk
  LEFT JOIN (
  -- 任意のSELECT文 (d)
  ) ON c.pk = d.pk
)
WHERE
  c.col3 = 'hoge'

これを標準SQLのWITH句を利用すると次のように書けます

WITH A AS (
-- 任意のSELECT文(a)
),  B AS (
-- 任意のSELECT文(b)
), C AS (
-- 任意のSELECT文(c)
), D AS (
-- 任意のSELECT文(d)
), E AS (
    SELECT * FROM A LEFT JOIN B ON A.a = B.a
)
SELECT
    *
 FROM
    C
LEFT JOIN D ON c.pk = d.pk
LEFT JOIN E ON c.pk = e.pk
WHERE
    C.col3 = 'hoge'

これだけでは何が便利かよくわからないと思います。

任意のSELECT文に対してA, B, C, D, Eなどと名前がつけられ、またそれをテーブルのように扱うことができます。

似たようなSQLが繰り返し出てきてわかりにくいときなど、このWITH句で名前をつけることによって繰り返しをテーブルのように扱い、SQLを簡潔に書くことができるようになります。

今回の例では「Cの結果とDの結果とEの結果を左外部結合したもののうちCの3列目が'hoge'であるものと抽出したい」という要件がレガシーSQLと比較して容易に読み取れるように・・・なってないなぁ、例が悪いなぁ・・。

実際の業務で扱うテーブルではそれぞれのSELECT文が複雑怪奇なので、レガシーSQLと比較すると明らかにWITH句を利用した標準SQLの方が読みやすいのですが、ちょっと適当な例が思い当たりませんでしたorz