通过

在系统版本控制时态表中查询数据

  • 项目

适用于： SQL Server 2016 (13.x) 及更高版本 Azure SQL 数据库Azure SQL 托管实例

如果想要获取时态表中数据的最新（当前）状态，可以像查询非时态表一样进行查询。 如果 PERIOD 列未隐藏，其值会显示在 SELECT * 查询中。 如果已将 PERIOD 列指定为 HIDDEN，其值不会显示在 SELECT * 查询中。 当 PERIOD 列隐藏时，必须在 SELECT 子句中明确引用 PERIOD 列以返回这些列的值。

若要执行任何一种基于时间的分析，请将新的 FOR SYSTEM_TIME 子句和四个特定于时态表的子子句结合使用，以便跨当前表和历史记录表查询数据。 有关这些子句的详细信息，请参阅时态表和 FROM 子句加上 JOIN、APPLY、PIVOT

  • AS OF <date_time>
  • FROM <start_date_time> TO <end_date_time>
  • BETWEEN <start_date_time> AND <end_date_time>
  • CONTAINED IN (<start_date_time>, <end_date_time>)
  • ALL

FOR SYSTEM_TIME可以在查询中为每个表单独指定 。 它可以在公用表表达式、表值函数和存储过程内使用。 将表别名与时态表配合使用时，时态表名称和别名之间必须包含 FOR SYSTEM_TIME 子句（请参阅下面“使用 AS OF 子句查询特定时间”中的第二个示例）。

使用 AS OF 子子句查询特定时间

当你因为数据处于过去的任意特定时间而需要重新构造数据状态时，可使用 AS OF 子子句。 你可以使用 PERIOD 列定义中指定的 datetime2 类型的精度来重新构造数据。

AS OF 子子句可以与常量文本或变量结合使用，以便动态指定时间条件。 所提供的值解释为 UTC 时间。

第一个示例返回 dbo.Department 表从过去某个特定日期起的状态。

-- State of entire table AS OF specific date in the past
SELECT [DeptID],
    [DeptName],
    [ValidFrom],
    [ValidTo]
FROM [dbo].[Department]
FOR SYSTEM_TIME AS OF '2021-09-01 T10:00:00.7230011';

第二个示例对行子集两个时间点之间的值进行比较。

DECLARE @ADayAgo DATETIME2;
SET @ADayAgo = DATEADD(DAY, -1, SYSUTCDATETIME());

-- Comparison between two points in time for subset of rows
SELECT D_1_Ago.[DeptID],
    D.[DeptID],
    D_1_Ago.[DeptName],
    D.[DeptName],
    D_1_Ago.[ValidFrom],
    D.[ValidFrom],
    D_1_Ago.[ValidTo],
    D.[ValidTo]
FROM [dbo].[Department]
FOR SYSTEM_TIME AS OF @ADayAgo AS D_1_Ago
INNER JOIN [Department] AS D
    ON D_1_Ago.[DeptID] = [D].[DeptID]
        AND D_1_Ago.[DeptID] BETWEEN 1 AND 5;

在时态查询中将视图与 AS OF 子子句结合使用

在需要进行复杂时间点分析的情况下，使用视图非常有用。 常见的示例是使用上个月的值生成现在的业务报表。

通常情况下，客户会有一个规范化的数据库模型，该模型涉及多个具有外键关系的表。 如何让该规范化模型中的数据看上去像处于过去的某个时间点，要查明此问题可能非常难，因为所有表都按自己的频率独立进行更改。

在这种情况下，最好的选择是创建一个视图并将 AS OF 子子句应用于整个视图。 利用此方法，可以将数据访问层建模从时间点分析分离，因为 SQL Server 会将 AS OF 子句透明地应用于参与视图定义的所有时态表。 此外，还可以将时态表和非时态表组合在同一个视图中，AS OF 将仅应用于时态表。 如果视图不引用任何时态表，那么，对其应用时态查询子句会失败并出现错误。

以下示例代码会创建一个联接三个时态表（Department、CompanyLocation 以及 LocationDepartments）的视图：

CREATE VIEW [dbo].[vw_GetOrgChart]
AS
SELECT [CompanyLocation].LocID,
    [CompanyLocation].LocName,
    [CompanyLocation].City,
    [Department].DeptID,
    [Department].DeptName
FROM [dbo].[CompanyLocation]
LEFT JOIN [dbo].[LocationDepartments]
    ON [CompanyLocation].LocID = LocationDepartments.LocID
LEFT JOIN [dbo].[Department]
    ON LocationDepartments.DeptID = [Department].DeptID;
GO

可以使用 AS OF 子子句和 datetime2 文本查询视图：

/* Querying view AS OF */
SELECT * FROM [vw_GetOrgChart]
FOR SYSTEM_TIME AS OF '2021-09-01 T10:00:00.7230011';

查询一段时间内特定行的更改

如果需要获取当前表中特定行的所有历史更改（也称为数据审核），时态子子句 FROM ... TO、BETWEEN ... AND 和 CONTAINED IN 将非常有用。

前两个子子句返回与指定时间段重叠的行版本（即，在给定时间段之前启动并在其之后结束的行版本），CONTAINED IN 则仅返回指定时间段边界内存在的行版本。

如果仅搜索非最新行版本，应直接查询历史记录表，以获得最佳查询性能。 如果需要在没有任何限制的情况下查询当前数据和历史数据，则使用 ALL。

/* Query using BETWEEN...AND sub-clause*/
SELECT [DeptID],
    [DeptName],
    [ValidFrom],
    [ValidTo],
    IIF(YEAR(ValidTo) = 9999, 1, 0) AS IsActual
FROM [dbo].[Department]
FOR SYSTEM_TIME BETWEEN '2021-01-01' AND '2021-12-31'
WHERE DeptId = 1
ORDER BY ValidFrom DESC;

/* Query using CONTAINED IN sub-clause */
SELECT [DeptID],
    [DeptName],
    [ValidFrom],
    [ValidTo]
FROM [dbo].[Department]
FOR SYSTEM_TIME CONTAINED IN ('2021-04-01', '2021-09-25')
WHERE DeptId = 1
ORDER BY ValidFrom DESC;

/* Query using ALL sub-clause */
SELECT [DeptID],
    [DeptName],
    [ValidFrom],
    [ValidTo],
    IIF(YEAR(ValidTo) = 9999, 1, 0) AS IsActual
FROM [dbo].[Department]
FOR SYSTEM_TIME ALL
ORDER BY [DeptID],
    [ValidFrom] DESC;

相关内容