Zapytania dotyczące danych w temporalnej tabeli wersjonowanej przez system

Dotyczy do: SQL Server 2016 (13.x) i nowsze wersje Azure SQL DatabaseAzure SQL Managed InstanceSQL database in Microsoft Fabric

Aby uzyskać najnowszy (aktualny) stan danych w tabeli czasowej, zapytaj ją w ten sam sposób jak w tabeli nietemporalnej. Jeśli kolumny PERIOD nie są ukryte, ich wartości są wyświetlane w zapytaniu SELECT *. Jeśli określisz PERIOD kolumny jako HIDDEN, ich wartości nie pojawiają się w zapytaniu SELECT * . Gdy kolumny PERIOD są ukryte, odwołuj się do nich konkretnie w klauzuli SELECT , aby zwrócić ich wartości.

Aby przeprowadzić analizę czasową, użyj klauzuli FOR SYSTEM_TIME z czterema podklauzulami specyficznymi dla czasu do zapytań danych 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

Możesz określić FOR SYSTEM_TIME niezależnie dla każdej tabeli w zapytaniu. Używaj go w typowych wyrażeniach tabelowych, funkcjach tabelowych oraz procedurach przechowywanych. Gdy używasz aliasu tabeli z tabelą temporalną, umieść klauzulę FOR SYSTEM_TIME między nazwą tabeli temporalnej a aliasem (zobacz Wykonywanie zapytania dla określonego momentu przy użyciu podklauzuli AS OF, drugi przykład).

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

Użyj podklauzuli AS OF , aby odtworzyć stan danych takim, jakim był w danym konkretnym czasie w przeszłości. Możesz odtworzyć dane z precyzją typu datetime2 , który określiłeś w PERIOD definicjach kolumn.

Użyj podzdania AS OF ze stałymi literalami lub zmiennymi, aby dynamicznie określić warunki czasowe. Wartości, które podajesz, interpretowane są jako czas UTC.

Ten pierwszy przykład zwraca stan tabeli dbo.DepartmentAS OF na konkretną 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 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;

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

Widoki są przydatne, gdy potrzebujesz złożonej analizy stanu na dany moment. Częstym przykładem jest wygenerowanie raportu biznesowego dzisiaj z wartościami za poprzedni miesiąc.

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

W takim przypadku najlepszym rozwiązaniem jest utworzenie widoku i zastosowanie podklauzuli AS OF do całego widoku. To podejście oddziela modelowanie warstwy dostępu do danych od analizy punktowej w czasie, ponieważ SQL Server stosuje klauzulę AS OF transparentnie do wszystkich tabel czasowych uczestniczących 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 OUTER JOIN [dbo].[LocationDepartments]
        ON [CompanyLocation].LocID = LocationDepartments.LocID
    LEFT OUTER 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:

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

Podklauzule czasowe FROM ... TO, BETWEEN ... AND i CONTAINED IN są przydatne, gdy trzeba pobrać wszystkie historyczne zmiany dla określonego wiersza w bieżącej tabeli (co jest również nazywane audytem danych).

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

Jeśli szukasz tylko niebieżących wersji wierszy, odpytuj bezpośrednio tabelę historii, aby uzyskać najlepszą wydajność zapytań. Użyj ALL, aby przeszukiwać bieżące i historyczne dane 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;

Aby podsumować, jak wyglądają dane w danym okresie, połącz FOR SYSTEM_TIME BETWEEN ... AND z GROUP BY oraz funkcjami agregującymi. Stosuj tę technikę do statystyki opisowej i analizy trendów. Agreguje wszystkie wersje wierszy, które były aktywne w danym przedziale czasowym, zamiast odtwarzać stan z jednego punktu w czasie.

Poniższy przykład oblicza statystyki opisowe wynagrodzeń pracowników w danym oknie czasowym, w podziale na działy. Wykorzystuje tabelę Employee wersjonowaną przez system, zdefiniowaną w Tabele czasowe i Scenariusze użycia tabel czasowych. Kolumna AnnualSalary jest miarą numeryczną dobrze nadającą się do funkcji AVG, MIN, MAX oraz STDEV.

Klauzula FOR SYSTEM_TIME BETWEEN ... AND zwraca każdą wersję wiersza, która obejmuje dany okres. W rezultacie sumy obejmują każdą pensję obowiązującą w tym okresie. Gdy zmienia się wynagrodzenie pracownika, zapytanie zwraca wiele wersji dla tego pracownika, a każda z nich przyczynia się do agregatu.

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;

Treści powiązane

  • Last updated on