Delen via

Query uitvoeren op gegevens in een systeem-geregistreerde temporele tabel

  • Artikel

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

Wanneer u de meest recente (huidige) status van gegevens in een tijdelijke tabel wilt ophalen, kunt u op dezelfde manier query's uitvoeren als bij een niet-tijdelijke tabel. Als de PERIOD kolommen niet verborgen zijn, worden de waarden weergegeven in een SELECT * query. Als u PERIOD kolommen hebt opgegeven als HIDDEN, worden de bijbehorende waarden niet weergegeven in een SELECT * query. Wanneer de PERIOD kolommen zijn verborgen, moet u specifiek verwijzen naar de PERIOD kolommen in de SELECT clausule om de waarden voor deze kolommen te retourneren.

Als u elk type tijdgebaseerde analyse wilt uitvoeren, gebruikt u de nieuwe FOR SYSTEM_TIME component met vier tijdelijke subclauses om query's uit te voeren op gegevens 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

FOR SYSTEM_TIME kan onafhankelijk worden opgegeven voor elke tabel in een query. Deze kan worden gebruikt in algemene tabelexpressies, tabelwaardefuncties en opgeslagen procedures. Wanneer u een tabelalias met een tijdelijke tabel gebruikt, moet de FOR SYSTEM_TIME component worden opgenomen tussen de tijdelijke tabelnaam en de alias (zie Query voor een specifieke tijd met behulp van de AS OF subclause tweede voorbeeld).

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

Gebruik de AS OF subclause wanneer u de status van de gegevens wilt reconstrueren, zoals op een bepaald moment in het verleden. U kunt de gegevens reconstrueren met de precisie van het datum/tijd2-type dat is opgegeven in PERIOD kolomdefinities.

De AS OF subclause kan worden gebruikt met constante letterlijke waarden of met variabelen, zodat u dynamisch de tijdvoorwaarde kunt opgeven. De opgegeven waarden worden geïnterpreteerd als UTC-tijd.

In dit eerste voorbeeld wordt de status van de dbo.Department-tabel AS OF op een specifieke datum in het verleden geretourneerd.

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

Het gebruik van weergaven is handig in scenario's waarin complexe analyse van een bepaald tijdstip is vereist. Een veelvoorkomend voorbeeld is het genereren van een bedrijfsrapport vandaag met de waarden voor de vorige maand.

Normaal gesproken hebben klanten een genormaliseerd databasemodel, dat veel tabellen met relaties met foreign keys bevat. Uitzoeken hoe gegevens uit dat genormaliseerde model naar een bepaald punt in het verleden hebben gekeken, kunnen lastig zijn, omdat alle tabellen onafhankelijk van elkaar veranderen.

In dit geval is de beste optie om een weergave te maken en de AS OF subclause toe te passen op de hele weergave. Met deze methode kunt u modellering van de gegevenstoegangslaag loskoppelen van een bepaald tijdstipanalyse, omdat SQL Server transparant AS OF component toepast op alle tijdelijke 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 JOIN [dbo].[LocationDepartments]
    ON [CompanyLocation].LocID = LocationDepartments.LocID
LEFT 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:

/* Querying 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 tijdelijke subclauses FROM ... TO, BETWEEN ... AND en CONTAINED IN zijn handig wanneer u alle historische wijzigingen voor een specifieke rij in de huidige tabel (ook wel een gegevenscontrole genoemd) moet ophalen.

De eerste twee subclauses retourneren rijversies die overlappen met een opgegeven periode (die vóór de opgegeven periode zijn begonnen en daarna eindigen), terwijl CONTAINED IN alleen de versies retourneert die binnen de opgegeven periodegrenzen bestonden.

Als u alleen zoekt naar niet-huidige rijversies, moet u de geschiedenistabel rechtstreeks doorzoeken voor de beste queryprestaties. Gebruik ALL wanneer u zonder beperkingen query's moet uitvoeren op huidige en historische gegevens.

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

Verwante inhoud