Query uitvoeren op gegevens in een systeem-geregistreerde temporele tabel

Van toepassing op: SQL Server 2016 (13.x) en latere versies Azure SQL DatabaseAzure SQL Managed InstanceSQL database in Microsoft Fabric

Om de meest recente (huidige) status van gegevens in een temporele tabel te krijgen, raadpleeg deze op dezelfde manier als een niet-temporele tabel. Als de PERIOD kolommen niet verborgen zijn, worden de waarden weergegeven in een SELECT * query. Als je kolommen specificeert PERIOD als HIDDEN, verschijnen hun waarden niet in een SELECT * query. Wanneer de PERIOD kolommen verborgen zijn, verwijs je er specifiek naar in de SELECT clausule om hun waarden terug te geven.

Om tijdgebaseerde analyse uit te voeren, gebruik je de FOR SYSTEM_TIME clausule met vier temporeel-specifieke subclausules om gegevens te bevragen in de huidige en geschiedenistabellen. Zie voor meer informatie over deze clausules Temporale tabellen en FROM-clausule 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

Je kunt onafhankelijk voor elke tabel in een query specificeren FOR SYSTEM_TIME . Gebruik het binnen gemeenschappelijke tabelexpressies, tabelwaardige functies en opgeslagen procedures. Wanneer je een tabelalias met een temporele tabel gebruikt, voeg dan de FOR SYSTEM_TIME clausule tussen de temporele tabelnaam en de alias op (zie Query voor een specifieke tijd met het tweede voorbeeld van de AS OF subclausule ).

Een query uitvoeren op een bepaald tijdstip met behulp van de AS OF subclause

Gebruik de AS OF subclausule om de toestand van data te reconstrueren zoals die op een bepaald moment in het verleden was. Je kunt de data reconstrueren met de precisie van het datetime2-type dat je in PERIOD kolomdefinities hebt opgegeven.

Gebruik de AS OF subclausule met constante literalen of variabelen om de tijdvoorwaarde dynamisch te specificeren. De waarden die je opgeeft worden geïnterpreteerd als UTC-tijd.

Dit eerste voorbeeld geeft de status van de dbo.Department tabel AS OF terug op een specifieke datum in het verleden.

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

In dit tweede voorbeeld worden de waarden tussen twee punten in de tijd vergeleken voor een subset rijen.

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;

Weergaven gebruiken met AS OF subclause in tijdelijke query's

Views zijn nuttig wanneer je complexe point-in-time analyse nodig hebt. Een veelvoorkomend voorbeeld is het genereren van vandaag een bedrijfsrapport met de waarden van de voorgaande maand.

Normaal gesproken hebben klanten een genormaliseerd databasemodel, dat veel tabellen met relaties met vreemde sleutels bevat. Achterhalen hoe data uit dat genormaliseerde model eruit zag op een punt in het verleden kan lastig zijn, omdat alle tabellen onafhankelijk van elkaar veranderen in hun eigen ritme.

In dit geval is de beste optie om een weergave te maken en de AS OF subclause toe te passen op de hele weergave. Deze aanpak scheidt de modellering van de data-toegangslaag van point-in-time analyse, omdat SQL Server de AS OF clausule transparant toepast op alle temporele tabellen die deelnemen aan de weergavedefinitie. Bovendien kunt u tijdelijke tabellen combineren met niet-tijdelijke tabellen in dezelfde weergave en AS OF alleen wordt toegepast op tijdelijke tabellen. Als de weergave niet verwijst naar ten minste één tijdelijke tabel, mislukt het toepassen van tijdelijke queryclausules erop met een fout.

Met de volgende voorbeeldcode wordt een weergave gemaakt waarmee drie tijdelijke tabellen worden samengevoegd: Department, CompanyLocationen 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

U kunt een query uitvoeren op de weergave met behulp van de AS OF subclause en een datum/tijd2 letterlijke waarde:

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

Query uitvoeren op wijzigingen in specifieke rijen in de loop van de tijd

De temporele subclausules FROM ... TO, BETWEEN ... AND, en CONTAINED IN zijn nuttig wanneer je alle historische wijzigingen voor een specifieke rij in de huidige tabel moet krijgen (ook wel een data-audit genoemd).

De eerste twee subclausules geven rijversies terug die overlappen met een bepaalde periode (dat wil zeggen die vóór de gegeven periode begonnen en daarna eindigden), terwijl CONTAINED IN alleen die versies teruggeven die binnen de gespecificeerde periodegrenzen bestonden.

Als je alleen zoekt naar niet-actuele rijversies, raadpleeg dan direct de geschiedenistabel voor de beste queryprestaties. Gebruik ALL het om huidige en historische gegevens zonder beperkingen op te vragen.

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

Om samen te vatten hoe data eruitziet over een periode, combineer FOR SYSTEM_TIME BETWEEN ... AND je met GROUP BY en aggregeer je functies. Gebruik deze techniek voor beschrijvende statistiek en trendanalyse. Het bundelt alle rijversies die binnen het tijdvenster actief waren, in plaats van één specifiek tijdstip te reconstrueren.

Het volgende voorbeeld berekent beschrijvende statistieken voor salarissen van werknemers over een tijdsvenster, gegroepeerd per afdeling. Het gebruikt de Employee door het systeem geversioneerde tabel die is gedefinieerd in Temporale tabellen en Gebruiksscenario's voor temporele tabellen. De AnnualSalary kolom is een numerieke maat die goed geschikt is voor de AVG, MIN, MAX, en STDEV functies.

De FOR SYSTEM_TIME BETWEEN ... AND clausule geeft alle rijversies terug die met de periode overlappen. Als gevolg hiervan bevatten de aggregaten elk salaris dat tijdens de periode van kracht was. Wanneer het salaris van een werknemer verandert, levert de query meerdere versies voor die werknemer terug, en elke versie draagt bij aan de aggregaten.

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;

Verwante onderwerpen

  • Last updated on