mysql - 使用 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 开发面向时间的数据库应用程序》一书，可作为 a free PDF download 获取.看起来您可以直接跳到第 5 章并通读第 6 章和第 7 章(但不要忽略后面章节中的替代方法)。

关于实现，postgreSQL 目前一般都具有良好的时间支持和对可延迟约束的支持(这在 SQL 中是至关重要的!对于顺序键等概念)。

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