【BigQuery基礎】WITH句でクエリの可読性を上げる

BigQueryで複雑な集計をしているとクエリが500行を超えることもあります。

その行数のクエリを１つのSELECT文で書こうとした結果、以下のような悩みを抱えることはありませんか？

FROM句の中にJOINしたクエリが並んでいて途中で頭が混乱する…
翌日見返すと全く読み解けない…

これらの悩みはWITHで解決できます！

こちらの記事ではWITH句の基本的な使い方について解説します。

WITH句を使うメリット
WITH句の使い方
よくあるWITH句でのエラー
まとめ

WITH句を使うメリット

間違わずに集計できる

SQLでクエリを書く際に集計結果の正確性を気にするケースは多いのではないでしょうか？

このクエリで集計したレポートは100人が見るBIツールで使用される。
経営者に見せるデータを集計する。

など、集計結果に重い責任が乗るケースはあるかと思います。

WITH句を使うことで、個別のSELECT文が理解しやすくなり、結果の検証もしやすくなるため、クエリの正確性が上がります。実際、私の周りでも集計を間違いやすいBigQuery初心者はWITH句を使っていないケースがほとんどです。

レビューが簡単にできる

他のメンバーが書いたクエリをレビューする際に、WITH句を使っていない場合、読み解くのがかなり困難です。

個別のクエリは保守前提で書かれないことが多く、積極的にコメントを記載する文化の組織は少ない印象を持っていますが、コメントが記載されていなかったとしてもWITH句があるだけでかなり読み解く難易度は下がります。

個人の経験的にクエリの可読性は「WITH句の有無 >>> インデントルール > コメント」で影響されます。一見手間は増えそうですが実際の作成労力は変わらないので是非WITH句を使用してみてください。

WITH句の使い方

WITH句を使わないクエリ

SELECT 
    b.name,
    SUM(a.sales) AS sales
FROM 
(
    SELECT 
        id,
        sales
    FROM 
        sales_data 
    WHERE
        date >= DATE("2021-01-01")
) a
JOIN 
(
    SELECT 
        id,
        name
    FROM 
        master_data 
    WHERE 
        name = "hoge"
) b
ON
    a.id = b.id
GROUP BY 
    b.name

WITH句を使うクエリ

WITH
current_sales_data AS (
    SELECT 
        id,
        sales
    FROM 
        sales_data 
    WHERE
        date >= DATE("2021-01-01")
),
hoge_master_data AS (
    SELECT 
        id,
        name
    FROM 
        master_data 
    WHERE 
        name = "hoge"
)

SELECT 
    b.name,
    SUM(a.sales) AS sales
FROM 
    current_sales_data a
JOIN 
    hoge_master_data b
ON
    a.id = b.id
GROUP BY 
    b.name

使い方は簡単で、

「WITH」をクエリの先頭に記載する
「サブクエリ名 AS (クエリ)」を記載する
サブクエリをテーブルのように扱って出力するクエリを記載する

になります。サブクエリを複数連ねるときはカンマで区切って並べることが可能です。例では2つのみ並べていますが3つ以上並べることも可能です。

今回の例はシンプルなクエリでしたが、実業務ではクエリが複雑で行数も増えるため、WITH句を使ったほうが明らかに読みやすくなります。

WITH句は標準SQL（Standard SQL）で使うことが可能です。レガシーSQLでは対応していませんが、現バージョンのBigQueryはデフォルトで標準SQLなので特に考慮は不要です。

よくあるWITH句でのエラー

途中のサブクエリでカンマが抜ける

WITH
current_sales_data AS (
    SELECT 
        id,
        sales
    FROM 
        sales_data 
    WHERE
        date >= DATE("2021-01-01")
)
hoge_master_data AS (
    SELECT 
        id,
        name
    FROM 
        master_data 
    WHERE 
        name = "hoge"
)

SELECT 
    b.name,
    SUM(a.sales) AS sales
FROM 
    current_sales_data a
JOIN 
    hoge_master_data b
ON
    a.id = b.id
GROUP BY 
    b.name

このクエリを実行すると以下のエラーメッセージが表示されます。

Syntax error: Expected "(" or "," or keyword SELECT but got identifier "hoge_master_data" at [25:1]

１つ目のサブクエリのカッコの後にカンマが抜けているのが原因なので、カンマを付けることでエラーが解消されます。

最後のサブクエリにカンマが付いている

WITH
current_sales_data AS (
    SELECT 
        id,
        sales
    FROM 
        sales_data 
    WHERE
        date >= DATE("2021-01-01")
),
hoge_master_data AS (
    SELECT 
        id,
        name
    FROM 
        master_data 
    WHERE 
        name = "hoge"
),

SELECT 
    b.name,
    SUM(a.sales) AS sales
FROM 
    current_sales_data a
JOIN 
    hoge_master_data b
ON
    a.id = b.id
GROUP BY 
    b.name

このクエリを実行すると以下のエラーメッセージが表示されます。

Syntax error: Trailing comma after the WITH clause before the SELECT clause is not allowed at [35:1]

最後のサブクエリのカッコの後のカンマを削除することでエラーが解消されます。

まとめ

こちらの記事では、

WITH句を使うメリット
WITH句の使い方

について解説しました。

初心者はWITH句を敬遠して１つのクエリで記載してしまいがちなケースは多いと思いますが、WITH句は簡単に使いこなせるので是非日々の集計で活用してみてください！