[SQL]with句の効用

[SQL]SQLの共通表式ではSQLのwith句でテストデータの生成ができるってことを書いたけど、それ以外にもwith句には効用がある。

・サブクエリにコメントを付けておこう。
SQLだと他のソースコードに比べて、コメントがおろそかになっていることが多いけど、サブクエリ毎にコメントを付けておくとわかりやすいんじゃないだろうか。

サンプル用のテーブルはこんな感じ。
IDをキーにして、開始日と終了日を持っているとしよう。

Periods

Column	Type
id	Char(5)
start_day	Date
end_day	Date

このPeriodsテーブルから開始日と終了日が重なっている期間をつなげて、つながった期間の開始日と終了日を抽出するとしよう。

/*
 * 重複した期間をつなげて、つなげた期間の開始日と終了日を抽出する
 *
 * table: Periods
 * output: start_day, end_day
 */
WITH
  -- 期間の先頭(開始日)を抽出
  -- 抽出条件:
  -- p1の開始日がどの期間とも重複しない. 以下のような期間が存在しない.
  -- p1      +---------------+
  -- p2 +---------------+
  Period_starts(start_day) AS (
    SELECT DISTINCT start_day FROM Periods p1
     WHERE NOT EXISTS (SELECT * FROM Periods p2
                        WHERE p2.start_day <  p1.start_day
                          AND p1.start_day <= p2.end_day)
  )
  ,
  -- 期間の最後(終了日)を抽出
  -- 抽出条件:
  -- p1の終了日がどの期間とも重複しない. 以下のような期間が存在しない.
  -- p1 +---------------+
  -- p2      +---------------+
  Period_ends(end_day) AS (
    SELECT DISTINCT end_day FROM Periods t1
     WHERE NOT EXISTS (SELECT * FROM Periods p2
                        WHERE p1.end_day   <  p2.end_day
                          AND p2.start_day <= p1.end_day)
  )
  ,
  -- 期間(開始日, 終了日)を抽出
  -- 抽出条件:
  -- 開始日と開始日以降で最も早い終了日を紐付ける.
  Overlapped(start_day, end_day) AS (
    SELECT satrt_day, MIN(end_day) AS end_day FROM Period_sarts, Period_ends
     WHERE start_day <= end_day
     GROUP BY start_day
  )
--
-- 最終結果(開始日, 終了日)
-- 開始日でソート
SELECT start_day, end_day FROM Overlapped
 ORDER BY start_day;

with句だとこのコメントが書きやすく文書化できて、見通しが良くなる。
ちょうど、CやJavaで関数毎にコメントを付けているのに相当するね。

・外側のクエリは単純にしておこう。
with句の外側のクエリを最終結果をソートしたりするだけの単純なクエリにしておくと、クエリ全体をテスト、デバッグしやすくなる。

先の例を使って外側のクエリだけを変えてみよう。

WITH
  ...WITH句の中は前と同じ...
--
-- Period_endsの結果だけを取得(終了日)
-- 終了日でソート
SELECT end_day FROM Period_ends
 ORDER BY end_day;

これで、Period_endsサブクエリの結果だけが返される。with句の中を書き換えることなく、Period_endsサブクエリの動作を確認できる。

同じように

WITH
  ...WITH句の中は前と同じ...
--
-- Period_startsの結果だけを取得(開始日)
-- 開始日でソート
SELECT start_day FROM Period_starts
 ORDER BY start_day;

とやっていけば、Period_startsから最終結果までを順を追って動作を確認することができる。

残念ながら相関サブクエリはその性質上、それ以上分解できないけど、ある程度テスト、デバッグはしやすくなるんじゃないだろうか。