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

シェアする

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

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

前提

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

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句の中で一時的に作り出しています。

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

『平日や営業日を取得するSQL(Postgress/Oracle)』へのコメント

  1. 名前: 投稿日:2020/10/21(水) 11:40:11 ID:d3916b501 返信

    ORACLEのLEVELの使い方が間違えてORA-01788エラーになります。

    • 名前:nisioka 投稿日:2020/10/21(水) 23:59:37 ID:95701fff6 返信

      ご指摘ありがとうございます。
      仰るとおりORACLE版に構文不備がありましたので、修正しています。

%d人のブロガーが「いいね」をつけました。