読者です 読者をやめる 読者になる 読者になる

13 日の金曜日を求める SQL の解説

昨日書いた SQLよくわからない認定を受けたのでちょっと解説しておきますね。


えー、書いた SQL はこんなのです*1

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

*1:year は初め終了条件に使おうと思って付けてたけど、結局 day から取得する形にしたから削った

*2:最近と言っても SQL99

*3:そのくせテーブル設計は残念な感じ