Fråga efter data i en systemversionsbaserad temporal tabell

Gäller för: SQL Server 2016 (13.x) och senare versioner Azure SQL DatabaseAzure SQL Managed InstanceSQL-databas i Microsoft Fabric

För att få det senaste (aktuella) dataläget i en temporär tabell, fråga den på samma sätt som i en icke-temporal tabell. Om de PERIOD kolumnerna inte är dolda visas deras värden i en SELECT * fråga. Om du anger PERIOD kolumner som HIDDEN, visas deras värden inte i en SELECT * fråga. När kolumnerna PERIOD är dolda, referera specifikt till dem i klausulen SELECT för att returnera deras värden.

För att utföra tidsbaserad analys, använd klausulen FOR SYSTEM_TIME med fyra tidsspecifika undersatser för att söka data över nuvarande och historiktabeller. Mer information om dessa klausuler finns i Temporal-tabeller och FROM-klausul plus 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

Du kan ange FOR SYSTEM_TIME oberoende för varje tabell i en fråga. Använd den i vanliga tabelluttryck, tabellvärda funktioner och lagrade procedurer. När du använder ett tabellalias med en temporär tabell, inkludera klausulen FOR SYSTEM_TIME mellan det temporala tabellnamnet och aliaset (se Fråga för en specifik tid med AS OF undersatsens andra exempel).

Fråga efter en viss tid med hjälp av underklausulen AS OF

Använd undersatsen AS OF för att återskapa datans tillstånd som det var vid en viss tidpunkt i det förflutna. Du kan rekonstruera data med den precision hos typen datetime2 som du angav i PERIOD kolumndefinitionerna.

Använd undersatsen AS OF med konstanta literaler eller variabler för att dynamiskt specificera tidsvillkoret. De värden du anger tolkas som UTC-tid.

Detta första exempel returnerar tabellens AS OF tillstånd dbo.Department ett specifikt datum i det förflutna.

-- 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';

I det här andra exemplet jämförs värdena mellan två tidpunkter för en delmängd rader.

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;

Använd vyer med AS OF underklausul i tidsfrågor

Vyer är användbara när du behöver komplex punkt-i-tid-analys. Ett vanligt exempel är att idag generera en affärsrapport med värdena för föregående månad.

Vanligtvis har kunderna en normaliserad databasmodell, som omfattar många tabeller med sekundärnyckelrelationer. Att ta reda på hur data från den normaliserade modellen såg ut vid en tidpunkt i det förflutna kan vara utmanande, eftersom alla tabeller ändras oberoende i sin egen rytm.

I det här fallet är det bästa alternativet att skapa en vy och tillämpa delklausulen AS OF på hela vyn. Denna metod frikopplar modelleringen av dataåtkomstlagret från punkt-i-tid-analys, eftersom SQL Server tillämpar AS OF klausulen transparent på alla temporala tabeller som deltar i vydefinitionen. Dessutom kan du kombinera temporala tabeller med icke-temporala tabeller i samma vy och AS OF tillämpas endast på temporala tabeller. Om vyn inte refererar till minst en temporal tabell misslyckas tillämpningen av temporala frågesatser på den med ett fel.

Följande exempelkod skapar en vy som kopplar samman tre temporala tabeller: Department, CompanyLocationoch 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

Du kan fråga vyn med hjälp av AS OF-underklause och ett datetime2 literal:

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

Fråga efter ändringar i specifika rader över tid

De temporala undersatserna FROM ... TO, BETWEEN ... AND, och CONTAINED IN är användbara när du behöver få alla historiska ändringar för en specifik rad i den aktuella tabellen (även kallad datagranskning).

De två första undersatserna returnerar radversioner som överlappar med en specificerad period (det vill säga de som började före den givna perioden och slutade efter den), medan CONTAINED IN endast de som existerade inom de angivna periodens gränser.

Om du söker efter endast versioner med icke-aktuella rader, fråga direkt i historiktabellen för bästa resultat. Använd ALL för att söka aktuella och historiska data utan några begränsningar.

/* 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;

För att sammanfatta hur data ser ut över en period, kombinera FOR SYSTEM_TIME BETWEEN ... AND med GROUP BY och aggregera funktioner. Använd denna teknik för beskrivande statistik och trendanalys. Den sammanfattar varje radversion som var aktiv under tidsfönstret i stället för att återskapa en viss tidpunkt.

Följande exempel beräknar beskrivande statistik för anställdas löner över ett tidsfönster, grupperat efter avdelning. Den använder den Employee systemversionerade tabellen som definieras i Temporal tables och Temporal table usage scenarios. Kolumnen AnnualSalary är ett numeriskt mått som passar väl för funktionerna AVG, MIN, MAX, och STDEV .

Klausulen FOR SYSTEM_TIME BETWEEN ... AND returnerar alla radversioner som överlappar perioden. Som ett resultat inkluderar summorna alla löner som var gällande under fönstret. När en anställds lön ändrades returnerar frågan flera versioner för den anställde, och varje version bidrar till aggregatet.

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;

Relaterat innehåll

  • Last updated on