如何最好地进行数据库数据的历史比较？

Question

我有一个包含组织员工名单的数据库，其中包含一个主要员工表和若干个关联表。

我想开始跟踪每周和每月的指标，以便能够做出以下操作：

3月1日：人数100人（2月份增加1人，减少2人）

4月1日：人数101人（3月份增加3人，减少2人）

5月1日：人数105人（4月份增加10人，减少6人）

我正在考虑最好的做法。我应该：

1. 每个月初对数据库进行完整快照，并让我的应用程序查询多个数据库来生成这些报告。 2. 尝试跟踪所有更改并在某个数据库触发的历史表中聚合该信息，以尝试建立每个月的当前状态。 3. 有其他建议吗？

- leora

---

什么平台？例如，SQL Server 2008具有内置的更改跟踪功能。 - Aaronaught

SQL Server 2005，但如果这是一个解决方案适用于2008年，可以满足我上述需求，我会考虑升级。 - leora

我建议详细说明数据大小和您想要在数据上运行的查询类型 - 也许这会激发一些灵感。 - Bandi-T

Answer 1

如果您只想跟踪新员工的入职或离职时间，那么应该首先向员工表本身添加相关字段： HireDate date NOT NULL 和 TerminationDate date NULL。

然后非常容易在任何特定日期确定人数（和详细信息）。

SELECT EmployeeID, EmployeeName, ...
FROM Employees
WHERE HireDate <= @EndDate
AND (TerminationDate IS NULL OR TerminationDate > @BeginDate)

如果您需要追踪修改（例如标题更改），那么最灵活的方法是使用触发器（或者如果可用，则使用数据库内置的更改跟踪）来维护实时历史记录表。我不建议使用完整的快照，因为这将在应用程序的生命周期内消耗大量空间。

您的历史记录表应包含基本表中的所有字段，再加上两个字段-修改日期和事务类型。可能还有第三个自动编号/序列/标识字段。以下是T-SQL版本：

CREATE TABLE EmployeeHistory
(
    TransactionID int NOT NULL IDENTITY(1, 1)
        CONSTRAINT PK_EmployeeHistory PRIMARY KEY CLUSTERED,
    TransactionDate datetime NOT NULL,
    TransactionType tinyint NOT NULL,    -- 1 = Add, 2 = Change, 3 = Delete
    EmployeeID int NOT NULL,
    EmployeeName varchar(100) NOT NULL,
    ...
)

然后使用触发器来维护它：

CREATE TRIGGER tr_Employees_History
ON Employees
FOR INSERT, UPDATE
AS BEGIN
    INSERT EmployeeHistory (TransactionDate, TransactionType, EmployeeID, ...)
        SELECT
            GETDATE(),
            CASE
                WHEN d.EmployeeID IS NULL THEN 1
                WHEN (i.TerminationDate IS NOT NULL) AND
                     (d.TerminationDate IS NULL) THEN 3
                ELSE 2
            END,
            i.EmployeeID, i.EmployeeName, ...
        FROM inserted i
        LEFT JOIN deleted d
        ON d.EmployeeID = i.EmployeeID
END

我假设您不会删除员工记录，而是设置一个“终止日期”；如果您选择删除（请不要这么做），那么您需要编写一个类似的“DELETE”触发器来替换第二个“CASE WHEN i.TerminationDate ...”行。

现在，请填充您的历史记录表：

INSERT EmployeeHistory (TransactionDate, TransactionType, EmployeeID, ...)
    SELECT HireDate, 1, EmployeeID, ...
    FROM Employees

注意 - 如果您没有 HireDate，请用 GETDATE() 替换它 - 您的历史记录只能从种子时刻开始有效。

现在，如果您想获取一个历史 "快照"，可以执行以下操作：

CREATE FUNCTION dbo.GetEmployeeSnapshot(@ReportDate datetime)
RETURNS TABLE
AS RETURN
    WITH History_CTE AS
    (
        SELECT
            TransactionType, EmployeeID, EmployeeName, ...,
            ROW_NUMBER() OVER (ORDER BY TransactionDate DESC) AS RowNum
            FROM EmployeeHistory
            WHERE TransactionDate <= @ReportDate
    )
    SELECT *
    FROM History_CTE
    WHERE RowNum = 1
    AND TransactionType IN (1, 2)    -- Filter out terminated employees

如果这个查询运行缓慢，如果你需要加速某些聚合操作，比如人数统计，那么你才应该考虑使用快照表：

CREATE TABLE HeadcountHistory
(
    ReportDate datetime NOT NULL
        CONSTRAINT PK_HeadcountHistory PRIMARY KEY CLUSTERED,
    HeadCount int NOT NULL
)

更新过程如下：

CREATE PROCEDURE dbo.UpdateHeadcountHistory
AS

DECLARE @ReportDate datetime
SET @ReportDate = GETDATE()

INSERT HeadcountHistory (HeadCount)
    SELECT @ReportDate, COUNT(*)
    FROM dbo.GetEmployeeSnapshot(@ReportDate)

将最后一个存储过程作为定期作业运行，然后您将拥有一个针对特定聚合的非规范化报告表。

如果需要更复杂的内容，建议考虑使用数据仓库。

- Aaronaught

Answer 2

如果您只是按计划运行此程序，那么我建议创建一个数据汇总表...每月运行一次进程来进行计数，并添加一行到代表数据的汇总表中。这样，您可以回顾历史记录并生成所需的任何统计信息。您可能希望考虑在比您计划报告的频率更频繁的基础上生成此数据（例如每周）...只要您具有高于报告期的分辨率，您应该拥有所需的所有数据。

- Michael Bray

---

但是如果我想深入了解并查看历史日期的详细信息，我该如何“返回”并获取那个时刻的状态？ - leora

如果你想了解一个历史日期的详细信息，除了将相应的列复制到另一个表中，你没有太多其他选择。这是一个相当不寻常的要求，我会质疑为什么你需要那么深入的历史知识水平？ - Michael Bray