时态表使用方案

项目
08/01/2024

适用范围：SQL Server

系统版本控制时态表适用于需要跟踪数据更改历史记录的情况。建议在以下用例中考虑使用时态表，可获得巨大的生产力优势。

数据审核

对存储关键信息的表使用时态系统版本控制，可跟踪对这些信息所做的更改和更改发生的时间，以及在任何时间点进行数据取证。

时态表允许在开发周期的早期阶段规划数据审核方案，或者根据需要将数据审核添加到现有应用程序或解决方案。

下图显示了一个 Employee 表，其数据样本包括当前行版本（标记为蓝色）以及历史行版本（标记为灰色）。

图的右侧部分在时间轴上显示了行版本，以及在使用或不使用 SYSTEM_TIME 子句的情况下，针对不同类型的基于时态表的查询选择了哪些行。

对新表启用系统版本控制，以便进行数据审核

如果确定了需要进行数据审核的信息，则请将数据库表创建为系统版本控制时态表。以下示例演示了一个方案，该方案为虚构的 HR 数据库中的 Employee 表：

CREATE TABLE Employee (
    [EmployeeID] INT NOT NULL PRIMARY KEY CLUSTERED,
    [Name] NVARCHAR(100) NOT NULL,
    [Position] VARCHAR(100) NOT NULL,
    [Department] VARCHAR(100) NOT NULL,
    [Address] NVARCHAR(1024) NOT NULL,
    [AnnualSalary] DECIMAL(10, 2) NOT NULL,
    [ValidFrom] DATETIME2(2) GENERATED ALWAYS AS ROW START,
    [ValidTo] DATETIME2(2) GENERATED ALWAYS AS ROW END,
    PERIOD FOR SYSTEM_TIME(ValidFrom, ValidTo)
)
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.EmployeeHistory));

创建系统版本控制时态表中对创建系统版本控制时态表所需的各种选项进行了说明。

对现有表启用系统版本控制，以便进行数据审核

如果要在现有数据库中执行数据审核，可使用 ALTER TABLE 扩展非时态表，使之成为系统版本控制时态表。为了避免在应用程序中进行中断性变更，可以 HIDDEN 方式添加时间段列，如创建系统版本控制时态表中所述。

以下示例说明了如何在虚构 HR 数据库中针对现有 Employee 表启用系统版本控制。它通过两个步骤在 Employee 表中启用系统版本控制。首先，将新时间段列添加为 HIDDEN。然后，它会创建默认历史记录表。

ALTER TABLE Employee ADD
ValidFrom DATETIME2(2) GENERATED ALWAYS AS ROW START HIDDEN
    CONSTRAINT DF_ValidFrom DEFAULT DATEADD (SECOND, -1, SYSUTCDATETIME()),
ValidTo DATETIME2(2) GENERATED ALWAYS AS ROW END HIDDEN
    CONSTRAINT DF_ValidTo DEFAULT '9999.12.31 23:59:59.99',
PERIOD FOR SYSTEM_TIME(ValidFrom, ValidTo);

ALTER TABLE Employee
SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.Employee_History));

重要

datetime2 数据类型的精度在源表和系统版本控制历史记录表中是相同的。

执行以上脚本以后，将会在历史记录表中以透明方式收集所有数据更改。在典型的数据审核方案中，你需要查询在所关注时段内应用到单个行的所有数据更改。可使用聚集行存储 B 树来创建默认的历史记录表，从而高效地解决这种用例问题。

注意

SQL Server 文档在提到索引时一般使用 B 树这个术语。在行存储索引中，SQL Server 实现了 B+ 树。这不适用于列存储索引或内存中数据存储。有关详细信息，请参阅 SQL Server 以及 Azure SQL 索引体系结构和设计指南。

执行数据分析

使用上述方法之一启用系统版本控制以后，只需执行一个查询即可进行数据审核。以下查询将搜索 Employee 表中记录行版本，此表的 EmployeeID = 1000 且在 2021 年 1 月 1 日至 2022 年 1 月 1 日的某段时间（包括上限）内保持活动状态：

