Oracle® 非公式 SQL, PL/SQL & DBA's リファレンス

目的別に複数のカレンダーで祝日・休日判定と営業日などを数えたい

祝日定義情報から任意の期間の祝日一覧(実表)を事前作成し、SQL で祝日・休日を判定や営業日の日数の計算を容易にできるようにする。
目的別に複数のカレンダーを使い分けることで休日の異なる複数国化やプロジェクトのスケジュール管理などにも使用できる。

機能の概要と目標

祝日定義テーブルから「一定の期間の祝日一覧」(実表)を自動生成する。

実装方針と内容

• 祝日・休日の定義をテーブルに保存し、定義とロジックを分断する
• 祝日・休日の定義の方法は簡易な表記にする
• 複数の休日カレンダーをカスタマイズ可能でマージや使い分けることができる
• 祝日定義のデータが更新されたときは祝日・休日の一覧を自動で更新する
• TO_CHAR(d,dy,day) や NEXT_DAY は国ごとに異なる戻り値や引数が必要、これを共通化させる
• 各種日数を手軽に数えるためのビュー(CALENDAR)を作成する

必須環境は Oracle 11g R2 以降 (Oracle EE 11.2.0.1.0 では動作確認)
WITH 句 中のカラムの別名を使用しなければ Oracle 11g R1 以前でも動作するかもしれない。

日本の祝日、日本と振替判定が似ているアメリカの祝日、振替処理のないデフォルト用の3つのカレンダーを組み込んである。

複数のカレンダー（各国語対応）も柔軟に組み込み可能な実表を更新する形式とした。
※ さまざまな国の休日の決定方法を既に実装してあるわけではない。
実装してあるのは、日曜祝日を月曜側に振替、土曜の祝日を金曜側に振替と日本の「国民の休日」

• 機能追加や検討したほうが良さげなもの

・プライマリ・タグ別に休日一覧作成する期間を設定する定義体やテーブル(現在はプログラム内で決め打ち)
・索引の追加：複数のプライマリ・タグで土日を休日にし100年単位で一覧を作るとレコードが結構多くなる

祝日・休日一覧の作成に必要な DDLとプログラム

祝日定義表(HOLIDAY_META) の更新時に、休日一覧出力 (HOLIDAY_LIST 表) へ自動更新させるためには以下の権限が必要

conn / as sysdba
GRANT EXECUTE ON DBMS_CQ_NOTIFICATION TO [usr_name];
GRANT CHANGE NOTIFICATION TO [user_name];

※ 時間経過を契機とする自動更新は含まれておらず別途ジョブスケジューラなどで更新が必要。

祝日定義例 (HOLIDAY_META 表)

祝日定義 date_str	日付種別 date_type	祝日名称 date_name	適用開始日 start_on	失効日 expire_on	タグ tag
M01D01	10	元日	1948-07-20		[JP]
M01W2D01	10	成人の日	2000-01-01		[JP]
M02D11	10	建国記念の日	1966-06-25		[JP]
M04D29	10	みどりの日	1989-02-17	2007-01-01	[JP]
M02D23	10	天皇誕生日	2019-05-01		[JP]
M04D29	10	昭和の日	2007-01-01		[JP]
M05D03	10	憲法記念日	1948-07-20		[JP]
M05D04	10	みどりの日	2007-01-01		[JP]
M05D05	10	こどもの日	1948-07-20		[JP]
M07W3D01	10	海の日	2003-01-01		[JP]
M08D11	10	山の日	2016-01-01		[JP]
M09W3D01	10	敬老の日	2003-01-01		[JP]
M10W2D01	10	体育の日	2000-01-01		[JP]
M11D03	10	文化の日	1948-07-20		[JP]
M11D23	10	勤労感謝の日	1948-07-20		[JP]
M12D23	10	天皇誕生日	1989-02-17	2019-05-01	[JP]
M*W*D6	1	公休日			[*]
M*W*D7	1	公休日			[*]

祝日定義

