0Pricing
SQL Academy · Урок

Временные строки и строки с версиями

Запросы по времени действия и по состоянию на дату

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

Что такое временные таблицы

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

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

Время действия и время транзакции

Время действия показывает, когда факт верен в реальном мире — например, зарплата сотрудника с 2020-01-01 по 2022-06-30. Время транзакции — это момент, когда строка была добавлена в базу данных или перестала действовать. Вместе эти понятия отвечают на два вопроса: Что было верно? и Когда мы об этом узнали?

Большинство практических задач начинается с отслеживания времени действия, которое можно реализовать вручную с помощью столбцов valid_from и valid_to.

Создание таблицы со временем действия

Самый простой способ хранить строки с версиями — добавить столбцы с отметками времени valid_from и valid_to. Значение NULL в valid_to (или специальная дата далёкого будущего, например 9999-12-31) означает, что строка в настоящее время активна.

CREATE TABLE employee_salary (
  id          SERIAL PRIMARY KEY,
  employee_id INT NOT NULL,
  salary      NUMERIC(12, 2) NOT NULL,
  valid_from  DATE NOT NULL,
  valid_to    DATE
);

INSERT INTO employee_salary (employee_id, salary, valid_from, valid_to)
VALUES
  (1, 50000, '2020-01-01', '2022-06-30'),
  (1, 60000, '2022-07-01', NULL);

Запрос текущей версии

Чтобы найти текущую активную строку для каждого сотрудника, отберите строки, где valid_to IS NULL (период не имеет конечной даты), или где сегодняшняя дата попадает в период действия. Специальное значение вроде '9999-12-31' упрощает сравнение диапазонов.

SELECT employee_id, salary
FROM employee_salary
WHERE valid_to IS NULL
ORDER BY employee_id;

Запросы по состоянию на момент времени

Запрос по состоянию на момент времени отвечает на вопрос: Какими были данные в определённый момент времени? Вы отбираете строки, в которых заданная отметка времени попадает в период действия. Это одна из самых мощных возможностей временных таблиц.

-- What was employee 1's salary on 2021-03-15?
SELECT employee_id, salary, valid_from, valid_to
FROM employee_salary
WHERE employee_id = 1
  AND valid_from <= '2021-03-15'
  AND (valid_to IS NULL OR valid_to > '2021-03-15');

Обновление строки с версией

Когда факт меняется, не следует изменять существующую строку на месте с помощью UPDATE. Вместо этого Вы закрываете текущую строку, устанавливая её valid_to, и добавляете новую строку с новым значением с помощью INSERT. Так сохраняется полная история.

-- Employee 1 gets a raise effective 2023-01-01
BEGIN;

-- Close the current open row
UPDATE employee_salary
SET valid_to = '2022-12-31'
WHERE employee_id = 1
  AND valid_to IS NULL;

-- Insert the new version
INSERT INTO employee_salary (employee_id, salary, valid_from, valid_to)
VALUES (1, 72000, '2023-01-01', NULL);

COMMIT;

Использование daterange для периодов действия

Тип daterange в PostgreSQL элегантно представляет период действия в одном столбце. Вы можете использовать оператор @> (содержит), чтобы проверить, попадает ли дата в диапазон, и добавить исключающее ограничение, запрещающее пересечение периодов для одной и той же сущности.

CREATE TABLE employee_salary_v2 (
  id          SERIAL PRIMARY KEY,
  employee_id INT NOT NULL,
  salary      NUMERIC(12, 2) NOT NULL,
  valid_period DATERANGE NOT NULL,
  EXCLUDE USING GIST (employee_id WITH =, valid_period WITH &&)
);

INSERT INTO employee_salary_v2 (employee_id, salary, valid_period)
VALUES
  (1, 50000, '[2020-01-01, 2022-07-01)'),
  (1, 60000, '[2022-07-01, infinity)');

Запрос по состоянию на момент времени с daterange

При использовании подхода с daterange запрос по состоянию на момент времени становится очень понятным. Оператор @> проверяет, что заданная дата входит в диапазон, автоматически обрабатывая его нижнюю и верхнюю границы.

-- What was employee 1's salary on 2021-03-15?
SELECT employee_id, salary, valid_period
FROM employee_salary_v2
WHERE employee_id = 1
  AND valid_period @> '2021-03-15'::date;

Таблицы с системным версионированием (стандарт SQL)

