システム バージョン管理されたテンポラル テーブル内のデータに対してクエリを実行する

  • [アーティクル]

適用対象: SQL Server 2016 (13.x) 以降 Azure SQL DatabaseAzure SQL Managed Instance

テンポラル テーブルのデータの最新 (現在) の状態を取得するときは、テンポラル以外のテーブルをクエリするときと同じ方法でクエリできます。 列が PERIOD 非表示になっていない場合は、その値がクエリに SELECT * 表示されます。 としてHIDDEN列を指定したPERIOD場合、その値はクエリにSELECT *表示されません。 PERIOD 列が非表示の場合、PERIOD 列の値を返すには、SELECT 句でそれらの列を明示的に参照する必要があります。

任意の種類の時間ベースの分析を実行するには、新しい FOR SYSTEM_TIME 句を使用し、テンポラル固有の 4 つのサブ句を指定して、現在のテーブルと履歴テーブルにわたってデータをクエリします。 これらの句の詳細については、「テンポラル テーブル」と「FROM (Transact-SQL)」を参照してください

  • 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 テンポラル テーブル名とエイリアスの間に 句を含める必要があります (サブクラスの 2 番目の例を 使用した AS OF 特定の時刻のクエリに関 するページを参照してください)。

AS OF サブ句を使用した特定時点のクエリ

過去の特定時点におけるデータの状態を再構築する必要がある場合は、AS OF サブ句を使用します。 PERIOD 列の定義で指定されている datetime2 型の精度でデータを再構築できます。

サブクラスは AS OF 、定数リテラルまたは変数と共に使用できるため、時間条件を動的に指定できます。 指定した値は UTC 時刻として解釈されます。

この最初の例では、過去の特定の日時 (AS OF) における 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';

この 2 番目の例では、行のサブセットの値が 2 つの時点について比較されます。

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 にビューを使用する

複雑な特定時点分析が必要なときは、ビューが役に立ちます。 一般的な例は、過去 1 か月の値を使用して今日のビジネス レポートを生成する場合です。

通常、ユーザーは外部キー リレーションシップを持つ多数のテーブルを含む正規化されたデータベース モデルを使用します。 すべてのテーブルは個別に独立したパターンで変化するので、その正規化されたモデルのデータが過去の特定の時点においてどのようなものになるかを調べるのは難しい場合があります。

このような場合に最善の方法は、ビューを作成し、AS OF サブ句をビュー全体に適用することです。 この方法を使用すると、ビュー定義に含まれるすべてのテンポラル テーブルに句が透過的に適用AS OFSQL Server、データ アクセス層のモデリングをポイントインタイム分析から切り離すことができます。 さらに、テンポラル テーブルと非テンポラル テーブルを同じビューで組み合わせることができ、 AS OF テンポラル テーブルにのみ適用されます。 ビューが少なくとも 1 つのテンポラル テーブルを参照していない場合、テンポラル クエリ句を適用するとエラーで失敗します。

次のサンプル コードでは、および の 3 つのテンポラル テーブルDepartmentCompanyLocationを結合するビューを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

これで、サブクラスと datetime2 リテラルをAS OF使用してビューに対してクエリを実行できるようになりました。

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

または、ローカル タイム ゾーンと でサブクラスを AS OF 使用してビューに対してクエリを AT TIME ZONE実行することもできます。

/* Querying view AS OF with local time*/
DECLARE @LocalTime DATETIMEOFFSET = '2021-09-01 10:00:00.7230011 -07:00';

SELECT * FROM [vw_GetOrgChart]
FOR SYSTEM_TIME AS OF @LocalTime AT TIME ZONE 'UTC';

一定期間における特定の行への変更のクエリ

テンポラル サブ句 FROM ... TO、BETWEEN ... AND、CONTAINED IN は、現在のテーブル内の特定の行に対するすべての変更履歴を取得する必要がある場合に、役に立ちます (データ監査とも呼ばれます)。

最初の 2 つのサブ句は指定された期間と重なる行のバージョンを返します (つまり、指定された期間より前に開始し、期間の後で終了するバージョン) が、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;

次のステップ