0Pricing
SQL Academy · Урок

Зачем хранить историю

Аудит, отмена действий и аналитика по прошлым данным

«Зачем хранить историю» — бесплатный урок SQL Academy на CoddyKit. Это урок 1 из 4. Ты можешь прочитать весь урок бесплатно ниже — а потом практиковать его прямо в браузере с встроенным редактором кода и ИИ-репетитором 24/7. Это часть пути обучения SQL Academy, и твой прогресс синхронизируется между веб-версией и приложением CoddyKit. Курс SQL Academy содержит 4 уроков всего.

Проблема перезаписи

Каждый раз, когда вы выполняете UPDATE или DELETE, старые данные исчезают навсегда. Это кажется эффективным, но создаёт реальные проблемы: вы не можете ответить на вопросы вроде какова была цена в прошлый вторник? или кто и когда изменил эту запись?

Сохранение истории означает хранение каждой версии строки, а не только последней. В этом уроке рассматривается важность этого подхода и то, как SQL помогает его реализовать.

Три причины сохранять историю

Есть три классические причины сохранять исторические данные в базе данных:

1. Аудит — доказать, что изменение произошло, кто его внёс и когда.
2. Откат — отменить ошибку без восстановления всей базы данных.
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()
);

Заполнение таблицы аудита

Можно записывать данные в таблицу аудита вручную, но наиболее надёжный подход — триггер базы данных, который автоматически срабатывает при каждом изменении данных. Так ни один код приложения не сможет обойти журнал.

Здесь мы напрямую добавляем одну строку аудита, чтобы показать её структуру перед рассмотрением триггеров.

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;

Чтение журнала аудита

После накопления строк в таблице аудита их можно запрашивать, чтобы отвечать на вопросы аудита. Запрос ниже показывает полную историю цен одного продукта, начиная с самой последней.

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 типа 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/7) и разблокировать остальной курс SQL Academy, подпишись на CoddyKit PRO. Курс SQL Academy содержит 4 уроков всего.

Чему я научусь в уроке «Зачем хранить историю»?

Аудит, отмена действий и аналитика по прошлым данным Ты практикуешь SQL Academy с помощью реального кода, который запускаешь прямо в браузере, и ИИ-репетитор 24/7 отвечает на твои вопросы во время урока.

Нужен ли мне опыт, чтобы начать SQL Academy?

Предыдущий опыт не требуется. SQL Academy на CoddyKit структурирован для всех уровней — от новичков до продвинутых, поэтому ты можешь начать отсюда или с самого начала и учиться в своем темпе. Это урок 1 из 4.

Сколько времени занимает урок «Зачем хранить историю»?

Большинство уроков CoddyKit занимают около 5–10 минут. Каждый из них компактный и интерактивный, поэтому ты постоянно делаешь прогресс и продолжаешь с того же места в веб-версии и приложении.

Можно ли писать и запускать код в этом уроке SQL Academy?

Да. Каждый урок SQL Academy включает встроенный редактор кода, поэтому ты пишешь и запускаешь реальный код прямо в браузере и получаешь моментальную обратную связь от AI — локальная установка не требуется.

Все уроки этого курса

  1. Зачем хранить историю
  2. Таблицы событий только для добавления
  3. Временные строки и строки с версиями
  4. Восстановление состояния из событий
← Назад к SQL Academy