SELECT * FROM Employee
FOR SYSTEM_TIME BETWEEN '2021-01-01 00:00:00.0000000'
    AND '2022-01-01 00:00:00.0000000'
WHERE EmployeeID = 1000
ORDER BY ValidFrom;

将 FOR SYSTEM_TIME BETWEEN...AND 替换为 FOR SYSTEM_TIME ALL 以分析该特定员工的整个数据变更历史记录：

SELECT * FROM Employee
FOR SYSTEM_TIME ALL
WHERE EmployeeID = 1000
ORDER BY ValidFrom;

若要搜索仅在某个时间段内（不计该时间段外）处于活动状态的行版本，请使用 CONTAINED IN。此查询非常高效，因为只查询历史记录表：

SELECT * FROM Employee
FOR SYSTEM_TIME CONTAINED IN (
    '2021-01-01 00:00:00.0000000', '2022-01-01 00:00:00.0000000'
)
WHERE EmployeeID = 1000
ORDER BY ValidFrom;

最后，在某些审核方案中，你可能需要了解整个表在过去任意时间点的情况：

SELECT * FROM Employee
FOR SYSTEM_TIME AS OF '2021-01-01 00:00:00.0000000';

系统版本控制时态表在存储时间段列的值时使用 UTC 时区，但你可能会发现在筛选数据和显示结果时，使用本地时区更方便。以下代码示例演示了如何应用筛选条件，该筛选条件使用本地时区指定，然后使用在 SQL Server 2016 (13.x) 中引入的 AT TIME ZONE 转换成了 UTC：

/* Add offset of the local time zone to current time*/
DECLARE @asOf DATETIMEOFFSET = GETDATE() AT TIME ZONE 'Pacific Standard Time';

/* Convert AS OF filter to UTC*/
SET @asOf = DATEADD(HOUR, - 9, @asOf) AT TIME ZONE 'UTC';

SELECT EmployeeID,
    [Name],
    Position,
    Department,
    [Address],
    [AnnualSalary],
    ValidFrom AT TIME ZONE 'Pacific Standard Time' AS ValidFromPT,
    ValidTo AT TIME ZONE 'Pacific Standard Time' AS ValidToPT
FROM Employee
FOR SYSTEM_TIME AS OF @asOf
WHERE EmployeeId = 1000;

所有其他使用系统版本控制型表的方案均可使用 AT TIME ZONE。

使用 FOR SYSTEM_TIME 在时态从句中指定的筛选条件是可执行 SARG 的。 SARG 代表“搜索参数”，可执行 SARG 表示 SQL Server 可以使用基础聚集索引来执行查找而不是扫描操作。有关详细信息，请参阅SQL Server 索引体系结构和设计指南。

如果直接查询历史记录表，请确保筛选条件也是可执行 SARG 的，即在指定筛选器时采用 <period column> { < | > | =, ... } date_condition AT TIME ZONE 'UTC' 形式。

如果将 AT TIME ZONE 应用到时间段列，SQL Server 会执行非常昂贵的表或索引扫描。在查询中要避免这种类型的条件：

<period column> AT TIME ZONE '<your time zone>' > {< | > | =, ...} date_condition。

有关详细信息，请参阅查询系统版本控制时态表中的数据。

时间点分析（按时间顺序查看）

按时间顺序查看方案显示整个数据集随时间变化的方式，而不是重点关注单个记录的更改。有时候，时程包括多个相关的临时表，每个表的变化模式都是独立的，而这也是你需要进行针对性分析的：

历史数据和当前数据中重要指标所指示的趋势
“AS OF”子句所揭示的全部数据在过去任意时间点（昨天、一月前，等等）的精确快照
两个所关注时间点（例如，一月前和三月前）之间的差异

许多实际方案都需要时程分析。为了说明此类使用方案，让我们看看带有自动生成历史记录的 OLTP。

