【SQL】BiTemporalDataModelで、1つ前の有効な履歴をJOINする方法に関する考察

TL;DR

BiTemporalデータのエッジケースまで考慮すると、以下のやり方で取得するのが一番厳密かつ簡単だと思われる。

1. 以下の条件を全て満たすbeforeをcurrentへJOINする
   • IDが一致
   • before.transact_toが無限遠
   • current.valid_fromとbefore.valid_toが一致
   • 以下いずれかを満たす
     • before.record_serial < current.record_serial
     • current.record_serial < before.record_serialかつbefore.valid_from < current.valid_from
2. current.valid_fromが小さい -> current.record_serialが小さい -> before.record_serialが大きい順でソートし、current.valid_fromとcurrent.record_serialで重複削除

SQLに落とし込んだイメージ

上記をまとめると以下のようなイメージとなる。

SELECT DISTINCT
-- current.valid_fromとcurrent.record_serialで重複削除
ON (current.valid_from, current.record_serial)

-- 実際にはここへ取得対象列関係の記述を行うが、記事的には不要なため省略

FROM current
LEFT JOIN before
  -- IDが一致
  ON current.ID = before.ID
  -- before.transact_toが無限遠
  AND before.transact_to = $無限遠
  -- current.valid_fromとbefore.valid_toが一致
  AND current.valid_from = before.valid.to
  AND (
    before.record_serial < current.record_serial
    OR (
      current.record_serial < before.record_serial
      AND before.valid_from < current.valid_from
    )
  )
WHERE current.transact_to = $無限遠
-- current.valid_fromが小さい -> current.record_serialが小さい -> before.record_serialが大きい順でソート
ORDER BY current.valid_from, current.record_serial, before.record_serial DESC;

前置き

「(作成) -> A -> B」というような履歴に対して、以下の表のような履歴を表示するケースを考えます。

i	current	before
1	A	(null)
2	B	A

エッジケースまで厳密に考えると、これが意外と難しく奥深かったので、考察した内容をまとめます。

状況に関する補足

単に1テーブル分の履歴を出したいケースでは、beforeイベントをJOINするより、一連の履歴のソート結果を手動で加工する方法の方がシンプルで良いと思います。
一方、複数テーブルの履歴をまとめて取得したいといった複雑なケースでは、どうしても今回紹介するような内容が必要となります。

この記事内で使う列名

この記事では、BiTemporalテーブルの制御列を以下のように置きます。

列名	概要	備考
record_serial	テーブルのPKとなるシリアル列1	・現実の時間軸で後から登録されたイベントほど大きい ・transact_from が一致する場合、valid_from, valid_to が大きいほど大きい
ID	一連の履歴を区別するためのID	・今回は1つのIDに紐付く履歴に絞って説明するため、以降は省略
valid_from	論理的時間軸におけるイベントの有効期間開始
valid_to	論理的時間軸におけるイベントの有効期間終了
transact_from	現実の時間軸における有効期間開始	今回は判定に用いないため、以降は省略
transact_to	現実の時間軸における有効期間終了	今回は無限遠であるか否かのみ判定するため、以降は省略

BiTemporalデータモデルにおける履歴パターンと、対応する必要な条件の整理

冒頭で紹介した条件はかなり複雑な内容でしたが、何故それらが必要なのか1つ1つ分解して説明します。

なお、例ではtransact_toが無限遠でないレコードを省略しています。
また、本来であればrecord_serialもそれを含めた番号となりますが、簡単のため、ここでは単に登録の時系列順を表現する値としています。

最も基本的なケース

まず、最も基本的な履歴は以下のようになるでしょう。
この場合はcurrent.valid_from = before.valid_toという条件だけで問題無くJOINできます。

イベント	valid_from	valid_to
A	2025/01/01	2025/01/02
B	2025/01/02	(無限遠)

valid_from = valid_toとなる履歴が存在するケース

BiTemporalデータモデルでは、以下のように、valid_fromとvalid_toの一致するレコードを設定できます。
このため、以下のような複雑な履歴に関しても考慮しなければなりません。

イベント	record_serial	valid_from	valid_to
A	1	(過去)	2025/01/01
B	2	2025/01/01	2025/01/01
C	3	2025/01/01	2025/01/01
D	4	2025/01/01	(無限遠)

このようなケースでcurrent.valid_from = before.valid_toだけを条件にINNER JOINした場合、以下のように意図せぬJOIN結果が生じます。

current	before	期待通りか	問題のパターン
B	A	o
B	B	x	自分自身がJOINされる
B	C	x	自分より未来がJOINされる
C	A	x	直前より過去がJOINされる
C	B	o
C	C	x	自分自身がJOINされる
D	A	x	直前より過去がJOINされる
D	B	x	直前より過去がJOINされる
D	C	o

このようなケースに関しては、まずbefore.record_serial < current.record_serialという条件を付けることで、以下のように問題のあるレコードを減らせます。

current	before	期待通りか	問題のパターン
B	A	o
C	A	x	直前より過去がJOINされる
C	B	o
D	A	x	直前より過去がJOINされる
D	B	x	直前より過去がJOINされる
D	C	o

残りの「直前より過去がJOINされてしまう」パターンについては、current.record_serialでの重複削除が必要です。
ここはサブクエリの取得結果をJOINする形も考えられますが、重複削除に持ち込む方がシンプルかなと考えています

重複削除は、current.record_serialが同じ中でもっともbefore.record_serialが大きい、つまり直前のイベントを選択することになります。
最終的な取得結果がvalid_from順であることも考慮すると、「current.valid_fromが小さい -> current.record_serialが小さい -> before.record_serialが大きい順でソートし、各ソートキーの中で一番上をそれぞれ選択」という形になるでしょう。
PostgreSQLを例にすると、DISTINCT ONを利用する想定です。

valid_fromが過去のレコードが後から追加されたケース

最後に、以下のような形で、Aが後から追加されたケースについて考えます。

イベント	record_serial	valid_from	valid_to
B	1	2025/01/01	2025/01/01
C	2	2025/01/01	(無限遠)
A	3	(過去)	2025/01/01

この場合、「current.valid_from = before.valid_toかつbefore.record_serial < current.record_serial」だけを条件にJOINしてしまうと、B.record_serialがA.record_serialより小さいため、期待したレコードが取得できなくなってしまいます。
このため、以下のように、すり抜けをケアする条件を加える必要が有ります。

• current.valid_fromとbefore.valid_toが一致
• 以下いずれかを満たす
  • before.record_serial < current.record_serial
  • current.record_serial < before.record_serialかつbefore.valid_from < current.valid_from

1. 「現実の時間軸で後に登録されたイベントほど大きい」及び「transact_fromが一致する場合、valid_from, valid_toが大きいほど大きい」というのは、実装方法によっては成り立たないかもしれません。ただ、自然に実装すればそうなるはずなので、もしそうなっていないなら実装を見直すべきだと考えています。 ↩