対象者:
SQL Server 2016 (13.x) およびそれ以降のバージョン Azure SQL DatabaseAzure SQL Managed InstanceSQL Database in Microsoft Fabric
時間テーブルの最新(現在)状態を取得するには、非時間テーブルと同じ方法でクエリを行います。
PERIOD 列が非表示ではない場合は、それらの値が SELECT * クエリで表示されます。
PERIOD列をHIDDENと指定すると、その値はSELECT *クエリには表示されません。
PERIOD列が非表示になったら、SELECT節で具体的に参照して値を返してください。
時間ベース解析を行うには、 FOR SYSTEM_TIME 節と4つの時間固有のサブ節を用いて、現在テーブルと履歴テーブルにまたがってデータをクエリします。 これらの句の詳細については、テンポラル テーブルおよび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時間として解釈されます。
この最初の例は、過去の特定の日付AS OFdbo.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 AS 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 はテンポラル テーブルに対してのみ適用されます。 ビューでテンポラル テーブルが 1 つも参照されていない場合、テンポラル クエリ句をテーブルに適用するとエラーで失敗します。
次のサンプル コードでは、次の 3 つのテンポラル テーブル (Department、CompanyLocation、LocationDepartments) を結合するビューを作成します。
CREATE VIEW [dbo].[vw_GetOrgChart]
AS
SELECT [CompanyLocation].LocID,
[CompanyLocation].LocName,
[CompanyLocation].City,
[Department].DeptID,
[Department].DeptName
FROM [dbo].[CompanyLocation]
LEFT OUTER JOIN [dbo].[LocationDepartments]
ON [CompanyLocation].LocID = LocationDepartments.LocID
LEFT OUTER JOIN [dbo].[Department]
ON LocationDepartments.DeptID = [Department].DeptID;
GO
AS OF サブ句と datetime2 リテラルを使用して、ビューに対してクエリを実行できます。
/* Query 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 は、現在のテーブルの特定の行のすべての履歴変更を取得する必要がある場合(データ監査とも呼ばれる)に役立ちます。
最初の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;
時間枠を通じた傾向を分析する
期間を通じてのデータがどのように見えるかを要約するために、 FOR SYSTEM_TIME BETWEEN ... AND と GROUP BY 関数および集計関数を組み合わせます。 この手法は記述的統計やトレンド分析に用いてください。 ウィンドウ内で有効だったすべての行バージョンを集約するのであって、単一時点を再構築するのではありません。
以下の例は、部門ごとにグループ化された期間にわたる従業員給与の記述統計を計算しています。
テンポラル テーブルおよびEmployeeで定義されているシステム バージョン管理テーブルを使用します。
AnnualSalary列は、AVG、MIN、MAX、STDEV関数に適した数値指標です。
FOR SYSTEM_TIME BETWEEN ... AND節は、その期間が重なるすべての行バージョンを返します。 その結果、集計額にはその期間中に有効だったすべての給与が含まれています。 従業員の給与が変更されると、クエリはその従業員の複数のバージョンを返し、それぞれのバージョンが集計に寄与します。
DECLARE @periodStart AS DATETIME2 = '2021-01-01';
DECLARE @periodEnd AS DATETIME2 = '2021-12-31';
SELECT [Department],
AVG([AnnualSalary]) AS AvgSalary,
MIN([AnnualSalary]) AS MinSalary,
MAX([AnnualSalary]) AS MaxSalary,
STDEV([AnnualSalary]) AS SalaryStdDev,
COUNT(*) AS RowVersions
FROM [dbo].[Employee] FOR SYSTEM_TIME
BETWEEN @periodStart AND @periodEnd
GROUP BY [Department]
ORDER BY AvgSalary DESC;