Daten in einer systemversionierten temporalen Tabelle abfragen

Gilt für: SQL Server 2016 (13.x) und spätere Versionen Azure SQL-DatenbankAzure SQL Managed InstanceSQL database in Microsoft Fabric

Um den neuesten (aktuellen) Zustand der Daten in einer temporalen Tabelle zu erhalten, fragen Sie diese auf die gleiche Weise wie eine nicht-temporale Tabelle ab. Wenn die PERIOD-Spalten nicht ausgeblendet sind, werden ihre Werte in einer SELECT *-Abfrage angezeigt. Wenn du PERIOD-Spalten als SELECT * angibst, erscheinen ihre Werte nicht in einer HIDDEN-Abfrage. Wenn die Spalten PERIOD verborgen sind, verweise sie speziell in der SELECT Klausel, um ihre Werte zurückzugeben.

Um zeitbasierte Analysen durchzuführen, verwenden Sie die FOR SYSTEM_TIME Klausel mit vier zeitspezifischen Unterklauseln, um Daten über die aktuellen und historischen Tabellen abzufragen. Weitere Informationen zu diesen Klauseln finden Sie unter Temporale Tabellen und FROM-Klausel 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

Man kann unabhängig für jede Tabelle in einer Abfrage angeben FOR SYSTEM_TIME . Verwenden Sie es in gängigen Tabellenausdrücken, tabellenwertigen Funktionen und gespeicherten Prozeduren. Wenn Sie ein Tabellenalias mit einer temporalen Tabelle verwenden, fügen Sie die FOR SYSTEM_TIME Klausel zwischen dem zeitlichen Tabellennamen und dem Alias ein (siehe Abfrage für eine bestimmte Zeit mit dem zweiten Beispiel der AS OF Unterklausel ).

Abfrage für einen bestimmten Zeitpunkt mithilfe der AS OF-Unterklausel

Verwenden Sie die AS OF Unterklausel, um den Zustand der Daten so zu rekonstruieren, wie er zu einem bestimmten Zeitpunkt in der Vergangenheit war. Du kannst die Daten mit der Genauigkeit des Datetime2-Typs rekonstruieren, den du in PERIOD den Spaltendefinitionen angegeben hast.

Verwenden Sie die AS OF Unterklausel mit konstanten Literalen oder Variablen, um die Zeitbedingung dynamisch zu spezifizieren. Die von dir angegebenen Werte werden als UTC-Zeit interpretiert.

Dieses erste Beispiel gibt den Zustand der Tabelle dbo.DepartmentAS OF an einem bestimmten Datum in der Vergangenheit zurück.

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

Dieses zweite Beispiel vergleicht die Werte zwischen zwei Zeitpunkten für eine Teilmenge von Zeilen.

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;

Verwenden Sie Ansichten mit der AS OF-Unterklausel in temporalen Abfragen

Ansichten sind nützlich, wenn man komplexe Punkt-in-Zeit-Analysen benötigt. Ein häufiges Beispiel ist die Erstellung eines Geschäftsberichts heute mit den Werten des vergangenen Monats.

In der Regel besitzen Kund*innen ein normalisiertes Datenbankmodell, das viele Tabellen mit Fremdschlüsselbeziehungen enthält. Herauszufinden, wie Daten aus diesem normalisierten Modell zu einem Zeitpunkt in der Vergangenheit aussahen, kann schwierig sein, da sich alle Tabellen unabhängig voneinander in ihrem eigenen Rhythmus ändern.

In diesem Fall besteht die beste Option darin, eine Ansicht zu erstellen und die AS OF-Unterklausel auf die komplette Ansicht anzuwenden. Dieser Ansatz entkoppelt die Modellierung der Datenzugriffsschicht von der Punkt-in-Zeit-Analyse, da SQL Server die Klausel AS OF transparent auf alle temporalen Tabellen anwendet, die an der View-Definition teilnehmen. Darüber hinaus können Sie temporale Tabellen in derselben Ansicht mit nicht temporalen Tabellen kombinieren, und AS OF wird nur auf die temporalen Tabellen angewendet. Wenn die Ansicht nicht mindestens auf eine temporale Tabelle verweist, tritt bei der Anwendung von temporalen Abfrageklauseln ein Fehler auf.

Der folgende Beispielcode erstellt eine Ansicht, in der drei temporale Tabellen verknüpft werden: Department, CompanyLocation und 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

Sie können die Ansicht mithilfe der AS OF-Unterklausel und eines datetime2-Literals abfragen:

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

Abfragen von Änderungen an bestimmten Zeilen im Zeitverlauf

Die temporalen Unterklauseln FROM ... TO, BETWEEN ... AND und CONTAINED IN sind nützlich, wenn Sie alle historischen Änderungen für eine bestimmte Zeile in der aktuellen Tabelle abrufen müssen (auch als Datenüberprüfung bekannt).

Die ersten beiden Unterklauseln geben Zeilenversionen zurück, die sich mit einer bestimmten Periode überlappen (also solche, die vor der gegebenen Periode begannen und danach endeten), während CONTAINED IN sie nur solche zurückgeben, die innerhalb der angegebenen Periodengrenzen existierten.

Wenn Sie nur nach nicht aktuellen Zeilenversionen suchen, fragen Sie für die beste Abfrageleistung die Verlaufstabelle direkt ab. Verwenden Sie ALL es, um aktuelle und historische Daten ohne Einschränkungen abzufragen.

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

Um zusammenzufassen, wie Daten über einen Zeitraum hinweg aussehen, kombinieren FOR SYSTEM_TIME BETWEEN ... AND Sie mit GROUP BY und aggregieren Sie Funktionen. Verwenden Sie diese Technik für beschreibende Statistiken und Trendanalysen. Es fasst jede Zeilenversion zusammen, die innerhalb des Zeitfensters aktiv war, anstatt einen einzelnen Zeitpunkt wiederherzustellen.

Das folgende Beispiel berechnet beschreibende Statistiken für Mitarbeitergehälter über ein Zeitfenster, gruppiert nach Abteilung. Es verwendet die Employee systemversionierte Tabelle, die in Temporal Tables und Temporal Table Use Scenarios definiert ist. Die Spalte AnnualSalary ist ein numerisches Maß, das gut zu den Funktionen , MIN, MAX, und STDEV passtAVG.

Die Klausel FOR SYSTEM_TIME BETWEEN ... AND gibt alle Zeilenversionen zurück, die den Zeitraum überlappen. Daher enthalten die Aggregatwerte alle Gehälter, die innerhalb des Zeitraums galten. Wenn sich das Gehalt eines Mitarbeiters ändert, liefert die Abfrage mehrere Versionen für diesen Mitarbeiter, und jede Version trägt zu den Aggregaten bei.

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;

Verwandte Inhalte

  • Last updated on