使用 SQL/关系数据库存储和检索历史数据

Question

鉴于此表：

CREATE TABLE DeptPeopleHistory (
  DEPT_ID INTEGER,
  PERSON_ID INTEGER,
  START_DATE INTEGER,
  END_DATE INTEGER,
  UNIQUE(DEPT_ID, START_DATE, PERSON_ID), -- works as sorted index.
  UNIQUE(PERSON_ID, START_DATE),
  UNIQUE(PERSON_ID, END_DATE),
  CONSTRAINT (START_DATE < END_DATE)
);

我有两个需求。第一个是获取在给定日期在给定部门工作的所有人员。目前我使用这个（语义上正确的）查询：

SELECT PERSON_ID FROM DeptPeopleHistory
WHERE
  DEPT_IT = :given_dept AND
  START_DATE <= :given_date AND :given_date < END_DATE

这对于小型历史表或查询最近的数据来说很快，但对于大型历史表和旧数据来说很慢，因为优化器仅使用第一个索引，并且没有好的方法来处理 END_DATE 。我尝试将 END_DATE 添加到第一个索引，但查询性能是相同的。我猜这是因为子过滤器 (DEPT_IT=:given_dept AND START_DATE <= :given_date) 应用于排序索引 (DEPT_ID、START_DATE、END_DATE、PERSON_ID) 时会产生未排序的 END_DATE 数据，因此 (:given_date < END_DATE ）仍然需要对结果进行顺序扫描。

我的另一个需求是强制执行以下约束：一个人不能同时在两个部门工作，也不能在同一部门工作两次。这意味着：

-- This must work for previously empty data:
INSERT INTO DeptPeopleHistory(DEPT_ID, PERSON_ID, START_DATE, END_DATE)
                      VALUES (1,       1,         20100501,   20100520);

-- This should cause constraint violation because the person already
-- works at dept 1 on days from 20100517 to 20100519:
INSERT INTO DeptPeopleHistory(DEPT_ID,   PERSON_ID, START_DATE, END_DATE)
                      VALUES (:any_dept, 1,         20100517,   20100523);

指定此约束的另一种方法是，对于给定的 PERSON_ID，START_DATE 必须是另一条记录中的最小值或等于 END_DATE。

考虑到这两个需求，我们实际上需要一种有效的方法来处理非相交范围。您是否知道通用 SQL 或某些特定数据库中的某些功能或结构可以满足这些需求？也许有一些“空间数据库”功能？

这些示例是在 MySQL 中进行的，但我需要适用于 Oracle、SQL Server 和 FireBird 的解决方案。这些解决方案不需要在所有此类数据库之间移植。

Answer 1

作为起点，我推荐 Rick Snodgrass 编写的《用 SQL 开发面向时间的数据库应用程序》一书，可通过以下网址获取： 免费 PDF 下载。看起来您可以直接跳到第 5 章并通读第 6 章和第 7 章（但不要忽略后面章节中的替代方法）。

至于实现，postgreSQL 目前总体上具有良好的时间支持以及对可延迟约束的支持（这对于 SQL 中至关重要！对于诸如排序键之类的概念）。

请注意，还有其他时态数据库模型，例如 Date Darwen Lorentzos。

Answer 2

您是否尝试过在 DEPT_ID 和 END_DATE 上添加另一个索引？如果您使用 MySQL 5+，它可能能够进行索引合并并使用该索引和 DEPT_ID、START_DATE、PERSON_ID 索引。

至于你的第二个问题，我认为强制执行这种类型的约束的唯一方法是通过应用程序逻辑或插入/更新触发器。

Answer 3

是否可以将表 DeptPeopleHistory 的结构更改为？：

CREATE TABLE DeptPeopleHistoryDetail (
  DEPT_ID INTEGER,
  PERSON_ID INTEGER,
  WORK_DATE INTEGER,               --- why is that INT and not DATE by the way?
  UNIQUE(WORK_DATE, PERSON_ID)
);

优点：

• 您不需要强制执行任何之前的 UNIQUE 约束，也不需要强制执行 START_DATE < END_DATE 一。
• 第二个复杂的约束也神奇地得到了解决。

缺点：

• 上一个示例中的 (1, 1, 20100501, 20100520) 现在分为 20 行。我想说，这不是一个真正的问题。关系数据库旨在处理许多行。
• 要查找部门中某个人员的 START_DATE 或 END_DATE，必须运行查询。 （如果这太慢，我怀疑，可以使用额外的表）

---

哦，你的慢查询将被写成：

SELECT PERSON_ID FROM DeptPeopleHistoryDetail
WHERE
  DEPT_IT = :given_dept AND
  WORK_DATE = :given_date 

---

使用你当前的DeptPeopleHistory设计，你能尝试以下的性能询问？

SELECT H.PERSON_ID
FROM DeptPeopleHistory H
  JOIN
    ( SELECT PERSON_ID
           , MAX(START_DATE) AS LATEST_START_DATE
      FROM DeptPeopleHistory
      WHERE
        DEPT_IT = :given_dept AND
        START_DATE <= :given_date
      GROUP BY
        PERSON_ID
    ) AS grp
    ON  H.DEPT_IT = :given_dept
    AND grp.PERSON_ID = H.PERSON_ID
    AND grp.LATEST_START_DATE = H.START_DATE
WHERE 
   :given_date < H.END_DATE