Top > PLSQL プログラミング入門(2) > 各国語対応可能な祝日、休日一覧を作成する

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

祝日定義情報から任意の期間の祝日一覧(実表)を事前作成し、SQL で祝日・休日を判定や営業日の日数の計算を容易にできるようにする。

機能の概要と目標

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

実装方針と内容

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

必須環境は Oracle 11g 以降 (Oracle EE 11.2.0.1.0 では動作確認)

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

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

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

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

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

対象ファイル名漢字コード
パッケージpackage.sqlfileholiday.zipShift JIS
テーブルと初期データschema.sql
ビューview.sql
更新時コールバック登録opt_callback.sql

祝日定義表(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
M01D0110元日1948-07-20[JP]
M01W2D0110成人の日2000-01-01[JP]
M02D1110建国記念の日1966-06-25[JP]
M04D2910みどりの日1989-02-172007-01-01[JP]
M02D2310天皇誕生日2019-05-01[JP]
M04D2910昭和の日2007-01-01[JP]
M05D0310憲法記念日1948-07-20[JP]
M05D0410みどりの日2007-01-01[JP]
M05D0510こどもの日1948-07-20[JP]
M07W3D0110海の日2003-01-01[JP]
M08D1110山の日2016-01-01[JP]
M09W3D0110敬老の日2003-01-01[JP]
M10W2D0110体育の日2000-01-01[JP]
M11D0310文化の日1948-07-20[JP]
M11D2310勤労感謝の日1948-07-20[JP]
M12D2310天皇誕生日1989-02-172019-05-01[JP]
M*W*D61公休日[*]
M*W*D71公休日[*]

祝日定義

祝日定義 [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値と同義) となる。

日付種別

日付種別内容
tag
[JP][JP-nn][US][*]
システム予約10-国民の祝日(振替や祝日の並びによって別の休日が発生する休日)
9--特別法の休日、祝日法より以前の祝祭日
8-振替休日(休日定義データとして使用しないことを推奨)
7--国民の休日(2つの祝日に挟まれた平日、使用しないことを推奨)
0休日でない日(日曜含む)
NULL未設定と等価
変更可-1--初期サンプルデータでは独自の休日として使用
-1
自由上記以外同じプライマリータグ(※)、かつ、同じ日に休日が重なった場合、日付種別が最大のものが代表値となる
11 以上にしたとき「国民の祝日」の名称より選択順位があがる

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

適用開始日、失効日

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

タグ

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

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

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

対象日付
dd
日付種別
date_type
祝日名称
date_name
タグ
tag
プライマリータグ
primary_tag
2017-01-0110New Year's Day[US]US
2017-01-0110元日[JP]JP
2017-01-028振替休日[JP]JP
2017-01-028Substitute holiday[US]US
2017-01-03-1年始[JP]JP
2017-01-0910成人の日[JP]JP
2017-01-1610Martin Luther King, Jr., Day[US]US
2017-02-070ふるさとの日【福井】[JP-18]JP-18
2017-02-1110建国記念の日[JP]JP
2017-02-200県政発足記念日【愛媛】[JP-38]JP-38
2017-02-2010Presidents Day[US]US
2017-02-230富士山の日【静岡】[JP-22]JP-22
2017-02-250プレミアムフライデー[JP]JP
  • プライマリータグとはタグ(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 R2 時点では表関数を含んでいるのでマテリアライズド・ビュー化は止めたほうがよいです。(再帰的問い合わせ に変換しても良いことは起きません)

使用例

日本 [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                                [*]
 


各国語対応可能な祝日、休日一覧を作成するの関連トピックス

SHIFT the Oracle 最近のトピックス

日本オラクル
■ 日本オラクル 株式会社
■ オラクルマスター資格 (オラクルマスターとは
■ Oracle のライセンスがわからない…
Oracle Direct (ネットで聞いても最後はここで要確認)