Dela via

Fråga efter data i en systemversionsbaserad temporal tabell

  • Artikel

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

När du vill hämta det senaste (aktuella) datatillståndet i en temporal tabell kan du fråga på samma sätt som du kör frågor mot en icke-temporal tabell. Om de PERIOD kolumnerna inte är dolda visas deras värden i en SELECT * fråga. Om du har angett PERIOD kolumner som HIDDENvisas inte deras värden i en SELECT * fråga. När de PERIOD kolumnerna är dolda måste du referera till de PERIOD kolumnerna specifikt i SELECT-satsen för att returnera värdena för dessa kolumner.

Om du vill utföra alla typer av tidsbaserad analys använder du den nya FOR SYSTEM_TIME-satsen med fyra tidsspecifika underklisklar för att fråga efter data i de aktuella tabellerna och historiktabellerna. 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

FOR SYSTEM_TIME kan anges separat för varje tabell i en fråga. Den kan användas i vanliga tabelluttryck, tabellvärdesfunktioner och lagrade procedurer. När du använder ett tabellalias med en temporal tabell måste FOR SYSTEM_TIME-satsen inkluderas mellan det temporala tabellnamnet och aliaset (se Fråga för en specifik tidpunkt med AS OF-underklausulen andra exemplet).

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

Använd AS OF-underpunkten när du behöver rekonstruera datatillståndet som det var vid en viss tidpunkt i det förflutna. Du kan rekonstruera data med precisionen av datetime2-typen som angavs i PERIOD-kolumndefinitionerna.

AS OF-underklausulen kan användas med konstanter eller variabler, så att du kan ange tidsvillkoret dynamiskt. De angivna värdena tolkas som UTC-tid.

Det här första exemplet returnerar tillståndet för dbo.Department-tabellen AS OF på 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 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

Att använda vyer är användbart i scenarier när komplex analys av tidpunkt krävs. Ett vanligt exempel är att generera en affärsrapport idag med värdena för föregående månad.

Vanligtvis har kunderna en normaliserad databasmodell, som omfattar många tabeller med sekundärnyckelrelationer. Det kan vara svårt att ta reda på hur data från den normaliserade modellen tittade på en tidigare tidpunkt, eftersom alla tabeller ändras oberoende av varandra i sin egen takt.

I det här fallet är det bästa alternativet att skapa en vy och tillämpa delklausulen AS OF på hela vyn. Med den här metoden kan du frikoppla modellering av dataåtkomstskiktet från punkt-i-tid-analys, eftersom SQL Server tillämpar AS OF-satsen transparent för 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 JOIN [dbo].[LocationDepartments]
    ON [CompanyLocation].LocID = LocationDepartments.LocID
LEFT JOIN [dbo].[Department]
    ON LocationDepartments.DeptID = [Department].DeptID;
GO

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

/* Querying 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 underclausesna FROM ... TO, BETWEEN ... AND och CONTAINED IN är användbara när du behöver hämta alla historiska ändringar för en specifik rad i den aktuella tabellen (kallas även för en datagranskning).

De första två underclauses returnerar radversioner som överlappar en angiven period (det vill säga de som startade före den angivna perioden och avslutades efter den), medan CONTAINED IN endast returnerar de som fanns inom angivna periodgränser.

Om du bara söker efter icke-aktuella radversioner bör du fråga historiktabellen direkt för bästa frågeprestanda. Använd ALL när du behöver köra frågor mot 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;

Relaterat innehåll