Consultar dados em uma tabela temporal versionada pelo sistema

Aplica-se a: SQL Server 2016 (13.x) e versões posteriores Banco de Dados SQL do AzureInstância Gerenciada de SQL do AzureSQL database in Microsoft Fabric

Para obter o estado mais recente (atual) dos dados em uma tabela temporal, consulte-a da mesma forma que uma tabela não temporal. Se as colunas PERIOD não estiverem ocultas, seus valores aparecerão em uma consulta SELECT *. Se você especificar colunas PERIOD como HIDDEN, os valores delas não aparecerão em uma consulta SELECT *. Quando as colunas PERIOD estiverem ocultas, faça referência a elas especificamente na cláusula SELECT para retornar seus valores.

Para realizar análises baseadas em tempo, use a FOR SYSTEM_TIME cláusula com quatro subcláusulas específicas para o tempo para consultar dados nas tabelas atuais e de histórico. Para obter mais informações sobre essas cláusulas, consulte tabelas temporais e cláusula FROM mais 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

Você pode especificar FOR SYSTEM_TIME independentemente para cada tabela em uma consulta. Use-a em expressões de tabela comuns, funções com valor de tabela e procedimentos armazenados. Quando você usa um alias de tabela com uma tabela temporal, inclua a FOR SYSTEM_TIME cláusula entre o nome da tabela temporal e o alias (veja Consulta para um tempo específico usando o AS OF segundo exemplo da subcláusula ).

Consultar um horário específico usando a subcláusula AS OF

Use a AS OF subcláusula para reconstruir o estado dos dados como eles estavam em qualquer momento específico do passado. Você pode reconstruir os dados com a precisão do tipo datetime2 que você especificou nas definições da coluna PERIOD.

Use a AS OF subcláusula com literais ou variáveis constantes para especificar dinamicamente a condição de tempo. Os valores que você fornece são interpretados como tempo UTC.

Esse primeiro exemplo retorna o estado da dbo.Department tabela AS OF uma data específica no passado.

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

Este segundo exemplo compara os valores entre dois pontos no tempo para um subconjunto de linhas.

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;

Utilizar visões com a subcláusula AS OF em consultas temporais

Views são úteis quando você precisa fazer uma análise complexa em um momento específico. Um exemplo comum é gerar um relatório empresarial hoje com os valores do mês anterior.

Geralmente, os clientes têm um modelo de banco de dados normalizado que envolve muitas tabelas com relações de chave estrangeira. Descobrir como os dados desse modelo normalizado foram vistos em um ponto do passado pode ser desafiador, porque todas as tabelas mudam independentemente em seu próprio ritmo.

Nesse caso, a melhor opção é criar uma exibição e aplicar a subcláusula AS OF em toda a exibição. Essa abordagem desacopla a modelagem da camada de acesso aos dados da análise pontual no tempo, porque o SQL Server aplica a cláusula AS OF de forma transparente a todas as tabelas temporais que participam da definição da exibição. Além disso, você pode combinar tabelas temporais e tabelas não temporais na mesma exibição, e AS OF será aplicado apenas às tabelas temporais. Se a exibição não fizer referência a pelo menos uma tabela temporal, a aplicação de cláusulas de consulta temporal a ela falhará com um erro.

O código de exemplo a seguir cria uma exibição que une três tabelas temporais: 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

Você pode consultar a exibição usando a subcláusula AS OF e um datetime2 literal:

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

Consultar alterações em linhas específicas ao longo do tempo

As subcláusulas temporais FROM ... TO, BETWEEN ... AND e CONTAINED IN são úteis quando você precisa obter todas as alterações históricas de uma linha específica na tabela atual (também conhecida como auditoria de dados).

As duas primeiras subcláusulas retornam versões de linha que se sobrepõem a um período especificado (ou seja, aquelas que começaram antes do período dado e terminaram depois dele), enquanto CONTAINED IN retornam apenas aquelas que existiam dentro dos limites especificados.

Se você procurar apenas por versões de linhas não atuais, consulte diretamente a tabela de histórico para melhor desempenho de consulta. Use ALL para consultar dados atuais e históricos sem restrições.

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

Para resumir como os dados se apresentam ao longo de um período, combine FOR SYSTEM_TIME BETWEEN ... AND com GROUP BY e agregue funções. Use essa técnica para estatísticas descritivas e análise de tendências. Ela consolida todas as versões de linha que estavam ativas durante o período, em vez de reconstruir um único ponto no tempo.

O exemplo a seguir calcula estatísticas descritivas para salários de funcionários ao longo de uma janela de tempo, agrupadas por departamento. Ele utiliza a Employee tabela versionada pelo sistema definida em tabelas temporais e cenários de uso de tabelas temporais. A coluna AnnualSalary é uma medida numérica bem adequada para as funções AVG, MIN, MAX e STDEV.

A cláusula FOR SYSTEM_TIME BETWEEN ... AND retorna cada versão de linha que se sobrepõe ao período. Como resultado, os agregados incluem todos os salários que estavam em vigor durante a janela. Quando o salário de um funcionário muda, a consulta retorna várias versões para esse funcionário, e cada versão contribui para os agregados.

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;

Conteúdo relacionado

  • Last updated on