带有自动生成数据历史记录的 OLTP

在事务处理系统中，可以分析重要度量值在一定时段内的变化情况。理想情况下，分析历史记录不会损害 OLTP 应用程序的性能，但前提是访问数据的最新状态时，必须尽量降低延迟并进行数据锁定。可以使用系统版本控制型时态表以透明方式保留所做更改的完整历史记录，以便以后进行分析。这些历史记录独立于当前数据，将对主 OLTP 工作负荷的影响降到最低。

对于需要进行高强度事务处理的工作负荷，建议使用系统版本控制时态表与内存优化表，以便能够以具有成本效益的方法将当前数据存储在内存中，并且将所做更改的完整历史记录存储在磁盘上。

对于历史记录表，建议你使用聚集列存储索引，原因如下：

聚集列存储索引可提高查询性能，这有益于典型的趋势分析。
在 OLTP 工作负荷很重的情况下，如果历史记录表使用聚集列存储索引，则内存优化表的数据刷新任务的执行效果最佳。
聚集列存储索引的压缩效果很好，尤其是在并非所有列的更改都同时发生的情况下。

将临时表与内存中 OLTP 一起使用，则不需总是将整个数据集保留在内存中，因此可轻松地辨识热数据和冷数据。

现实中此类情况的示例包括库存管理、货币贸易等。

下图显示了用于库存管理的简化数据模型：

以下代码示例将 ProductInventory 创建为内存中系统版本控制时态表，所使用的聚集列存储索引基于历史记录表（此表实际上是替换了默认创建的行存储索引）：

注意

请确保你的数据库允许创建内存优化表。请参阅创建内存优化表和本机编译的存储过程。

USE TemporalProductInventory;
GO

BEGIN
    --If table is system-versioned, SYSTEM_VERSIONING must be set to OFF first
    IF ((SELECT temporal_type
        FROM SYS.TABLES
        WHERE object_id = OBJECT_ID('dbo.ProductInventory', 'U')) = 2)
    BEGIN
        ALTER TABLE [dbo].[ProductInventory]
        SET (SYSTEM_VERSIONING = OFF);
    END

    DROP TABLE IF EXISTS [dbo].[ProductInventory];
    DROP TABLE IF EXISTS [dbo].[ProductInventoryHistory];
END
GO

CREATE TABLE [dbo].[ProductInventory] (
    ProductId INT NOT NULL,
    LocationID INT NOT NULL,
    Quantity INT NOT NULL CHECK (Quantity >= 0),
    ValidFrom DATETIME2 GENERATED ALWAYS AS ROW START NOT NULL,
    ValidTo DATETIME2 GENERATED ALWAYS AS ROW END NOT NULL,
    PERIOD FOR SYSTEM_TIME(ValidFrom, ValidTo),
    --Primary key definition
    CONSTRAINT PK_ProductInventory PRIMARY KEY NONCLUSTERED (
        ProductId,
        LocationId
    )
)
WITH (
    MEMORY_OPTIMIZED = ON,
    SYSTEM_VERSIONING = ON (
        HISTORY_TABLE = [dbo].[ProductInventoryHistory],
        DATA_CONSISTENCY_CHECK = ON
    )
);

CREATE CLUSTERED COLUMNSTORE INDEX IX_ProductInventoryHistory
ON [ProductInventoryHistory] WITH (DROP_EXISTING = ON);

对于上述模型来说，下面是库存维护过程的具体内容：

CREATE PROCEDURE [dbo].[spUpdateInventory]
    @productId INT,
    @locationId INT,
    @quantityIncrement INT
    WITH NATIVE_COMPILATION, SCHEMABINDING