Стандарт SQL:2011 ввёл временные таблицы с системным версионированием. База данных автоматически управляет столбцами времени транзакции row_start и row_end. В PostgreSQL это приходится имитировать, а в SQL Server и MariaDB такая возможность встроена и включается с помощью SYSTEM VERSIONING.

Пример ниже показывает синтаксис SQL Server / MariaDB для иллюстрации этой концепции.

-- SQL Server / MariaDB syntax (reference)
CREATE TABLE dbo.Product (
  ProductID   INT PRIMARY KEY,
  Name        VARCHAR(100),
  Price       DECIMAL(10,2),
  SysStart    DATETIME2 GENERATED ALWAYS AS ROW START,
  SysEnd      DATETIME2 GENERATED ALWAYS AS ROW END,
  PERIOD FOR SYSTEM_TIME (SysStart, SysEnd)
)
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.Product_History));

Временные соединения: согласование двух таблиц во времени

Распространённая задача — соединить две временные таблицы по совпадающим периодам времени. Например, сопоставить зарплаты сотрудников с их закреплением за отделами, когда у обоих есть периоды действия. Соединение выполняется по ключу сущности AND условию пересечения с помощью && для диапазонов или явных сравнений дат.

CREATE TABLE dept_assignment (
  employee_id INT,
  department  VARCHAR(50),
  valid_period DATERANGE
);

INSERT INTO dept_assignment VALUES
  (1, 'Engineering', '[2020-01-01, infinity)'),
  (1, 'Marketing',   '[2019-01-01, 2020-01-01)');

-- Periods where employee 1 was in Engineering AND had salary > 55000
SELECT s.salary, d.department,
       s.valid_period * d.valid_period AS overlap_period
FROM employee_salary_v2 s
JOIN dept_assignment d
  ON s.employee_id = d.employee_id
  AND s.valid_period && d.valid_period
WHERE s.employee_id = 1
  AND s.salary > 55000;

Предотвращение пропусков и пересечений

Две распространённые проблемы качества данных во временных таблицах — это пропуски (периоды без записи) и пересечения (две строки, действующие одновременно). Исключающее ограничение с помощью && предотвращает пересечения на уровне базы данных. Для обнаружения пропусков нужно проверить запросом, какие периоды не покрыты.

-- Find gaps in salary history for employee 1
-- (periods where upper(prev) < lower(next))
SELECT
  upper(a.valid_period) AS gap_start,
  lower(b.valid_period) AS gap_end
FROM employee_salary_v2 a
JOIN employee_salary_v2 b
  ON a.employee_id = b.employee_id
  AND upper(a.valid_period) < lower(b.valid_period)
WHERE a.employee_id = 1
  AND NOT EXISTS (
    SELECT 1 FROM employee_salary_v2 c
    WHERE c.employee_id = 1
      AND lower(c.valid_period) > upper(a.valid_period)
      AND lower(c.valid_period) < lower(b.valid_period)
  )
ORDER BY gap_start;

Проверка знаний

Проверьте, насколько хорошо Вы понимаете временные таблицы и запросы по состоянию на момент времени.

Итоги: временные таблицы и строки с версиями

В этом уроке Вы узнали, как моделировать изменяющиеся во времени данные с помощью столбцов времени действия и типа daterange в PostgreSQL. Основные выводы:

  • Никогда не перезаписывайте исторические строки — закройте старую строку и добавьте новую версию.
  • Используйте запросы по состоянию на момент времени (valid_from <= target AND valid_to > target), чтобы получить данные на любой момент в прошлом.
  • Тип daterange с оператором @> делает запросы к временным данным краткими и понятными.
  • Исключающие ограничения на && (пересечение диапазонов) обеспечивают целостность данных на уровне базы данных.
  • Временные соединения согласуют две истории, находя пересечение их периодов действия.

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

Часто задаваемые вопросы

Урок «Временные строки и строки с версиями» бесплатный?

Да — полный текст урока «Временные строки и строки с версиями» бесплатно доступен здесь в веб-версии. Чтобы практиковать его интерактивно (встроенный редактор кода и ИИ-репетитор 24/7) и разблокировать остальной курс SQL Academy, подпишись на CoddyKit PRO. Курс SQL Academy содержит 4 уроков всего.

Чему я научусь в уроке «Временные строки и строки с версиями»?

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

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

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

Сколько времени занимает урок «Временные строки и строки с версиями»?

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

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

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

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

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