はじめに

BigQueryでは中間的なクエリ結果をCTEとして記述することができます。このCTEは通常マテリアライズ化されません。

GoogleSQL only materializes the results of recursive CTEs, but doesn't materialize the results of non-recursive CTEs inside the WITH clause. If a non-recursive CTE is referenced in multiple places in a query, then the CTE is executed once for each reference. The WITH clause with non-recursive CTEs is useful primarily for readability.
( GoogleSQLは再帰CTEのみマテリアライズ化し、非再帰のCTEではWITH句をマテリアライズ化しません。非再帰CTEが複数の場所で参照されると、そのCTEは参照される度に毎回実行されます。非再帰CTEは可読性を向上させるためのものです。)
https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax#with_clause

しかし、これでは複雑なCTEや大規模なデータを集約した場合などで、無駄に何度も同じクエリを実行されることになります。

テーブルを作り明示的にマテリアライズ化することもできますが保存先を決める必要があり、また一時テーブルを利用するとクエリキャッシュが利用できなくなります。

そこで、再帰CTEを利用してみます。上記引用文のように再帰CTEはマテリアライズ化されます。再帰CTEにするためには"WITH RECURSIVE"と記述する必要がありますが、それだけでは再帰CTEとして扱われません。実際に自分自身のテーブルを参照する必要があります。

それでは試してみましょう。

通常のCTE(非再帰)

まず、通常のCTEで、with句のテーブルを複数回参照するクエリを実行してみます。
違いがわかりやすいように乱数を返すCTEを使ってみます。

with 
  cte as (
    select rand() as value
  )
select * from cte
union all
select * from cte

異なる乱数が表示され、CTEが2回実行されていることがわかります。

実行計画を見ても、rand()関数が二回呼び出されていることがわかります。

CTE(recursive指定)

次に"recursive"を指定してみましょう。

with recursive
  cte as (
    select rand() as value
  )
select * from cte
union all
select * from cte

やはり違う乱数が出力されます。単に"with recursive"と書くだけでは再帰CTEとして扱われないようです。

再帰CTE(自己参照)

そこで、"union all"で自分自身を参照してみます。実際に行が増えないように条件"where false"を指定してみます。

with recursive
  cte as (
    select rand() as value
    union all
    select * from cte where false
  )
select * from cte
union all
select * from cte

同じ乱数が出力されました！ CTEがマテリアライズ化されているようです。

実行計画を見ても、計算を2回行うのではなく、計算結果が2回参照されているようです。

使い所

実際には、複数回CTEを実行した方が早いことも多いと思いますが、複雑なCTEや、大きいテーブルを集約した場合など、実行が速くなったりスロットが節約できることもあると思います。