AS
BEGIN ATOMIC
    WITH (
        TRANSACTION ISOLATION LEVEL = SNAPSHOT,
        LANGUAGE = N'English'
    )

    UPDATE dbo.ProductInventory
    SET Quantity = Quantity + @quantityIncrement
    WHERE ProductId = @productId
        AND LocationId = @locationId

    -- If zero rows were updated then this is an insert
    -- of the new product for a given location

    IF @@rowcount = 0
    BEGIN
        IF @quantityIncrement < 0
            SET @quantityIncrement = 0

        INSERT INTO [dbo].[ProductInventory] (
            [ProductId], [LocationID], [Quantity]
        )
        VALUES (@productId, @locationId, @quantityIncrement)
    END
END;

spUpdateInventory 存储过程可以将新产品插入库存中，也可以更新特定位置的产品数量。业务逻辑很简单，其注重点是通过表更新对 Quantity 字段进行递增/递减操作，确保最新状态始终准确，而系统版本控制表则以透明方式将历史记录维度添加到数据中，如下图所示。

现在，通过本机编译模块即可高效地执行最新状态查询：

CREATE PROCEDURE [dbo].[spQueryInventoryLatestState]
    WITH NATIVE_COMPILATION, SCHEMABINDING
AS
BEGIN ATOMIC
    WITH (
        TRANSACTION ISOLATION LEVEL = SNAPSHOT,
        LANGUAGE = N'English'
    )

    SELECT ProductId, LocationID, Quantity, ValidFrom
    FROM dbo.ProductInventory
    ORDER BY ProductId, LocationId
END;
GO

EXEC [dbo].[spQueryInventoryLatestState];

使用 FOR SYSTEM_TIME ALL 子句可轻松进行一定时段内的数据更改分析，如以下示例所示：

DROP VIEW IF EXISTS vw_GetProductInventoryHistory;
GO

CREATE VIEW vw_GetProductInventoryHistory
AS
SELECT ProductId,
    LocationId,
    Quantity,
    ValidFrom,
    ValidTo
FROM [dbo].[ProductInventory]
FOR SYSTEM_TIME ALL;
GO

SELECT * FROM vw_GetProductInventoryHistory
WHERE ProductId = 2;

下图显示了一个产品的数据历史记录，可以将上述视图导入 Power Query、Power BI 或类似的商业智能工具来轻松呈现该记录：

这种情况下可以使用时态表进行其他类型的时间旅行分析，例如通过 AS OF 子句重新构造库存在过去任意时间点的状态，或者比较属于不同时刻的快照。

就这种使用方案来说，还可以将 Product 表和 Location 表扩展成为时态表，以便以后对 UnitPrice 和 NumberOfEmployee 的更改历史记录进行分析。

ALTER TABLE Product ADD
ValidFrom DATETIME2 GENERATED ALWAYS AS ROW START HIDDEN
    CONSTRAINT DF_ValidFrom DEFAULT DATEADD (SECOND, -1, SYSUTCDATETIME()),
ValidTo DATETIME2 GENERATED ALWAYS AS ROW END HIDDEN
    CONSTRAINT DF_ValidTo DEFAULT '9999.12.31 23:59:59.99',
PERIOD FOR SYSTEM_TIME(ValidFrom, ValidTo);

ALTER TABLE Product
SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.ProductHistory));

ALTER TABLE [Location] ADD
ValidFrom DATETIME2 GENERATED ALWAYS AS ROW START HIDDEN
    CONSTRAINT DFValidFrom DEFAULT DATEADD (SECOND, -1, SYSUTCDATETIME()),
ValidTo DATETIME2 GENERATED ALWAYS AS ROW END HIDDEN
    CONSTRAINT DFValidTo DEFAULT '9999.12.31 23:59:59.99',
PERIOD FOR SYSTEM_TIME(ValidFrom, ValidTo);

ALTER TABLE [Location]
SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.LocationHistory));

由于数据模型现在涉及多个时态表，因此进行 AS OF 分析的最佳做法是创建一个可从相关表中提取必需数据的视图，然后将 FOR SYSTEM_TIME AS OF 应用到该视图，这样可以大大简化完整数据模型状态的重新构造过程：

DROP VIEW IF EXISTS vw_ProductInventoryDetails;
GO

