Interrogar dados numa tabela temporal versionada pelo sistema

Aplica-se a: SQL Server 2016 (13.x) e versões posteriores Base de Dados SQL do Azure Azure SQL Managed InstanceBase de dados SQL no Microsoft Fabric

Para obter o estado mais recente (atual) dos dados numa tabela temporal, consulte-os da mesma forma que numa tabela não temporal. Se as colunas PERIOD não estiverem ocultas, seus valores aparecerão em uma consulta SELECT *. Se especificar PERIOD colunas como HIDDEN, os seus valores não aparecem numa SELECT * consulta. Quando as PERIOD colunas estiverem ocultas, referencia-as especificamente na SELECT cláusula para devolver os seus valores.

Para realizar uma análise baseada no tempo, use a FOR SYSTEM_TIME cláusula com quatro subcláusulas específicas temporais 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

Podes especificar FOR SYSTEM_TIME independentemente para cada tabela numa consulta. Utilize-o em expressões de tabela comuns, funções com valor de tabela e procedimentos armazenados. Quando usar 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 por um tempo específico usando a subcláusula AS OF

Use a AS OF subcláusula para reconstruir o estado dos dados tal como estavam em qualquer momento específico do passado. Pode reconstruir os dados com a precisão do tipo datetime2 que especificou nas PERIOD definições das colunas.

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

Este primeiro exemplo devolve 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 vistas com a subcláusula AS OF em consultas temporais

As visualizações são úteis quando precisa de uma análise complexa de um determinado momento. Um exemplo comum é gerar hoje um relatório empresarial com os valores do mês anterior.

Normalmente, os clientes têm um modelo de banco de dados normalizado, que envolve muitas tabelas com relações de chave estrangeira. Descobrir que aspeto tinham os dados desse modelo normalizado num determinado momento no passado pode ser difícil, porque todas as tabelas mudam independentemente, cada uma ao seu próprio ritmo.

Nesse caso, a melhor opção é criar uma visualização e aplicar a subcláusula AS OF a toda a visualização. Esta abordagem dissocia a modelação da camada de acesso a dados da análise num determinado momento, porque o SQL Server aplica transparentemente a cláusula AS OF a todas as tabelas temporais que participam na definição da vista. Além disso, você pode combinar tabelas temporais com não temporais na mesma exibição e AS OF é aplicada apenas às temporais. Se a vista não fizer referência a pelo menos uma tabela temporal, a aplicação de cláusulas de consulta temporal resultará num erro.

O código de exemplo a seguir cria um modo de exibição que une três tabelas temporais: Department, CompanyLocatione 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';

Consulta de 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 é necessário obter todas as alterações históricas de uma linha específica na tabela corrente (também designado por auditoria de dados).

As as duas primeiras subcláusulas devolvem versões de registo que se sobrepõem a um período especificado (ou seja, aquelas que começaram antes do período indicado e terminaram depois dele), enquanto CONTAINED IN devolve apenas as que existiram dentro dos limites do período especificado.

Se procurar apenas versões de linhas não atuais, consulte diretamente a tabela de histórico para obter o melhor desempenho de consulta. Use ALL para consultar dados atuais e históricos sem quaisquer 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 o aspeto dos dados ao longo de um período, combine FOR SYSTEM_TIME BETWEEN ... AND com GROUP BY e funções de agregação. Use esta técnica para estatísticas descritivas e análise de tendências. Agrega todas as versões de cada linha que estiveram ativas durante esse intervalo, em vez de reconstruir um único ponto no tempo.

O exemplo seguinte calcula estatísticas descritivas para os salários dos funcionários ao longo de uma janela temporal, agrupada por departamento. Utiliza a tabela com versão do sistema Employee definida em tabelas temporais e cenários de utilização de tabelas temporais. A coluna AnnualSalary é uma medida numérica bem adequada às funções AVG, MIN, MAX e STDEV.

A cláusula FOR SYSTEM_TIME BETWEEN ... AND devolve todas as versões de linha que se sobreponham ao período. Como resultado, os agregados incluem todos os salários que estiveram em vigor durante a janela. Quando o salário de um funcionário muda, a consulta devolve várias versões para esse colaborador, 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