履歴を保持する理由
過去のデータを使った監査、取り消し、分析を学びます
「履歴を保持する理由」はCoddyKit上の無料SQL Academyレッスンです。 これはレッスン1/4です。 下記で完全なレッスンを無料で読むことができます。その後、ブラウザ内の組み込みコードエディタと24時間対応のAIチューターでハンズオン演習できます。 これはSQL Academy学習パスの一部であり、ウェブとCoddyKitアプリ全体で進捗が同期されます。 SQL Academyコースには全4レッスンが含まれています。
上書きの問題点
UPDATEまたはDELETEを実行するたびに、古いデータは永久に失われます。一見効率的に思えますが、先週の火曜日の価格はいくらだったかや、このレコードをいつ誰が変更したかといった質問に答えられなくなるという大きな問題が生じます。
履歴を保持するには、最新の行だけでなく、行のすべてのバージョンを保存します。このレッスンでは、なぜそれが重要なのか、そしてSQLでどのように実現するのかを学びます。
履歴を保持する3つの理由
データベースで履歴データを保持する代表的な理由は3つあります。
1. 監査 — 変更が行われたこと、変更者、変更日時を証明できます。
2. 取り消し — データベース全体を復元せずに、誤った変更をロールバックできます。
3. 分析 — 過去についての質問に答え、傾向を見つけ、期間を比較できます。
適切に設計された履歴管理戦略なら、ストレージを過度に重複させることなく、これら3つの要件をすべて満たせます。
シンプルな監査テーブル
最もシンプルな方法は、すべての変更を記録する独立した監査テーブルを作成することです。各行には、変更前の値、変更後の値、変更者、変更日時を記録します。
以下はproductsテーブル用の監査テーブルです。operation列にはINSERT、UPDATE、DELETEのいずれかを格納します。
CREATE TABLE products_audit (
audit_id SERIAL PRIMARY KEY,
product_id INT NOT NULL,
operation VARCHAR(6) NOT NULL, -- INSERT / UPDATE / DELETE
old_price NUMERIC(10,2),
new_price NUMERIC(10,2),
changed_by TEXT NOT NULL,
changed_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);監査テーブルへのデータ投入
監査テーブルには手動で書き込むこともできますが、最も信頼性の高い方法は、データが変更されるたびに自動的に実行されるデータベーストリガーを使うことです。これにより、アプリケーションコードがログを迂回することを防げます。
ここでは、トリガーを扱う前に構造を示すため、監査行を1件直接挿入します。
INSERT INTO products_audit (product_id, operation, old_price, new_price, changed_by)
VALUES (42, 'UPDATE', 9.99, 12.49, 'alice');
SELECT * FROM products_audit ORDER BY changed_at DESC LIMIT 5;監査証跡の読み取り
監査テーブルに行が蓄積されると、それらをクエリして監査に関する質問に答えられます。以下のクエリは、1つの商品の価格履歴全体を新しい順に表示します。
SELECT
changed_at,
changed_by,
operation,
old_price,
new_price
FROM products_audit
WHERE product_id = 42
ORDER BY changed_at DESC;有効日：有効時刻の履歴
監査テーブルには変更を行った時点（トランザクション時刻）が記録されます。一方、現実世界で何がいつ有効だったかを追跡する必要がある場合もあります。これは有効時刻と呼ばれます。
メインテーブルにvalid_from列とvalid_to列を追加すると、有効時刻の履歴を作成できます。これは、緩やかに変化するディメンション（SCD Type 2）と呼ばれることもあります。
CREATE TABLE employee_history (
id SERIAL PRIMARY KEY,
employee_id INT NOT NULL,
department TEXT NOT NULL,
salary NUMERIC(10,2) NOT NULL,
valid_from DATE NOT NULL,
valid_to DATE -- NULL means current record
);
-- Current record for employee 7
INSERT INTO employee_history (employee_id, department, salary, valid_from)
VALUES (7, 'Engineering', 85000, '2023-01-01');緩やかに変化するレコードの更新
従業員が部署を異動した場合、その行をUPDATEすることはありません。代わりに、valid_toを設定して古い行を閉じ、新しい終了日未設定の行を挿入します。これにより、完全な履歴を保持できます。
-- Step 1: close the current record
UPDATE employee_history
SET valid_to = '2024-06-01'
WHERE employee_id = 7 AND valid_to IS NULL;
-- Step 2: insert the new record
INSERT INTO employee_history (employee_id, department, salary, valid_from)
VALUES (7, 'Product', 90000, '2024-06-01');
-- Verify history
SELECT department, salary, valid_from, valid_to
FROM employee_history
WHERE employee_id = 7
ORDER BY valid_from;ある時点のデータを検索する
有効時刻の列があれば、特定の日付に何が有効だったかを尋ねられます。単純なUPDATEモデルでは不可能なクエリです。
WHERE句で、対象の日付が行の有効期間内にあるかどうかを確認します。
-- What department and salary did employee 7 have on 2023-09-15?
SELECT department, salary, valid_from, valid_to
FROM employee_history
WHERE employee_id = 7
AND valid_from <= '2023-09-15'
AND (valid_to > '2023-09-15' OR valid_to IS NULL);システムバージョン管理テンポラルテーブル
最新のSQLデータベース（PostgreSQL 16以降、SQL Server、MySQL 8）は、システムバージョン管理テンポラルテーブルをサポートしています。データベースが非表示の列にトランザクション時刻を自動的に記録し、専用の構文で過去の状態を検索できます。
SQL Serverの例です。概念は各エンジンで共通しています。
-- SQL Server / MariaDB style (illustrative)
CREATE TABLE orders (
order_id INT PRIMARY KEY,
status VARCHAR(20),
total NUMERIC(10,2),
SysStartTime DATETIME2 GENERATED ALWAYS AS ROW START,
SysEndTime DATETIME2 GENERATED ALWAYS AS ROW END,
PERIOD FOR SYSTEM_TIME (SysStartTime, SysEndTime)
) WITH (SYSTEM_VERSIONING = ON);
-- Query historical state
SELECT * FROM orders FOR SYSTEM_TIME AS OF '2024-01-15 12:00:00'
WHERE order_id = 100;履歴を元にした取り消し
履歴テーブルは読み取り専用ではありません。ミスを取り消すためにも利用できます。バッチジョブによって500件の商品価格レコードが破損した場合でも、バックアップに触れずに監査テーブルから復元できます。
-- Undo all price changes made by the bad batch job at a specific time
UPDATE products p
SET price = a.old_price
FROM products_audit a
WHERE p.id = a.product_id
AND a.operation = 'UPDATE'
AND a.changed_by = 'batch_job'
AND a.changed_at BETWEEN '2024-03-10 02:00:00' AND '2024-03-10 02:05:00';
-- Confirm affected rows
SELECT COUNT(*) AS rows_restored FROM products_audit
WHERE changed_by = 'batch_job'
AND changed_at BETWEEN '2024-03-10 02:00:00' AND '2024-03-10 02:05:00';時間経過に基づく分析
履歴データによって、時系列分析が可能になります。指標が時間とともにどのように変化したかを追跡したり、前月比の数値を比較したり、異常を検出したりできます。これらはすべて、別のデータウェアハウスを用意せずに実行できます。
このクエリは、監査テーブルを使用して、暦月ごとの商品の平均価格を示します。
SELECT
DATE_TRUNC('month', changed_at) AS month,
ROUND(AVG(new_price), 2) AS avg_price
FROM products_audit
WHERE product_id = 42
AND operation IN ('INSERT', 'UPDATE')
GROUP BY 1
ORDER BY 1;理解度チェック
SQLでの履歴データの保存について、理解度を確認しましょう。
復習：履歴を保持する理由
このレッスンでは、データを上書きすることが危険な理由と、監査、取り消し、分析のために履歴を保持するSQLパターンについて学びました。
重要なポイント：
- 監査テーブルには、誰がいつ実行したかを含め、すべてのINSERT、UPDATE、DELETEが記録されます。
- 有効時間（SCD Type 2）の行では、
valid_from/valid_to列を使用して、現実世界での時系列を記録します。 - 時点クエリでは、これらの日付列で絞り込むことで、過去に関する質問に答えます。
- システムバージョン管理テンポラルテーブルでは、データベースレベルでトランザクション時間の追跡を自動化します。
- 履歴データにより、バックアップを必要とせずに限定的な取り消しや高度な時系列分析が可能になります。
過去を保持することは余計な負担ではありません。信頼性が高く、監査可能なシステムの基盤です。
よくある質問
「履歴を保持する理由」レッスンは無料ですか？
はい。「履歴を保持する理由」の完全なテキストはこのウェブで無料で読めます。インタラクティブに演習し（組み込みコードエディタと24時間対応のAIチューター）、SQL Academyコースの残りをアンロックするには、CoddyKit PROにアップグレードしてください。 SQL Academyコースには全4レッスンが含まれています。
「履歴を保持する理由」で何を学びますか？
過去のデータを使った監査、取り消し、分析を学びます ブラウザで直接実行するハンズオンコードでSQL Academyを演習し、24時間対応のAIチューターがレッスンを進める中での質問に答えます。
SQL Academyを始めるのに経験は必要ですか？
事前経験は必要ありません。CoddyKitのSQL Academyは初級者から上級者向けに構成されているため、ここから始めるか最初から始めて、自分のペースで進むことができます。 これはレッスン1/4です。
「履歴を保持する理由」レッスンにはどのくらい時間がかかりますか？
ほとんどのCoddyKitレッスンは約5～10分かかります。各レッスンはコンパクトでインタラクティブなので、着実に進歩し、ウェブとアプリ全体で正確に前回の場所から再開できます。
このSQL Academyレッスンでコードを書いて実行できますか？
はい。すべてのSQL Academyレッスンに組み込みコードエディタが含まれているため、ブラウザでリアルコードを書いて実行し、即座のAIフィードバックを取得できます。ローカル設定は不要です。