CREATE VIEW vw_ProductInventoryDetails
AS
SELECT PrInv.ProductId,
    PrInv.LocationId,
    P.ProductName,
    L.LocationName,
    PrInv.Quantity,
    P.UnitPrice,
    L.NumberOfEmployees,
    P.ValidFrom AS ProductStartTime,
    P.ValidTo AS ProductEndTime,
    L.ValidFrom AS LocationStartTime,
    L.ValidTo AS LocationEndTime,
    PrInv.ValidFrom AS InventoryStartTime,
    PrInv.ValidTo AS InventoryEndTime
FROM dbo.ProductInventory AS PrInv
INNER JOIN dbo.Product AS P
    ON PrInv.ProductId = P.ProductID
INNER JOIN dbo.Location AS L
    ON PrInv.LocationId = L.LocationID;
GO

SELECT * FROM vw_ProductInventoryDetails
FOR SYSTEM_TIME AS OF '2022-01-01';

以下屏幕截图显示了为 SELECT 查询生成的执行计划。这表明，在处理时态关系时，不管过程怎么复杂，都可以通过数据库引擎得到妥善处理：

使用以下代码来比较两个时间点（一天前和一月前）的产品库存状态：

DECLARE @dayAgo DATETIME2 = DATEADD (DAY, -1, SYSUTCDATETIME());
DECLARE @monthAgo DATETIME2 = DATEADD (MONTH, -1, SYSUTCDATETIME());

SELECT inventoryDayAgo.ProductId,
    inventoryDayAgo.ProductName,
    inventoryDayAgo.LocationName,
    inventoryDayAgo.Quantity AS QuantityDayAgo,
    inventoryMonthAgo.Quantity AS QuantityMonthAgo,
    inventoryDayAgo.UnitPrice AS UnitPriceDayAgo,
    inventoryMonthAgo.UnitPrice AS UnitPriceMonthAgo
FROM vw_ProductInventoryDetails
FOR SYSTEM_TIME AS OF @dayAgo AS inventoryDayAgo
INNER JOIN vw_ProductInventoryDetails
FOR SYSTEM_TIME AS OF @monthAgo AS inventoryMonthAgo
    ON inventoryDayAgo.ProductId = inventoryMonthAgo.ProductId
        AND inventoryDayAgo.LocationId = inventoryMonthAgo.LocationID;

异常检测

异常检测（或离群值检测）是指确定哪些项目不符合期望的模式或者不同于数据集中的其他项目。可以使用系统版本控制型临时表来检测定期或不定期发生的异常，因为你可以利用临时查询来快速查找特定模式。具体异常取决于所收集数据的类型以及业务逻辑。

以下示例显示了在销售数字中检测“峰值”所需的简化逻辑。假定你要处理一个临时表，该表收集所购产品的历史记录：

CREATE TABLE [dbo].[Product] (
    [ProdID] [int] NOT NULL PRIMARY KEY CLUSTERED,
    [ProductName] [varchar](100) NOT NULL,
    [DailySales] INT NOT NULL,
    [ValidFrom] [datetime2] GENERATED ALWAYS AS ROW START NOT NULL,
    [ValidTo] [datetime2] GENERATED ALWAYS AS ROW END NOT NULL,
    PERIOD FOR SYSTEM_TIME([ValidFrom], [ValidTo])
)
WITH (
    SYSTEM_VERSIONING = ON (
        HISTORY_TABLE = [dbo].[ProductHistory],
        DATA_CONSISTENCY_CHECK = ON
    )
);

下图显示了一定时段内的购买项目：

假定购买产品的数目在平时有一些小的差异，则可使用以下查询来确定单一离群值。单一离群值指的是与最近的示例相比具有显著差异（2 倍），但与周围的示例相比没有显著差异（不到 20%）的示例：

WITH CTE (
    ProdId,
    PrevValue,
    CurrentValue,
    NextValue,
    ValidFrom,
    ValidTo
    )
