Dotazování na data v systémově verzované časové tabulce

Platí na: SQL Server 2016 (13.x) a novější verze Azure SQL DatabaseAzure SQL Managed InstanceSQL database in Microsoft Fabric

Pro získání nejnovějšího (aktuálního) stavu dat v časové tabulce je dotazujte stejným způsobem jako v netempální tabulce. Pokud PERIOD sloupce nejsou skryté, jejich hodnoty se zobrazí v SELECT * dotazu. Pokud specifikujete PERIOD sloupce jako HIDDEN, jejich hodnoty se v dotazu SELECT * neobjeví. Když jsou sloupce PERIOD skryté, odkazujte na ně konkrétně v klauzuli SELECT a vraťte jejich hodnoty.

Pro provedení analýzy založené na čase použijte klauzuli FOR SYSTEM_TIME se čtyřmi časově specifickými podklauzulemi k dotazování dat napříč tabulkami aktuálních a historických. Další informace o těchto výrazech naleznete v tématu Temporální tabulky a výraz FROM 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

Každou tabulku v dotazu můžete FOR SYSTEM_TIME specifikovat samostatně. Použijte ho v běžných tabulkových výrazech, tabulkových funkcích a uložených procedurách. Když použijete alias tabulky s časovou tabulkou, zahrňte klauzuli FOR SYSTEM_TIME mezi názvem temporal table a aliasem (viz Dotaz pro konkrétní čas pomocí podklauzule AS OF v druhém příkladu).

Dotaz na konkrétní čas pomocí podkladové klauzule AS OF

Použijte podklauzuli AS OF k rekonstrukci stavu dat tak, jak byla v jakémkoli konkrétním okamžiku v minulosti. Data můžete rekonstruovat s přesností typu datetime2 , který jste specifikoval ve PERIOD sloupcových definicích.

Použijte podklauzuli AS OF s konstantními literály nebo proměnnými k dynamickému určení časové podmínky. Hodnoty, které uvádíte, jsou interpretovány jako UTC čas.

Tento první příklad vrací stav tabulky dbo.DepartmentAS OF na konkrétní datum v minulosti.

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

Tento druhý příklad porovnává hodnoty mezi dvěma body v čase pro podmnožinu řádků.

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;

Použijte pohledy s podčástí AS OF v temporálních dotazech

Pohledy jsou užitečné, když potřebujete složitou analýzu v daném čase. Běžným příkladem je vytvoření obchodní zprávy dnes s hodnotami za předchozí měsíc.

Zákazníci mají obvykle normalizovaný databázový model, který zahrnuje mnoho tabulek s relacemi cizích klíčů. Zjistit, jak data z daného normalizovaného modelu vypadala v určitém okamžiku v minulosti, může být náročné, protože všechny tabulky se mění nezávisle na svém rytmu.

V tomto případě je nejlepší volbou vytvořit zobrazení a použít AS OF subclause na celé zobrazení. Tento přístup odděluje modelování vrstvy přístupu k datům od analýzy v daném čase, protože SQL Server aplikuje klauzuli AS OF transparentně na všechny časové tabulky, které se účastní definice pohledu. Kromě toho můžete v jednom zobrazení zkombinovat dočasné tabulky s ne dočasnými tabulkami a AS OF se použijí jenom na dočasné tabulky. Pokud zobrazení neodkazuje alespoň na jednu dočasnou tabulku, použití dočasných klauzulí pro dotazování selže s chybou.

Následující ukázkový kód vytvoří zobrazení, které spojí tři dočasné tabulky: Department, CompanyLocationa 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

Dotaz na zobrazení můžete provést pomocí podklauzule AS OF a literálu datetime2 .

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

Dotaz na změny v konkrétních řádcích v průběhu času

Časové klauzule FROM ... TO, BETWEEN ... AND a CONTAINED IN jsou užitečné, když potřebujete získat všechny historické změny u konkrétního řádku v aktuální tabulce (což se také označuje jako audit dat).

První dvě podklauzule vracejí verze řádků, které se překrývají s daným obdobím (tedy ty, které začaly před daným obdobím a skončily po něm), zatímco CONTAINED IN vrací pouze ty, které existovaly v rámci určených hranic období.

Pokud hledáte pouze neaktuální verze řádků, přímo se zeptejte na tabulku historie pro nejlepší výkon dotazu. Používejte ALL dotazy na aktuální i historická data bez jakýchkoli omezení.

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

Pro shrnutí, jak data vypadají v průběhu období, kombinujte FOR SYSTEM_TIME BETWEEN ... AND s GROUP BY a agregujte funkce. Tuto techniku použijte pro popisnou statistiku a analýzu trendů. Sloučí všechny verze řádku, které byly aktivní v daném časovém okně, místo aby zrekonstruoval jeden konkrétní časový okamžik.

Následující příklad počítá popisné statistiky platů zaměstnanců v časovém okně, seskupené podle oddělení. Používá systémově verzizovanou tabulku definovanou Employee ve scénářích použití Temporal Tables a Temporal Table. Sloupec AnnualSalary je numerická míra dobře přizpůsobená funkcím AVG, MIN, MAX, a STDEV .

Klauzule FOR SYSTEM_TIME BETWEEN ... AND vrací každou verzi řádku, která se překrývá s daným obdobím. Výsledkem je, že souhrny zahrnují všechny platy, které byly platné během tohoto období. Když se změní plat zaměstnance, dotaz vrátí více verzí pro tohoto zaměstnance a každá verze přispívá do agregátů.

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;

Související obsah

  • Last updated on