Udostępnij za pośrednictwem

Zapytania dotyczące danych w temporalnej tabeli wersjonowanej przez system

  • Artykuł

Dotyczy: SQL Server 2016 (13.x) i nowsze wersje Azure SQL DatabaseAzure SQL Managed InstanceSQL Database w Microsoft Fabric

Jeśli chcesz uzyskać najnowszy (bieżący) stan danych w tabeli czasowej, możesz wykonać zapytanie w taki sam sposób, jak w przypadku wykonywania zapytań względem tabeli innej niż czasowa. Jeśli kolumny PERIOD nie są ukryte, ich wartości są wyświetlane w zapytaniu SELECT *. Jeśli określono kolumny PERIOD jako HIDDEN, ich wartości nie są wyświetlane w zapytaniu SELECT *. Gdy kolumny PERIOD są ukryte, należy odwołać się do kolumn PERIOD w szczególności w klauzuli SELECT, aby zwrócić wartości tych kolumn.

Aby wykonać dowolną analizę opartą na czasie, użyj nowej klauzuli FOR SYSTEM_TIME z czterema podkluczemi specyficznymi dla czasu, aby wykonywać zapytania o dane w tabelach bieżących i historycznych. Aby uzyskać więcej informacji na temat tych klauzul, zobacz Tabele czasowe i klauzula FROM oraz 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 można określić niezależnie dla każdej tabeli w zapytaniu. Można go używać wewnątrz typowych wyrażeń tabeli, funkcji wartości tabeli i procedur składowanych. W przypadku używania aliasu tabeli czasowej, klauzula FOR SYSTEM_TIME musi być uwzględniona między nazwą tabeli czasowej a aliasem (zobacz zapytanie dotyczące konkretnego czasu z użyciem podklauzuli AS OF, drugi przykład).

Wykonywanie zapytania o określony czas przy użyciu podklauzuli AS OF

Użyj podklasy AS OF, gdy musisz odtworzyć stan danych, tak jak w dowolnym momencie w przeszłości. Dane można odtworzyć z dokładnością typu datetime2, który został określony w definicjach kolumn PERIOD.

Podklauzula AS OF może być używana ze stałymi literami lub zmiennymi, dzięki czemu można dynamicznie określać warunek czasu. Podane wartości są interpretowane jako czas UTC.

Ten pierwszy przykład zwraca stan tabeli dbo.DepartmentAS OF na określoną datę w przeszłości.

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

W drugim przykładzie porównaliśmy wartości między dwoma punktami w czasie dla podzbioru wierszy.

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;

Użycie widoków z podklauzulą AS OF w zapytaniach czasowych

Korzystanie z widoków jest przydatne w scenariuszach, gdy wymagana jest złożona analiza punktu w czasie. Typowym przykładem jest generowanie raportu biznesowego dzisiaj z wartościami z poprzedniego miesiąca.

Zazwyczaj klienci mają znormalizowany model bazy danych, który obejmuje wiele tabel z relacjami kluczy obcych. Zrozumienie, jak dane z tego znormalizowanego modelu wyglądały w określonym momencie w przeszłości, może być wyzwaniem, ponieważ wszystkie tabele zmieniają się niezależnie w swoim własnym tempie.

W takim przypadku najlepszym rozwiązaniem jest utworzenie widoku i zastosowanie podklauzuli AS OF do całego widoku. Dzięki temu podejściu można oddzielić modelowanie warstwy dostępu do danych od analizy punktu w czasie, ponieważ serwer SQL Server stosuje klauzulę AS OF przezroczysto do wszystkich tabel czasowych, które uczestniczą w definicji widoku. Ponadto można łączyć tabele czasowe z nieczasowymi w tym samym widoku, a AS OF jest stosowany tylko do tabel czasowych. Jeśli widok nie odwołuje się do co najmniej jednej tabeli czasowej, stosowanie do niej klauzul zapytań czasowych kończy się niepowodzeniem z powodu błędu.

Poniższy przykładowy kod tworzy widok, który łączy trzy tabele czasowe: Department, CompanyLocationi 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

Możesz wykonać zapytanie dotyczące widoku przy użyciu podklasy AS OF i literału datetime2:

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

Zapytanie o zmiany w określonych wierszach w miarę upływu czasu

Podklasy czasowe FROM ... TO, BETWEEN ... AND i CONTAINED IN są przydatne, gdy konieczne jest pobranie wszystkich zmian historycznych dla określonego wiersza w bieżącej tabeli (nazywanej również inspekcją danych).

Pierwsze dwie podklasy zwracają wersje wierszy, które nakładają się na określony okres (czyli te, które rozpoczęły się przed danym okresem i zakończyły się po nim), podczas gdy CONTAINED IN zwraca tylko te, które istniały w określonych granicach okresu.

Jeśli szukasz tylko nie bieżących wersji wierszy, należy wykonać zapytanie bezpośrednio w tabeli historii, aby uzyskać najlepszą wydajność zapytań. Użyj ALL, jeśli musisz wykonywać zapytania dotyczące bieżących i historycznych danych bez żadnych ograniczeń.

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

Powiązana zawartość