気合でカレンダーらしきものを作る

結局いらなかったけど、せっかく作ったからメモ。

平日が必要になった

社内用の集計SQLを作ってたのだが、「平日」が必要になった。システムの中には祝日マスタテーブルがあったので祝日はさくっと消せるのだが、土日をどうするかって事になった。
まあ、to_dateの'D'オプション指定すれば曜日はわかるので、簡単だろーと思ってたのだが、よく考えればプログラムで取るほど簡単じゃない。欲しいのは「指定した日付から何日間」みたいな感じでよいのだが、SQLにはforループは無い。
さてどうしようと途方に暮れていたのだが、SQLパズルにヒントがあった。

• 3-8 カレンダーを表示その1 - Oracle SQLパズル

なるほど、行番号使えば数値でループできるな、さすがパズル。

でもPostgreSQL8.3にはrow_numberが無いのです。

無いのは無いのですが、再帰集合で連番作る手法は知ってたのでそれで代用しようと試みました。
参考にするならこことか。

• 第1回　連番の特性を利用してデータ操作をもっと自由に　SQLで連番を扱う　（2）行集合に連番を割り当てる - SQLアタマアカデミー

ただ、今回はホントにテーブルも無い状態なのでpg_tablesを使います。スキーマとかを一覧してくれるやつです。
決して整ってはないけどこんな感じ。

select to_date(:startdate, 'yyyy/MM/dd') + (rownum.no::integer - 1) + :term as date
      ,to_char(to_date(:startdate, 'yyyy/MM/dd') + (rownum.no::integer - 1) + :term, 'D') as dayofweek
from (select (select count(*) + 1
              from pg_tables as x
              where x.tablename < y.tablename
             ) as no
      from pg_tables as y
      order by no
     ) rownum
where rownum.no::integer <= (:term + 1)

これでdayofweek列に曜日を表す数値が入るので、それをさらに絞り込んで祝日マスタと合わせれば平日だけ取り出せますね。

結局オチは

カレンダーテーブルってのがあったらしい。もっと早く相談しとけばよかったorz