Interroger des données dans une table temporelle versionnée par le système

S’applique à : SQL Server 2016 (13.x) et versions ultérieures Azure SQL DatabaseAzure SQL Managed InstanceSQL database in Microsoft Fabric

Pour obtenir le dernier état (actuel) des données dans une table temporelle, interrogez-la de la même manière qu’une table non temporelle. Si les colonnes PERIOD ne sont pas masquées, leurs valeurs apparaissent dans une requête SELECT *. Si vous spécifiez PERIOD les colonnes comme HIDDEN, leurs valeurs n’apparaissent pas dans une SELECT * requête. Lorsque les PERIOD colonnes sont cachées, référenez-les spécifiquement dans la SELECT clause pour retourner leurs valeurs.

Pour effectuer une analyse basée sur le temps, utilisez la FOR SYSTEM_TIME clause avec quatre sous-clauses spécifiques au temps pour interroger les données à travers les tableaux courant et historique. Pour plus d’informations sur ces clauses, consultez Tables temporelles et clause FROM avec JOIN, APPLY et 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

Vous pouvez spécifier FOR SYSTEM_TIME indépendamment chaque table dans une requête. Utilisez-le dans des expressions de table courantes, des fonctions à valeurs de table et des procédures stockées. Lorsque vous utilisez un alias de table avec une table temporelle, incluez la FOR SYSTEM_TIME clause entre le nom de la table temporelle et l’alias (voir Requête pour un temps spécifique en utilisant le AS OF deuxième exemple de la sous-clause ).

Interroger un point précis dans le temps en utilisant la sous-clause AS OF

Utilisez la AS OF sous-clause pour reconstituer l’état des données tel qu’elles étaient à un moment précis dans le passé. Vous pouvez reconstruire les données avec la précision du type datetime2 que vous avez spécifié dans PERIOD les définitions de colonnes.

Utilisez la AS OF sous-clause avec des littéraux ou variables constants pour spécifier dynamiquement la condition temporelle. Les valeurs que vous fournissez sont interprétées comme l’heure UTC.

Ce premier exemple renvoie l’état du dbo.Department tableau AS OF à une date précise du passé.

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

Ce second exemple compare les valeurs entre deux points dans le temps pour un sous-ensemble de lignes.

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;

Utiliser des vues avec la sous-clause AS OF dans des requêtes temporelles

Les vues sont utiles lorsque vous avez besoin d’une analyse complexe au moment donné. Un exemple courant est la génération aujourd’hui d’un rapport commercial avec les valeurs du mois précédent.

En règle générale, les clients utilisent un modèle de base de données normalisé qui implique de nombreuses tables avec des relations de clés étrangères. Comprendre à quoi se présentaient les données de ce modèle normalisé à un moment donné dans le passé peut être difficile, car toutes les tables changent indépendamment selon leur propre cadence.

Dans ce cas, la meilleure solution consiste à créer une vue et à appliquer la sous-clause AS OF à toute la vue. Cette approche découple la modélisation de la couche d’accès aux données de l’analyse au moment donné, car SQL Server applique la AS OF clause de manière transparente à toutes les tables temporelles participant à la définition de la vue. En outre, vous pouvez combiner des tables temporelles et non temporelles dans la même vue, et AS OF est appliqué seulement aux tables temporelles. Si la vue ne référence pas au moins une table temporelle, l’application de clauses de requêtes temporelles échoue avec une erreur.

L’exemple de code suivant crée une vue qui joint trois tables temporelles : Department, CompanyLocation et 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

Vous pouvez interroger la vue à l’aide de la sous-clause AS OF et d’un littéral datetime2 :

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

Rechercher des modifications sur des lignes spécifiques dans le temps

Les sous-clauses FROM ... TOtemporelles , BETWEEN ... AND, et CONTAINED IN sont utiles lorsque vous devez obtenir toutes les modifications historiques d’une ligne spécifique dans la table courante (également appelé audit de données).

Les deux premières sous-clauses retournent les versions de la ligne qui se chevauchent avec une période spécifiée (c’est-à-dire celles qui ont commencé avant la période donnée et se sont terminées après), tandis que CONTAINED IN ne retournent que celles qui existaient dans les limites de la période spécifiée.

Si vous recherchez uniquement des versions de lignes non récentes, interrogez directement la table d’historique pour connaître les meilleures performances de requête. À utiliser ALL pour interroger les données actuelles et historiques sans aucune restriction.

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

Pour résumer l’apparence des données sur une période, combinez FOR SYSTEM_TIME BETWEEN ... AND et GROUP BY agrégez des fonctions. Utilisez cette technique pour les statistiques descriptives et l’analyse des tendances. Il regroupe toutes les versions de lignes qui étaient actives pendant la fenêtre au lieu de reconstruire un seul moment dans le temps.

L’exemple suivant calcule des statistiques descriptives pour les salaires des employés sur une fenêtre horaire, regroupées par département. Il utilise la Employee table version système définie dans les tableaux temporels et les scénarios d’utilisation des tables temporelles. La colonne AnnualSalary est une mesure numérique bien adaptée aux fonctions AVG, MIN, MAX et STDEV.

La clause FOR SYSTEM_TIME BETWEEN ... AND renvoie chaque version de ligne qui chevauche la période. En conséquence, les agrégats incluent tous les salaires en vigueur pendant la fenêtre. Lorsque le salaire d’un employé change, la requête renvoie plusieurs versions pour cet employé, et chaque version contribue aux agrégats.

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;

Contenu connexe

  • Last updated on