Запрос данных в системной темпоральной таблице

Относится к: SQL Server 2016 (13.x) и более поздние версии База данных SQL AzureУправляемый экземпляр SQL AzureSQL Database в Microsoft Fabric

Чтобы получить последнее (текущее) состояние данных во временной таблице, запросите её так же, как к невременной таблице. Если столбцы PERIOD не скрыты, их значения отображаются в запросе SELECT *. Если указать PERIOD столбцы как HIDDEN, их значения не отображаются в SELECT * запросе. Когда столбцы PERIOD скрыты, ссылайтесь на них конкретно в SELECT предложении, чтобы вернуть их значения.

Для проведения анализа на основе времени используйте FOR SYSTEM_TIME предложение с четырьмя временно-специфическими подпредложениями для запроса данных между текущими и историческими таблицами. Дополнительные сведения об этих предложениях см. в темпоральных таблицах и предложении FROM плюс 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

Вы можете указывать FOR SYSTEM_TIME отдельно для каждой таблицы в запросе. Используйте его в общих выражениях таблиц, функциях с таблицьными значениями и сохранённых процедурах. Когда вы используете псевдоним таблицы с темпоральной таблицей, укажите предложение FOR SYSTEM_TIME между именем темпоральной таблицы и её псевдонимом (см. второй пример в разделе «Запрос для определённого времени с использованием вложенного предложения AS OF»).

Запрос о конкретном времени с использованием подчиненного предложения AS OF

Используйте подзапрос AS OF, чтобы восстановить состояние данных таким, каким оно было в любой конкретный момент в прошлом. Вы можете восстановить данные с точностью типа datetime2, который вы указали в определениях столбцов PERIOD.

Используйте AS OF подпредложение с постоянными литералами или переменными, чтобы динамически задать временное условие. Указанные вами значения интерпретируются как UTC-время.

Этот первый пример возвращает состояние dbo.Department таблицы AS OF на определённую дату в прошлом.

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

Во втором примере сравниваются значения в интервале между двумя моментами времени для подмножества строк.

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;

Используйте представления с подзапросами AS OF в темпоральных запросах

Представления полезны, когда требуется сложный анализ данных на определённый момент времени. Распространённый пример — создание бизнес-отчета сегодня с значениями за предыдущий месяц.

Как правило, у клиентов имеется нормализованная модель базы данных, которая включает в себя множество таблиц со связями по внешнему ключу. Выяснить, как данные из этой нормализированной модели выглядели в определённый момент прошлого, бывает сложно, потому что все таблицы меняются независимо по своей частоте.

В этом случае лучше всего создать представление и применить пункт AS OF ко всему представлению. Этот подход разделяет моделирование слоя доступа к данным и анализ на определённый момент времени, поскольку SQL Server прозрачно применяет предложение AS OF ко всем временным таблицам, участвующим в определении представления. Кроме того, можно объединить темпоральные таблицы с нетемпоральными таблицами в одном представлении, и AS OF применяется только к темпоральным. Если представление не ссылается хотя бы на одну темпоральную таблицу, применение темпоральных запросов к нему завершается ошибкой.

В следующем примере кода создается представление, которое объединяет три темпоральные таблицы: Department, CompanyLocationи 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

Вы можете выполнить запрос для представления, используя AS OF подзапрос и литерал datetime2.

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

Запрос изменений в определенных строках со временем

Временные подзапросы FROM ... TO, BETWEEN ... AND и CONTAINED IN полезны, когда нужно получить все исторические изменения по конкретной строке в текущей таблице (это также называется аудитом данных).

Первые два подусловия возвращают версии строк, которые перекрываются с указанным периодом (то есть те, которые начались до данного периода и закончились после него), в то время как CONTAINED IN возвращает только те, которые существовали в пределах границ указанного периода.

Если вы ищете только версии строк, не актуальные, задавайте запросы напрямую к таблице истории для максимальной производительности. Используйте ALL для запроса текущих и исторических данных без каких-либо ограничений.

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

Чтобы подытожить, как выглядят данные за период, объединяйте FOR SYSTEM_TIME BETWEEN ... AND и GROUP BY агрегируйте функции. Используйте этот метод для описательной статистики и анализа тенденций. Он объединяет все версии строк, которые были активны в течение этого окна, вместо того чтобы восстанавливать состояние на один конкретный момент времени.

Следующий пример вычисляет описательные статистические данные по зарплатам сотрудников в течение определённого временного окна, сгруппированные по отделам. Он использует Employee системную версионную таблицу, определённую в временных таблицах и сценариях использования временной таблицы. Столбец AnnualSalary — это числовой показатель, хорошо подходящий для функций AVG, MIN, MAX и STDEV.

Оговорка FOR SYSTEM_TIME BETWEEN ... AND возвращает все версии строки, перекрывающие период. В результате агрегированные данные включают все значения заработной платы, действовавшие в течение этого периода. При изменении зарплаты сотрудника запрос возвращает несколько версий для этого сотрудника, и каждая версия вносит вклад в агрегаты.

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;

Связанные материалы

  • Last updated on