履歴管理されているマスタを圧縮するSQL

[2012/8/23] 内容に誤りがあったため、一旦公開を停止していましたが、SQLが修正できたの再度公開しました。

昨今は、どこの会社でもマスタを履歴管理するのが流行りらしく、適用期間（適用開始日と終了日）を持つことが多い。以前からこのようなマスタを扱う案件にばかりに関わっているのだが、扱いがたいそう難しい。

たしかに「現在時点」でマスタを探す分には本日が適用期間間に入っているものを探せばよいが、過去のデータだと困ってしまう。月次残高データの名称は、月初時点のマスタと紐付けるべきだろうか、それとも月末時点のマスタと紐付けるべきだろうか。運悪く月中に登録と削除が行われた場合、月初にも月末にもそのマスタは見つからない。さらに履歴管理されているマスタ同士を結合する場合、そのマスタの有効期間は双方のマスタの適用期間のANDをとる必要がある。

閑話休題。履歴管理されているマスタのもう一つの問題点は、修正を繰り返すとまったく同じ内容にも関わらず適用期間が異なるものが沢山生まれてしまうという点がある。マスタメンテナンスを画面から行うのであればまだしも、他システムから複数の履歴管理されたマスタ取り込みを結合してマスタを生成するような場合などは特にそうなりがちとなる。

そんな場合に必要となるのが、履歴管理されているマスタを圧縮する方法だ。単純に GROUP BY して MIN(適用開始日)、MAX(適用終了日)としてしまうと、データ内容が A ⇒ B ⇒ A と変更された場合、B の情報が消えてしまう。あくまで連続した適用期間のみ集約する必要がある。答えを言ってしまうと、このサイトにあるように旅人算を応用してグループ化する。

SELECT
    SM.組織コード,
    MIN(SM.適用開始日) AS 適用開始日,
    MAX(SM.適用終了日) AS 適用終了日,
    SM.組織名称
FROM
    (
        SELECT
            SM.組織コード,
            SM.適用開始日,
            SM.適用終了日,
            SM.組織名称,
            SM.適用終了日 - SUM(SM.適用終了日-SM.適用開始日+1) OVER (
                PARTITION BY
                    SM.組織コード,
                    SM.組織名称
                ORDER BY
                    SM.適用開始日
            ) AS 距離
        FROM
            組織マスタ SM
    ) SM
GROUP BY
    SM.組織コード,
    SM.組織名称,
    SM.距離