履歴管理されているマスタの空白期間を埋めるSQL

開発

履歴管理されているマスタ（キー項目に対し適用開始日、適用終了日を持ち行が複数発生するマスタ）を使うようなシステムで帳票出力時に名称が出てこないといった問題がしばしば起こる。原理的にはトランザクション上にある日付データで参照すれば、入力時点では存在していたはずなのだから、そのようなことは起こりえないはずだが、次のような場合を考えればそうとも言えない。

  • 該当のマスタやトランザクションの発生元が異なるシステムであり、整合性を前提とできない
  • 移行の時点で整合性が取れていないが、システム側の都合で日付を修正できない
  • 運用の関係上、データの適用期間を手作業でメンテナンスが発生する

たしかに入力チェックには適用期間付きのマスタは有効である（特に異なる会計期間での入力が重複するような場合など）が、表示するという用途ではデメリットも大きい。

例えば、月次の残高データと履歴管理されているマスタを結合することを考える。さて、１月のデータはどのマスタと結合すればよいのだろうか。月初のマスタと結合すると月中に生まれたマスタとは結合できない。月末のマスタと結合しても、月中に廃止となったマスタとはやはり結合できない。

このような場合、単に名称を出したいだけであれば、履歴の空白期間を前後のデータで埋めたマスタを作り出せれば解決できる。

というわけで本日の話題は、「履歴管理されているマスタの空白期間を埋めるSQL」の作り方について説明しよう（前置きが長すぎ）。

空白期間は本来存在しないわけであるから、何らかの基準を設ける必要がある。

  • 原則として、適用終了日を伸ばして次の適用開始日にくっつける
  • 最初の適用開始日より前のレコードは存在しないので、最初の適用開始日は十分に昔、例えば 1900年1月1日とする
  • 最後の適用終了日は十分に先の日付、例えば 3999年12月31日にする

さてこのような結果を得るにはどのようなSQLを組めばよいだろうか。実は分析関数を使うと比較的簡単に書ける。

SELECT
    社員コード,
    (CASE WHEN (ROW_NUMBER() OVER (
        PARTITION BY
           社員コード
        ORDER BY
           適用開始日
    ) = 1) THEN '19000101' ELSE 適用開始日 END) AS 適用開始日,
    NVL(TO_CHAR(TO_DATE(LEAD(適用開始日) OVER (
        PARTITION BY
            社員コード
        ORDER BY
            適用開始日
    ), 'YYYYMMDD') -1, 'YYYYMMDD'), '39991231') AS 適用終了日,
    社員名称
FROM
    社員マスタ
ORDER BY
    社員コード,
    適用開始日

ね？　簡単でしょ？（ボブの絵画教室風）

[03/15追記] 誤記載を修正しました。