13 日の金曜日を求める SQL の解説
昨日書いた SQL、よくわからない認定を受けたのでちょっと解説しておきますね。
WITH Cal(day/*, year*/) AS ( SELECT GETDATE() -- , YEAR(GETDATE()) UNION ALL SELECT DATEADD(dd, 1, day) -- , year FROM Cal WHERE YEAR(DATEADD(dd, 1, day)) < 2014 ) , Friday13th(day) AS ( SELECT day FROM Cal WHERE DAY(day) = 13 AND DATEPART(dw, day) = 6 ) SELECT CONVERT(varchar, day, 111) FROM Friday13th UNION ALL SELECT CAST(COUNT(*) AS varchar) FROM Friday13th OPTION (MAXRECURSION 1700)
共通表式 WITH
WITHは共通表式 (common table expression) と言って、比較的最近標準に追加されたもの*2なので、知らない人もいるかもしれない。要はビューです。
例えば、以下の WITH を使った SQL は、
WITH X(col) AS ( SELECT col_a FROM SomeTable ) SELECT col FROM X
インラインビューを使って以下のように書き換えることができる。
SELECT col FROM (SELECT col_a AS col FROM SomeTable) X
どういうときに共通表式を使うかと言うと、
- インラインビューにするには複雑すぎる
- インラインビューだと複数個所に書かなければいけない
- 再帰 SQL が書きたい
のいずれかに当てはまるとき。
とは言っても、単純なインラインビューなんて本当にごく稀だから、インラインでビューが書きたいと思ったら即 WITH 使う、って感じ。
で、ちょっと脱線するけど、複数の SQL で同じような共通表式を書く必要が出てきたら、それは本来 CREATE VIEW でビューにすべき。
ただ、ビューを全く使わないプロジェクト*3ってあるんだよね・・・
そういうプロジェクトでは共通表式のままにしておくしかない。まぁ、そういうプロジェクトでは共通表式すら使えない可能性もあるけど・・・
話を戻して、共通表式。
投稿した SQL では、Cal と Friday13th という共通表式を定義して使っている。
Cal は、実行日から 2013 年 12 月 31 日までのカレンダーとなる。これは単純な共通表式ではなく、再帰を使っているので後で説明する。
Friday13th は、Cal から 13 日の金曜日のみを抽出している。
Friday13th は 2 箇所で使われているから、Friday13th を作らなかった場合は Friday13th の中の SELECT を 2 箇所に書く必要がある。
これはだるいし修正が入った際に修正忘れの原因にもなるので、積極的に共通表式を使用するべきところ。
WHERE 句の DAY(day) = 13 の部分はいいでしょう。ただ 13 日を抽出しているだけ。
DATEPART(dw, day) = 6 の部分で金曜日を抽出しているんだけど・・・この部分はコメント書くか、変数にしてしまってもいいかもしれない。
再帰 SQL
前述したように、Cal は再帰を使っている。
Cal の中身は、以下のようになっている。
SELECT GETDATE() UNION ALL SELECT DATEADD(dd, 1, day) FROM -- ここでCal自身を使っている Cal WHERE YEAR(DATEADD(dd, 1, day)) < 2014
このように、コメントを追加した部分で Cal 自身を使っている。
再帰 SQL では、通常のプログラミング言語の再帰とは違い、開始点から記述する。開始点となっているのは、UNION ALL より上の部分で、今日の日付を持っている。
そして、UNION ALL で再帰部分を行連結している。
再帰部分では、前の日付 (day) の次の日を SELECT 句で指定し、WHERE 句で再帰の終了条件を記述している。
WHERE 句には次の日の年が 2014 より小さいとなっているので、2013 年 12 月 31 日の次の再帰で WHERE 句が FALSE となり、再帰が終了する。
SQL Server 独自の話だけど、SQL Server ではデフォルトで再帰の回数が 100 に制限されているから、それ以上の回数再帰がしたい場合は OPTION で設定する必要がある。
今回は、今日から 20013 2013 年の末までなので、1600 あれば足りるかなー、と思って最初は 1600 に設定したんだけど、ちょと足りなかったみたい。ここもコメント必要だよなー。
本体
本体は、13 日の金曜日を表示する部分と個数を表示する部分に分かれている。
SELECT CONVERT(varchar, day, 111) FROM Friday13th UNION ALL SELECT CAST(COUNT(*) AS varchar) FROM Friday13th
CONVERT 関数は SQL Server の関数で、第二引数を第一引数で指定した型に変換する。
第三引数は変換の書式で、111 は yyyy/MM/dd 形式に変換してくれる。
これに 13 日の金曜日が何回出るかを文字列に変換してくっ付けて、完成！
これでもう読めるはず・・・！
てことでコメントつけて不要部分削除したもの載せときますねー
WITH Cal(day) AS ( -- 2013/12/31までのカレンダー SELECT GETDATE() UNION ALL SELECT DATEADD(dd, 1, day) FROM Cal WHERE YEAR(DATEADD(dd, 1, day)) < 2014 ) , Friday13th(day) AS ( SELECT day FROM Cal WHERE DAY(day) = 13 -- 13日の AND DATEPART(dw, day) = 6 -- 金曜日 ) SELECT CONVERT(varchar, day, 111) -- yyyy/MM/dd形式に変換 FROM Friday13th UNION ALL SELECT CAST(COUNT(*) AS varchar) FROM Friday13th OPTION (MAXRECURSION 1700) -- 再帰回数の制限を設定 -- 2013/12/31までは4.5年ちょいなので、1700あれば足りる -- 1700 / 366 ≒ 4.6