AS (
    SELECT ProdId,
        LAG(DailySales, 1, 1) OVER (PARTITION BY ProdId ORDER BY ValidFrom) AS PrevValue,
        DailySales,
        LEAD(DailySales, 1, 1) OVER (PARTITION BY ProdId ORDER BY ValidFrom) AS NextValue,
        ValidFrom,
        ValidTo
    FROM Product
    FOR SYSTEM_TIME ALL
    )
SELECT ProdId,
    PrevValue,
    CurrentValue,
    NextValue,
    ValidFrom,
    ValidTo,
    ABS(PrevValue - NextValue) / convert(FLOAT, (
            CASE
                WHEN NextValue > PrevValue THEN PrevValue
                ELSE NextValue
            END)) AS PrevToNextDiff,
    ABS(CurrentValue - PrevValue) / convert(FLOAT, (
            CASE
                WHEN CurrentValue > PrevValue THEN PrevValue
                ELSE CurrentValue
            END)) AS CurrentToPrevDiff,
    ABS(CurrentValue - NextValue) / convert(FLOAT, (
            CASE
                WHEN CurrentValue > NextValue THEN NextValue
                ELSE CurrentValue
            END)) AS CurrentToNextDiff
FROM CTE
WHERE ABS(PrevValue - NextValue) / (
        CASE
            WHEN NextValue > PrevValue THEN PrevValue
            ELSE NextValue
        END) < 0.2
    AND ABS(CurrentValue - PrevValue) / (
        CASE
            WHEN CurrentValue > PrevValue THEN PrevValue
            ELSE CurrentValue
        END) > 2
    AND ABS(CurrentValue - NextValue) / (
        CASE
            WHEN CurrentValue > NextValue THEN NextValue
            ELSE CurrentValue
        END) > 2;

注意

此示例已特意简化。在生产方案中，你可能会使用高级统计方法来确定不遵循通用模式的样本。

渐变维度

数据仓库中的维度通常包含相对静态的与以下实体相关的数据：地理位置、客户或产品。不过，某些方案会要求你也跟踪数据在维度表中的更改。由于维度修改的发生频率要小得多、修改方式不可预测，并且超出常规的事实数据表更新计划，因此这些类型的维度表称为缓慢变化的维度 (SCD)。

根据更改历史记录的保留方式，可以将渐变维度分为多个类别：

维度类型	详细信息
类型 0	不保留历史记录。维度属性反映原始值。
类型 1	维度属性反映最新值（覆盖以前的值）
类型 2	每个版本的维度成员在表中使用不同的行表示，通常使用列表示有效期
类型 3	在同一行中使用额外列保留所选属性的有限历史记录
类型 4	在单独的表中保留历史记录，而原始维度表则保留最新（当前）的维度成员版本

选择 SCD 策略时，由 ETL（提取-转换-加载）层确保维度表的准确性，通常这需要编写更复杂的代码并完成额外的维护工作。

使用系统版本控制型时态表可以大幅降低代码的复杂性，因为会自动保留数据的历史记录。由于是使用两个表来实现的，时态表最接近于类型 4 SCD。但是，由于时态查询只允许你引用当前表，因此也可以考虑在计划使用类型 2 SCD 的环境中使用时态表。

要将常规维度转换为 SCD，可以创建一个新表，也可以将现有表更改为系统版本控制型时态表。如果现有维度表包含历史数据，可创建一个单独的表，将历史数据移到其中，并将当前（实际）的维度版本保留在原始维度表中。然后，使用 ALTER TABLE 语法通过预定义历史记录表将维度表转换为系统版本控制时态表。

以下示例演示了该过程，并假定 DimLocation 维度表已经有 ValidFrom 和 ValidTo 作为不可为 null 的 datetime2 列，并通过 ETL 过程对这些列进行了填充：

/* Move "closed" row versions into newly created history table*/
SELECT * INTO DimLocationHistory
FROM DimLocation
WHERE ValidTo < '9999-12-31 23:59:59.99';
GO

