Eseguire query sui dati in una tabella temporale con versione gestita dal sistema

Si applica a: SQL Server 2016 (13.x) e versioni successive database SQL di Azure AzureSQL Managed InstanceSQL database in Microsoft Fabric

Per ottenere lo stato più recente (attuale) dei dati in una tabella temporale, interrogalo allo stesso modo di una tabella non temporale. Se le colonne PERIOD non sono nascoste, i rispettivi valori compaiono in una query SELECT *. Se specifichi le colonne PERIOD come HIDDEN, i relativi valori non compaiono in una query SELECT *. Quando le PERIOD colonne sono nascoste, riferiscile specificamente nella SELECT clausola per restituirne i valori.

Per eseguire un'analisi basata sul tempo, si utilizza la FOR SYSTEM_TIME clausola con quattro sottoclausole specifiche temporali per interrogare i dati tra le tabelle corrente e storica. Per ulteriori informazioni su queste clausole, vedere tabelle temporali e clausola FROM con JOIN, APPLY e 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

Puoi specificare FOR SYSTEM_TIME indipendentemente per ogni tabella in una query. Usalo all'interno di espressioni di tabella comuni, funzioni a valori di tabella e stored procedure. Quando usi un alias di tabella con una tabella temporale, includi la FOR SYSTEM_TIME clausola tra il nome della tabella temporale e l'alias (vedi Query per un tempo specifico usando il AS OF secondo esempio della sottoclausola ).

Query per un data/ora specifica con la sottoclausola AS OF

Usa la AS OF sottoclausola per ricostruire lo stato dei dati come era in un momento specifico del passato. Puoi ricostruire i dati con la precisione del tipo datetime2 che hai specificato nelle PERIOD definizioni delle colonne.

Usa la AS OF sottoclausola con letterali o variabili costanti per specificare dinamicamente la condizione temporale. I valori che fornisci sono interpretati come tempo UTC.

Questo primo esempio restituisce lo stato della dbo.Department tabella AS OF a una data specifica del passato.

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

Questo secondo esempio confronta i valori tra due punti nel tempo per un subset di righe.

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;

Usare le viste con la sottoclausola AS OF nelle query temporali

Le visuali sono utili quando hai bisogno di un'analisi complessa in un punto nel tempo. Un esempio comune è generare oggi un report aziendale con i valori del mese precedente.

I clienti si affidano in genere a un modello di database normalizzato che include molte tabelle con relazioni di chiave esterna. Scoprire come apparivano i dati di quel modello normalizzato in un momento del passato può essere una sfida, perché tutte le tabelle cambiano indipendentemente secondo la propria cadenza.

In questo caso, la soluzione migliore consiste nel creare una vista e applicare la sottoclausola AS OF all'intera vista. Questo approccio disaccoppia la modellazione dello strato di accesso ai dati dall'analisi puntuale nel tempo, perché SQL Server applica la AS OF clausola in modo trasparente a tutte le tabelle temporali che partecipano alla definizione della vista. Inoltre, è possibile combinare tabelle temporali e non temporali nella stessa vista e la clausola AS OF viene applicata solo a quelle temporali. Se la vista non contiene alcun riferimento ad almeno una tabella temporale, l'applicazione delle clausole di query temporali genera un errore.

Il codice di esempio seguente crea una vista che unisce tre tabelle temporali: Department, CompanyLocation e 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

È ora possibile eseguire una query sulla vista usando la sottoclausola AS OF e un valore letterale datetime2:

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

Query per le modifiche apportate a colonne specifiche nel tempo

Le subclausole temporali FROM ... TO, BETWEEN ... AND e CONTAINED IN sono utili quando è necessario recuperare tutte le modifiche storiche per una riga specifica nella tabella corrente (nota anche come audit dei dati).

Le prime due sottoclausole restituiscono versioni di riga che si sovrappongono a un periodo specificato (cioè quelle iniziate prima del periodo dato e terminate dopo), mentre CONTAINED IN restituiscono solo quelle esistenti entro i confini specificati del periodo.

Se cerchi solo versioni non aggiornate delle righe, consulta direttamente la tabella della cronologia per le migliori prestazioni delle query. Da usare ALL per interrogare dati attuali e storici senza alcuna restrizione.

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

Per riassumere come appaiono i dati in un periodo, combinare FOR SYSTEM_TIME BETWEEN ... AND e GROUP BY aggregare le funzioni. Utilizzare questa tecnica per statistiche descrittive e analisi delle tendenze. Raggruppa tutte le versioni di riga che erano attive durante la finestra temporale, invece di ricostruire un singolo punto nel tempo.

Il seguente esempio calcola statistiche descrittive per gli stipendi dei dipendenti in una finestra temporale, raggruppate per dipartimento. Utilizza la Employee tabella versionata al sistema definita negli scenari di utilizzo delle tabelle temporali e delle tabelle temporali. La AnnualSalary colonna è una misura numerica ben adatta alle AVGfunzioni, MIN, MAX, e STDEV .

La clausola FOR SYSTEM_TIME BETWEEN ... AND restituisce ogni versione della riga che si sovrappone al periodo. Di conseguenza, gli aggregati includono ogni stipendio effettivo durante la finestra di lavoro. Quando lo stipendio di un dipendente cambia, la query restituisce più versioni per quel dipendente, e ogni versione contribuisce agli aggregati.

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;

Contenuti correlati

  • Last updated on