SQL Academy · 课时

时态行与版本化行

Q: 「时态行与版本化行」这节课中我会学到什么？

有效时间查询与截至某时点查询。 你通过在浏览器中直接运行的动手代码来练习 SQL Academy，全天候 AI 导师会在你学习这节课的过程中回答你的问题。

有效时间查询与截至某时点查询。

时态行与版本化行 是 CoddyKit 上的免费 SQL Academy 课时。这是第 3 节课，共 4 节。你可以在下方免费阅读本课时的完整内容 — 然后在浏览器中使用内置代码编辑器和全天候 AI 导师进行实践。这是 SQL Academy 学习路径的一部分，你的进度在网页和 CoddyKit 应用中同步。 SQL Academy 课程共包含 4 节课。

什么是时态表

时态表可以跟踪数据随时间发生的变化。数据发生变化时，时态表不会覆盖原有行，而是保留该行的每个版本，并为每个版本标记其有效时间段。

这里有两个关键概念：有效时间（事实在现实世界中成立的时间）和事务时间（数据库记录该事实的时间）。将两者结合起来，就能得到完整的双时态表。

有效时间与事务时间

有效时间表示某个事实在现实世界中成立的时间，例如员工从 2020-01-01 到 2022-06-30 的薪资。事务时间表示数据库插入或使该行失效的时间。两者结合后，可以回答两个问题：当时什么是真实的？以及我们什么时候知道这件事？

大多数实际应用会先从有效时间跟踪开始，您可以使用 valid_from 和 valid_to 列手动实现它。

创建有效时间表

存储版本化行最简单的方法，是添加 valid_from 和 valid_to 时间戳列。valid_to 为 NULL（或使用类似 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 表示有效期

PostgreSQL 的 daterange 类型可以优雅地将有效期表示为单个列。您可以使用 @>（包含）运算符检查某个日期是否落在范围内，并添加排除约束，防止同一实体的有效期相互重叠。

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;

知识检验

测试您对时态表和时点查询的理解。

回顾：时态行和版本化行

在本课中，您学习了如何使用有效时间列和 PostgreSQL 的 daterange 类型来建模随时间变化的数据。要点：

绝不要覆盖历史行——关闭旧版本，然后插入新版本。
使用时点查询（valid_from <= target AND valid_to > target）来获取过去任意时刻的数据。
daterange 类型结合 @> 运算符，可以让时态查询简洁易读。
对 &&（范围重叠）添加排除约束，可以在数据库层面保证数据完整性。
时态连接通过求两个历史记录的有效时间段交集来对齐它们。

这些模式构成了事件溯源、审计日志记录以及任何重视历史准确性的系统的基础。

常见问题解答

「时态行与版本化行」课时是免费的吗？

是的 — 「时态行与版本化行」的完整文本可在网页上免费阅读。要进行交互式练习（内置代码编辑器和全天候 AI 导师）并解锁 SQL Academy 课程的其余内容，请升级到 CoddyKit PRO。 SQL Academy 课程共包含 4 节课。

「时态行与版本化行」这节课中我会学到什么？

有效时间查询与截至某时点查询。你通过在浏览器中直接运行的动手代码来练习 SQL Academy，全天候 AI 导师会在你学习这节课的过程中回答你的问题。

学习 SQL Academy 需要有经验吗？

无需任何先前经验。CoddyKit 上的 SQL Academy 课程适合初学者到高级学习者，你可以从这里开始或从头开始，按照自己的节奏学习。这是第 3 节课，共 4 节。

「时态行与版本化行」课时需要多长时间？

大多数 CoddyKit 课程大约需要 5–10 分钟。每节课都很精短且互动，所以你能稳步进步，并在网页和应用中从离开的地方继续。

我能在这节 SQL Academy 课中编写并运行代码吗？

能。每节 SQL Academy 课都包含内置代码编辑器，你可以在浏览器中直接编写并运行真实代码，并获得即时 AI 反馈 — 无需本地设置。

此课程中的所有课时

← 返回 SQL Academy