祝日定義 [YnnnnMnnWsnDnn] の記述仕様(「勝手仕様の日付書式」)

• Ynnnn は年、Mnn は月、Wsn は週、Dnn は月の日または曜日番号を指定する

• Ynnnn と Wsn は省略可
• Ynnnn を省略した場合は通年となる
• Mnn は月の番号を指定する 値域 [1..12] or [01..12]
• M* という記述で 1月から12月までの意味となる
• Wsn の週の番号は年の暦週ではなく月の週(1日から7日は第1週)を指定する 値域 [-5..-1] or [1..5]
• Wsn の数値がマイナスの場合には月末から逆算する(月の最終金曜日は "W-1D5" )
• W* という記述で対象月のすべての週の意味となる
• Dn 部は Wsn が無い場合、日にち[1..31]、Wsn がある場合には曜日番号[1..7] となる
  曜日番号は (1:Mon/2:Tue/3:Wed/4:Thu/5:Fri/6:Sat/7:Sun)

表記例

'Y2018M1D2' => 2018年1月2日
'M10D4' => 毎年 10月4日 / 'M10W4D1' => 毎年 10月第4週月曜日
'M*W*D7' => 毎週日曜日 / 'M*D15' => 毎月15日

補足) 'M10D31'(10月31日)や、うるう年ではない年の'M02W5D1'(2月第5週月曜日)は無効 (NULL値と同義) となる。

日付種別

(※) タグ中に 最初に見つかった []（半角カギカッコ) で囲まれた文字でマルチカレンダーの分類用に使用されるタグ。
同一日に複数の休日がある場合のパーティションキーとして日付とプライマリ・タグを使用して日付種別の降順でリストを選択するために必要。
サンプルデータでは [JP][JP-nn] と [US]、[*] のタグで休日情報を区別している。

適用開始日、失効日

適用開始日、失効日：指定がない場合、それぞれ期限制限なしとして扱う。
サンプルデータでは定義が有効となる施行日、廃止になる施行日を設定（祝日データの正確性については必ず確認してください）

タグ

複数のカレンダー情報を区別や情報補足するための文字列。先頭のタグ（プライマリ・タグ）にだけプログラム内で意味がある。
サンプル内では日本[JP]、日本の地方自治体[JP-nn] とアメリカ [US] の祝日・休日をタグで分類している。
[*] タグは追加カスタマイズを想定した振替処理などが一切無いバニラ状態なもの。

[JP] は [ISO 3166-1] による日本の国名コード、[JP-nn] は [ISO 3166-2:JP] の県コード

休日一覧出力形式 (HOLIDAY_LIST 表)

• プライマリ・タグとはタグ(tag) 内で最初に [] で記述された文字列をあらわす。
  プライマリ・タグを使用して複数の休日一覧を区別する。

クエリー補助用ビュー (CALANDAR)

システム日付の当月から前後 12カ月分の日付一覧を出力する

CREATE VIEW CALENDAR(dd)
AS
  select add_months(trunc(sysdate, 'mm'), -12) + column_value
  from table (
    holiday.seqnum_gen(0, add_months(trunc(sysdate, 'mm'), 12)
    - add_months(trunc(sysdate, 'mm'), -12) - 1)
  );

• 休日・祝日一覧自体のビュー化について

日本の祝日一覧もアメリカの祝日一覧もそれぞれ単一の SQL で導出しているのでリテラルの直接埋め込みと関数にパッケージ名を補完すればビュー化は可能。

Oracle 12c で強化されたパターンマッチ検索（MATCH_RECOGNIZE）(※1) から 11g ベースに落とし込んでいるので 12c のスマートな SQL に簡単に書き換えできる。
Oracle 12c R2 時点では表関数を含んでいるのでマテリアライズド・ビュー化は止めたほうがよいです。(再帰的問い合わせ に変換しても良いことは起きません)

(※1) 参考 URL (Oracleホワイト・ペーパー pdf)

• Patterns everywhere – Find them fast! Oracle Database 12c のSQL パターン・マッチング

