時系列データと履歴テーブル

データが時間とともにどのように変化したかを追跡することは、多くのビジネスシステムにおいて重要な要件です。このニーズは、組織がレコードの現在の状態だけでなく、過去の状態も知りたい場合によく発生します。例えば、従業員の給与がどのように変化したか、あるいは過去の特定時点で顧客の住所が何であったかなどです。これは時系列データ管理と呼ばれます。変更履歴を保持することで、傾向分析、監査、法令遵守などの質問に答えることができます。これを実現するために、データベースでは履歴テーブルや時系列テーブルを使用し、重要なデータのすべての変更と、それぞれのバージョンが有効だった期間の情報を記録します。

-- Schema for employee_salaries history table
CREATE TABLE employee_salaries (
    employee_salary_id SERIAL PRIMARY KEY,
    employee_id INTEGER NOT NULL REFERENCES employees(employee_id),
    salary NUMERIC(12,2) NOT NULL,
    effective_from DATE NOT NULL,
    effective_to DATE
);

employee_salariesのような時系列テーブルを使うことで、各従業員の現在の給与だけでなく、過去のすべての給与とその有効期間も保存できます。effective_fromおよびeffective_to列は、特定の給与が有効だった期間を定義します。現在の給与を取得するには、effective_toがnullのレコードを探します。履歴の変化を見るには、従業員のすべてのレコードを検索します。この方法により、過去の任意の時点で従業員の給与を調べたり、組織全体で給与がどのように変化したかを分析したりするなど、複雑なビジネス上の質問に答えることができます。

-- Query to get the entire salary history for employee_id = 2
SELECT salary, effective_from, effective_to
FROM employee_salaries
WHERE employee_id = 2
ORDER BY effective_from DESC;