/* Create clustered columnstore index which is a very good choice in DW scenarios*/
CREATE CLUSTERED COLUMNSTORE INDEX IX_DimLocationHistory ON DimLocationHistory;

/* Delete previous versions from DimLocation which will become current table in temporal-system-versioning configuration*/
DELETE FROM DimLocation
WHERE ValidTo < '9999-12-31 23:59:59.99';

/* Add period definition*/
ALTER TABLE DimLocation
ADD PERIOD FOR SYSTEM_TIME(ValidFrom, ValidTo);

/* Enable system-versioning and bind history table to the DimLocation*/
ALTER TABLE DimLocation
SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.DimLocationHistory));

创建以后，就不需要在数据仓库加载过程中使用额外的代码来维护 SCD。

下图演示了如何在涉及 2 个 SCD（DimLocation 和 DimProduct）和 1 个事实数据表的简单方案中使用时态表。

要在报表中使用上述 SCD，需对查询进行有效调整。例如，你可能需要计算过去六个月的总销售额和人均销售产品数。这两个指标都需要对事实数据表和维度中的数据进行更正，这些数据可能更改了对分析来说很重要的属性（DimLocation.NumOfCustomers、DimProduct.UnitPrice）。以下查询对所需指标进行了正确的计算：

DECLARE @now DATETIME2 = SYSUTCDATETIME();
DECLARE @sixMonthsAgo DATETIME2;

SET @sixMonthsAgo = DATEADD(month, - 12, SYSUTCDATETIME());

SELECT DimProduct_History.ProductId,
    DimLocation_History.LocationId,
    SUM(F.Quantity * DimProduct_History.UnitPrice) AS TotalAmount,
    AVG(F.Quantity / DimLocation_History.NumOfCustomers) AS AverageProductsPerCapita
FROM FactProductSales F
/* find corresponding record in SCD history in last 6 months, based on matching fact */
INNER JOIN DimLocation
FOR SYSTEM_TIME BETWEEN @sixMonthsAgo AND @now AS DimLocation_History
    ON DimLocation_History.LocationId = F.LocationId
    AND F.FactDate BETWEEN DimLocation_History.ValidFrom AND DimLocation_History.ValidTo
/* find corresponding record in SCD history in last 6 months, based on matching fact */
INNER JOIN DimProduct
FOR SYSTEM_TIME BETWEEN @sixMonthsAgo AND @now AS DimProduct_History
    ON DimProduct_History.ProductId = F.ProductId
    AND F.FactDate BETWEEN DimProduct_History.ValidFrom AND DimProduct_History.ValidTo
WHERE F.FactDate BETWEEN @sixMonthsAgo AND @now
GROUP BY DimProduct_History.ProductId, DimLocation_History.LocationId;

注意事项

对 SCD 使用由系统控制版本的时态表是可以接受的，前提是根据数据库事务时间计算的有效期适用于你的业务逻辑。如果加载数据时的延迟很严重，则事务时间可能是不可接受的。

默认情况下，系统版本控制时态表不允许在加载后更改历史记录数据（将 SYSTEM_VERSIONING 设置为 OFF 后可以修改历史记录）。在频繁更改历史数据的情况下，这可能导致功能受限。

只要列发生更改，系统版本控制型临时表就会生成行版本。如果你想要在进行特定的列更改时禁止显示新版本，则需在 ETL 逻辑中纳入该限制。

如果你预计 SCD 表中会有大量的历史行，则可考虑使用聚集列存储索引作为历史记录表的主要存储选项。使用列存储索引可减少历史记录表占用情况，并加快分析查询的速度。

修复行级数据损坏

你可以依赖由系统控制版本的时态表中的历史数据，将各个行快速修复到以前捕获的任何状态。如果能够找到受影响的行，并且/或者知道在何时进行了不需要的数据更改，则时态表的这种属性会很有用。借助此知识，无需处理备份，即可高效执行修复。

此方法有多种优点：