使用例

日本 [JP] と日本の地方自治体 [JP-nn] の祝日、休日、記念日等の名称一覧。
サンプルデータは [JP] と [JP-nn] のプライマリ・タグを変えて別のカレンダーとして登録しているため同一日付で複数レコードが発生する。(2020年の天皇の誕生日など)

select dd, date_type, date_name, primary_tag from holiday_list
where primary_tag like 'JP%'
and dd between date '2020-01-01' and date '2020-12-31' 
order by dd;
 
 
DD          DATE_TYPE DATE_NAME                  PRIMARY_TAG
---------- ---------- -------------------------- ------------
2020-01-01         10 元日                       JP
2020-01-02         -1 年始                       JP
2020-01-03         -1 年始                       JP
2020-01-13         10 成人の日                   JP
2020-01-25          0 プレミアムフライデー       JP
2020-02-07          0 ふるさとの日【福井】       JP-18
2020-02-11         10 建国記念の日               JP
2020-02-20          0 県政発足記念日【愛媛】     JP-38
2020-02-23         10 天皇誕生日                 JP
2020-02-23          0 富士山の日【静岡】         JP-22

カレンダービューと結合

select c.dd, h.date_name from CALENDAR c
left join HOLIDAY_LIST h on (h.dd = c.dd and h.primary_tag = 'JP')
where c.dd between date '2018-01-01' and date '2018-01-31'
order by c.dd;
 
DD         DATE_NAME
---------- --------------------------
2018-01-01 元日
2018-01-02 年始
2018-01-03 年始
2018-01-04
2018-01-05
2018-01-06
2018-01-07
2018-01-08 成人の日
2018-01-09
2018-01-10
2018-01-11
....

2018年 9月の営業日を数える。(土曜日曜を JP プライマリ・タグに休日として追加登録した場合)

select
  count(c.dd) cnt
from CALENDAR c
left join HOLIDAY_LIST h on (h.dd = c.dd and h.primary_tag = 'JP')
where nvl(h.date_type,0) = 0
and c.dd between DATE '2018-09-01' and DATE '2018-09-30'
order by c.dd;
 
       CNT
----------
        18

2018年の「国民の祝日」「振替休日」「国民の休日」の日数を数える。

select
  count(c.dd) cnt
from CALENDAR c
  left join HOLIDAY_LIST h on (h.dd = c.dd and h.primary_tag = 'JP')
where h.date_type > 0 -- データによっては >= 7
and c.dd between DATE '2018-01-01' and DATE '2018-12-31';
 
       CNT
----------
        20

複数の休日カレンダーをマージ 優先順位は [JP] [US] [*] の順

select to_char(dd, 'yyyy-mm-dd(dy)') dy, date_type, date_name, tag
from (
    select
      row_number() over (
        partition by dd
        order by decode(primary_tag,'JP',1,'US',2,'*',3,4),
          date_type desc nulls last
      ) rn,
      dd, date_type, date_name, tag
    from holiday_list 
    where primary_tag in ('*', 'JP', 'US')
    and dd between date '2017-01-01' and date '2017-12-31'
)
where rn = 1
order by dd;
 
DY                DATE_TYPE DATE_NAME                      TAG
---------------- ---------- ------------------------------ --------
2017-01-01(sun)          10 元日                           [JP]
2017-01-02(mon)           8 振替休日                       [JP]
2017-01-03(tue)          -1 年始                           [JP]
2017-01-07(sat)           1                                [*]
2017-01-08(sun)           1                                [*]
2017-01-09(mon)          10 成人の日                       [JP]
2017-01-14(sat)           1                                [*]
2017-01-15(sun)           1                                [*]
2017-01-16(mon)          10 Martin Luther King, Jr., Day   [US]
2017-01-21(sat)           1                                [*]
2017-01-22(sun)           1                                [*]

---

関連事項

• 祝日、休日カレンダーの実用的な使用例
• NLS_CALENDAR (Japanese Imperial) を令和に対応させる