Sdílet prostřednictvím

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

  • Článek

platí pro: SQL Server 2016 (13.x) a novější verze Azure SQL DatabaseAzure SQL Managed InstanceSQL Database v Microsoft Fabric

Pokud chcete získat nejnovější (aktuální) stav dat v dočasné tabulce, můžete dotazovat stejným způsobem jako dotazování na tabulku, která není dočasná. Pokud PERIOD sloupce nejsou skryté, jejich hodnoty se zobrazí v SELECT * dotazu. Pokud jste zadali PERIOD sloupce jako HIDDEN, jejich hodnoty se v dotazu SELECT * nezobrazí. Pokud jsou sloupce PERIOD skryté, musíte odkazovat na sloupce PERIOD konkrétně v klauzuli SELECT, aby se vrátily hodnoty těchto sloupců.

Pokud chcete provést jakýkoli typ analýzy založené na čase, použijte novou klauzuli FOR SYSTEM_TIME se čtyřmi dílčími částmi specifickými pro časové období k dotazování dat napříč aktuálními tabulkami a tabulkami historie. 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

FOR SYSTEM_TIME lze zadat nezávisle pro každou tabulku v dotazu. Dá se použít uvnitř běžných tabulkových výrazů, tabulkových funkcí a uložených procedur. Při použití aliasu tabulky s časovou tabulkou musí být klauzule FOR SYSTEM_TIME zahrnuta mezi názvem časové tabulky a aliasem (viz Dotaz na konkrétní čas pomocí AS OF podklauzule druhého příkladu).

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

Použijte podčlánek AS OF, když potřebujete rekonstruovat stav dat tak, jak byly v konkrétní době v minulosti. Data můžete rekonstruovat s přesností typu datetime2, který byl zadán v definicích sloupců PERIOD.

AS OF subclause lze použít s konstantními literály nebo s proměnnými, abyste mohli dynamicky určit časovou podmínku. Zadané hodnoty se interpretují jako čas UTC.

Tento první příklad vrátí stav tabulky dbo.Department k určitému datu 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 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

Použití zobrazení je užitečné ve scénářích, kdy je nutná složitá analýza k určitému bodu v č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 vypadala data z tohoto normalizovaného modelu 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 a každá svým vlastním tempem.

V tomto případě je nejlepší volbou vytvořit zobrazení a použít AS OF subclause na celé zobrazení. Tento přístup umožňuje oddělit modelování vrstvy přístupu k datům od analýzy k určitému bodu v čase, protože SQL Server používá AS OF klauzuli transparentně u všech časových tabulek, které se účastní definice zobrazení. 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 JOIN [dbo].[LocationDepartments]
    ON [CompanyLocation].LocID = LocationDepartments.LocID
LEFT JOIN [dbo].[Department]
    ON LocationDepartments.DeptID = [Department].DeptID;
GO

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

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

Dočasné dílčí části FROM ... TO, BETWEEN ... AND a CONTAINED IN jsou užitečné, když potřebujete získat všechny historické změny pro konkrétní řádek v aktuální tabulce (označované také jako audit dat).

První dvě dílčí ustanovení vrací verze řádků, které se překrývají se zadaným obdobím (tj. ty, které začínají před daným obdobím a končí po něm), zatímco CONTAINED IN vrátí pouze ty, které existují v zadaných hranicích období.

Pokud hledáte pouze nesoučasné verze řádků, měli byste se dotazovat přímo na tabulku historie, abyste zajistili nejlepší výkon dotazů. Pokud potřebujete dotazovat aktuální a historická data bez jakýchkoli omezení, použijte ALL.

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

Související obsah