平日や営業日を取得するSQL(Postgress/Oracle)

平日や営業日を取得するSQL(Postgress/Oracle)
カテゴリ
技術
タグ
oracle
Postgress
SQL

業務を行う上では、休日を除いた日付を取得したいことがあるかと思います。
これが地味に難しいですが、そこそこキレイに取得できたので紹介します。

当然ですが、祝日を除いた営業日を取得したい場合は、「祝日マスタ」のようなテーブルで情報を保持している必要があります。

前提

ここでの祝日マスタは以下の形式とします。

IDHOLIDAY
12019-01-14
22019-02-11
32019-03-21
42019-04-29
52019-04-30
62019-05-01
72019-05-02
82019-05-03
92019-05-06

実装SQL

日付取得に関する要件は色々とあるかと思いますが、ここでは「前営業日を取得する」ことを提示します。翌営業日などほかの要件もこれの応用で取得できると思います。

ちなみにこれらSQLでの肝は日付候補を複数レコードとして取得することで、Postgressでは generate_series関数というのがとても便利で、Oracleでは同じようなことをlevel疑似列で行います。

Postgress版

使い方としては、WITH句で前営業日を取得するようにしているので、以降はその一時テーブルから簡単なSELECT文で取得できます。

コメントにも記載していますが、解説もします。まず元となるレコード群とするためにgenerate_series関数で候補数分取得します(ここでは15レコード分)。それらを本日日付(current_date)から引くことで前日から16日前までの候補日付となります。

そこから絞り混みを行い、(曜日を取得する関数)で土日を除き、祝日マスタに該当しないことをもって祝日を除きます。そうして残ったものが営業日のみの日付候補となるので、max関数を用いて前営業日を取得しています。

これらから少し修正すれば、翌営業日だったり、土日以外が休みなどにも変更できます。

Oracle版

Oracle版も基本は同じです。generate_seriesがlevel疑似列に変わっています。

終わりに

そもそも理想を言えば、必要な年月分(10年分とか)のカレンダーを保持して、その日付が休みかどうかのフラグを持っているのが楽です。それを今回はWITH句の中で一時的に作り出しています。

これから機能設計する場合はそうしたカレンダーベースの営業日の保持の仕方をすることをお勧めします。


関連記事

  1. 平日や営業日を取得するSQL(Postgress/Oracle)

    業務を行う上では、休日を除いた日付を取得したいことがあるかと思います。 これが地味に難しいですが、そこそこキレイに取得できたので紹介します。 当然ですが、祝日を除いた営業日を取得したい場合は、「祝日マスタ」のようなテーブ […]

  2. テーブル生成時には列長セマンティクスを明示すべき

    問題点 これはテーブル生成時の問題で、Oracle データベースのパラメータである列長セマンティクス(NLS_LENGTH_SEMANTICS)を正しく設定しておかないと、意図した文字数のデータを挿入することができなくな […]

  3. Oracleの権限テーブル(DBA_TAB_PRIVS)のバージョン差異

    oracle DBでは、権限テーブルをDBA_TAB_PRIVS(USER_TAB_PRIVSも同様)で確認できるが、バージョン11g→12cで列追加の変更があった。しかし、公式でそのことを明記しているのが見つからなかっ […]

  4. OracleDBの無効オブジェクトを調査する方法

    背景 DBインスタンスのダンプを取得して移行を行ったのですが、なぜか無効なオブジェクトがいくつか発生してDBが上手く動かなくなってしまったためです。 原因究明はできていませんが、もし同様にDBのオブジェクトが無効になって […]

  5. Oracle DBのセッションを増やすor強制終了する【ORA-12516対応】

    ここでは、Oracle DBのセッションを強制終了する手順を説明します。 強制終了しなければならない状況は色々とあるとは思いますが、以下のエラーが発生した場合なんかもそうかと思います。 ORA-12516: TNS: リ […]