你可以精确控制修复范围。不受影响的记录需保持最新状态，这通常是一项很关键的要求。
操作很高效，数据库会保持联机状态，以便所有工作负荷使用数据。
修复操作本身也会进行版本控制。你有修复操作本身的审核记录，因此可以在以后根据需要分析所发生的情况。

可以相对轻松地自动执行修复操作。下一个代码示例显示了针对数据审核方案中使用的 Employee 表执行数据修复的存储过程。

DROP PROCEDURE IF EXISTS sp_RepairEmployeeRecord;
GO

CREATE PROCEDURE sp_RepairEmployeeRecord
    @EmployeeID INT,
    @versionNumber INT = 1
AS
WITH History
AS (
    /* Order historical rows by their age in DESC order*/
    SELECT
        ROW_NUMBER() OVER (PARTITION BY EmployeeID
            ORDER BY [ValidTo] DESC) AS RN,
        *
    FROM Employee FOR SYSTEM_TIME ALL
    WHERE YEAR(ValidTo) < 9999 AND Employee.EmployeeID = @EmployeeID
)
/* Update current row by using N-th row version from history (default is 1 - i.e. last version) */
UPDATE Employee
SET [Position] = H.[Position],
    [Department] = H.Department,
    [Address] = H.[Address],
    AnnualSalary = H.AnnualSalary
FROM Employee E
INNER JOIN History H ON E.EmployeeID = H.EmployeeID AND RN = @versionNumber
WHERE E.EmployeeID = @EmployeeID;

此存储过程采用 @EmployeeID 和 @versionNumber 作为输入参数。默认情况下，此过程将行状态还原到历史记录中的最后一个版本 (@versionNumber = 1)。

下图显示了过程调用前后的行状态。红色矩形标记的是不正确的当前行版本，绿色矩形标记的是历史记录中的正确版本。

EXEC sp_RepairEmployeeRecord @EmployeeID = 1, @versionNumber = 1;

可以将该修复存储过程定义为接受具体的时间戳而非行版本。该过程会将行还原为在所提供的时间点（即 AS OF 时间点）处于活动状态的任何版本。

DROP PROCEDURE IF EXISTS sp_RepairEmployeeRecordAsOf;
GO

CREATE PROCEDURE sp_RepairEmployeeRecordAsOf
    @EmployeeID INT,
    @asOf DATETIME2
AS
/* Update current row to the state that was actual AS OF provided date*/
UPDATE Employee
SET [Position] = History.[Position],
    [Department] = History.Department,
    [Address] = History.[Address],
    AnnualSalary = History.AnnualSalary
FROM Employee AS E
INNER JOIN Employee FOR SYSTEM_TIME AS OF @asOf AS History
    ON E.EmployeeID = History.EmployeeID
WHERE E.EmployeeID = @EmployeeID;

对于同一数据样本，下图显示的是带有时间条件的修复方案。突出显示的是 @asOf 参数、历史记录中选择的所提供时间点的实际行，以及进行修复操作后当前表中的全新行版本：

在数据仓库和报表系统中自动加载数据时，可能会进行数据更正。很多情况下，如果刚更新的值不正确，从历史记录中还原以前的版本即可。下图显示了自动执行此过程的方法：

关系图显示如何自动执行过程。

通过

时态表使用方案

数据审核

对新表启用系统版本控制，以便进行数据审核

对现有表启用系统版本控制，以便进行数据审核

执行数据分析

时间点分析（按时间顺序查看）

带有自动生成数据历史记录的 OLTP

异常检测

渐变维度

注意事项

修复行级数据损坏

反馈

其他资源

通过

时态表使用方案

数据审核

对新表启用系统版本控制，以便进行数据审核

对现有表启用系统版本控制，以便进行数据审核

执行数据分析

时间点分析（按时间顺序查看）

带有自动生成数据历史记录的 OLTP

异常检测

渐变维度

注意事项

修复行级数据损坏

相关内容

反馈

其他资源