概要
WITH句を複数使ってデータ処理をするような複雑なSQLを作るときに、①1つのSQLファイルで複数のWITH句をつなげるのか②1つの処理毎にテーブル生成をするのかどちらが良いかを考えてみました。
ググっても同じようなことを考えている記事をパッと見つけられなかったので、あまり需要がないかもしれませんが、SQLの書き方や考え方の参考になれば幸いです。
注意点
- 独学でSQLを勉強し、実務でデータ分析を生業にしている人間の個人的な意見です。
- システム基盤、データ基盤の設計のためのSQLというよりもデータ分析のために必要なデータ抽出や加工をすることを前提にして考察しています。設計には設計におけるお作法などがあると思うので、話半分程度に読んでもらえれば幸いです。
問題設定
次のSQLのようにいくつかのサブクエリ（WITH句）を使って何かの処理を実現したいという状況だとします。
例えば、複数のデータテーブルを使い、いろいろな加工を施しながら最終的には集計対象となるユーザの数をカウントする、など。
WITH table1 AS (
-- 処理①
)
,table2 AS (
--処理②
)
...
,tableN AS (
--処理Ⓝ
)
SELECT
COUNT(DISTINCT userID)
FROM tableN
上のSQLのように1つのSQLファイルで複数のサブクエリ（WITH句）を連結させることで実現したいことを達成する方法もあります（これをパターン①と呼びます）が、もう一つやり方があると思います。それは一つ一つの処理でテーブルを生成してしまう、と言うやり方です（こちらをパターン②と呼びます）。↓に例を出します。
SQL①
CREATE OR REPLACE TABLE table1
(
-- 処理①
)
SQL②
CREATE OR REPLACE TABLE table2
(
-- 処理②
)
SQLⓝ
WITH tableN AS (
-- 処理Ⓝ
)
SELECT
COUNT(DISTINCT userID)
FROM tableN
このように、一つ一つの処理でテーブルを生成するクエリを作ってもWITH句を連携させて作った場合と同じ結果を得ることができます。
今回はこの2つの方法のいずれが良いかを考察してみました。
考察
比較観点
どちらが良いかを判断するにはどのような観点（軸）で比較するべきかを考えます。
今回は次の5つがその観点になると思います。
- ファイル管理の煩雑さ
- データテーブル数
- デバッグのしやすさ
- 解釈性
- 実行可能性
ファイル管理の煩雑さ
基本的にファイル数が多くなればなるほどファイルの管理（バージョンなど）が煩雑になります。今回の場合パターン①ではSQLファイルが1つで済むので、ファイル管理は大変ではありません。
一方でテーブルを毎回生成するパターン②の場合は、必要なテーブルの数だけSQLファイルが必要となるため、ファイル管理が大変になると思います。実現したいことが1つだけであればまだ管理できるかもしれませんが複数の実現したいこと（例：分析レポート）が出てきた場合にはどれがどのテーブルを生成するSQLなのかを管理しきるのが非常に大変になると思います。
データテーブル数
パターン①ではSQLを実行するために生成されるテーブルはせいぜい1個（CREATEしないのであれば0個）ですが、パターン②では分割したSQLファイルの数だけテーブル数が増えます。使っているDBMSにもよりますが、ファイル管理の煩雑さ同様にテーブルの管理が大変になることが想定できます。また、データテーブルの更新忘れといった運用ミスの発生リスクもあります。
クラウドのDBサービス（例：GoogleのBigQueryやAWSのRedshift, RDS等）を使っている場合は課金形態にもよりますが、ストレージ料金やテーブル数などで課金される場合もあるため、コスト面でもパターン②は考慮する必要があります。
しかしパターン①では1回のSQL実行にかかるスキャン量がパターン②よりも大きくなる可能性もありその場合はパターン①もコストを気にする必要が出てきます。課金形態やどのくらいの頻度でSQLを実行するのか、1回あたりのスキャンにかかるコストなどを総合的に考えなくてはいけません。
デバッグのしやすさ
パターン①は、すべての処理が同じファイルで行われているため、パターン②と比べてデバッグがしづらいと思います。例えば、table③の処理で問題があり、かつそのtable③がtable①②を使っているような状況だったとします。SQLで書くと次のような状態だったとします。
WITH table1 AS (
-- 処理①
)
,table2 AS (
-- 処理②
)
,table3 AS (
SELECT *
FROM table1
LEFT JOIN table2
ON table1.col1 = table2.col2
)
...
SELECT
COUNT(DISTINCT userID)
FROM tableN
この場合、パターン②ではtable③の生成SQLを修正すればtable③を独立に動かして見て、バグの検証/修正ができます。しかし、パターン①ではtable①②③のみを実行できるような状態にする必要があるので、
- table④以降をコメントアウト
- 最後のSELECT句をtable③の検証用に書き換える
という作業が発生します。
パターン②よりもSQL改変すべき量が多いです。
また、table③だけに問題があればパターン①でも上の様にSQLを改変すれば検証できますが、仮にほかのtableでも問題がありそうだとなった場合は、特定のか所をコメントアウトして最後のSELECT句を検証用に書き換える、という作業が毎回発生するのでデバッグに時間がかかります。
ので、デバッグのしやすさについてはパターン②の方がラクだと考えています。
解釈性
「解釈性」という言葉に解釈多様性が含まれるので、一度今回の記事の中身での定義を記載します。
今回は解釈性という言葉の意味するところは、「SQLの中身の理解しやすさではなく、処理全体の流れの理解しやすさ」と定義します。SQLである以上、「インプットとなるデータが存在し、SQLが加工/処理を施すことでアウトプットが出てくる」という処理の流れがあるわけですが、パターン②ではこの流れがつかみづらいのではないかと考えています。
例えば、「デバッグのしやすさ」で例示しているSQLを使って考えると、
インプットとなるテーブル
↓
table1
↓
table2
↓
table3
↓
…
という処理の流れになっているわけですが、パターン①ではこの流れがつかみ取りやすいのですが、パターン②では物理的にファイルが分割されてしまっているため、
SQL①
インプットとなるテーブル
↓
table1
SQL②
インプットとなるテーブル
↓
table2
SQL③
SQL①とSQL②の結果のテーブルがインプットテーブル
↓
table3
とぶつ切り状態になるため、頭の中で処理をトレースするにしても思考がぶつ切りになってしまう感覚を覚えます。
実行可能性
筆者がGoogleのBigQueryをメインに触っているため、他のサービスやDBMSではどうなのかわからないのですが、BigQueryでは、サブクエリの数が多すぎると実行不可になってしまいます。そのためパターン①ではWITH句の数をあまり多くしすぎことはできません。具体的に何個のサブクエリで実行不可になるのか？というのは無いです。筆者の肌感ですが、15個くらいのwith句であればいけますが、一つのwith句が非常に長かったり処理内容が多い（case whenやifなどを多用している）と実行可能なwith句の数はもっと少なくなります。
パターン②ではこのような問題は発生しないので、実行可能性を気にする必要はないです。
まとめ
これまでの話をまとめると、下の表の結果となります。
| パターン① | パターン② | |
|---|---|---|
| ファイル管理の煩雑さ | 〇 | △ |
| データテーブル数 | 〇 | △ |
| デバッグのしやすさ | △ | 〇 |
| 解釈性 | 〇 | △ |
| 実行可能性 | 〇 | 〇 |
（あまり使いたくないのですが）時と場合に応じてパターン①の作り方にするか②にするかを決める必要があるかなと思います。
自分がどっちを好ましいと感じるか
複数人のPJであれば管理の煩雑さを避けるためのパターン①にする
デバッグのしやすさを重視したいからパターン②にする
といった優先度を基にしてどちらにするか決めると良いのかなーと思います。