Поделиться через

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

  • Статья

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

Если требуется получить актуальное (текущее) состояние данных в темпоральной таблице, можно отправить запрос точно так же, как в нетемпоральной таблице. PERIOD Если столбцы не скрыты, их значения отображаются в запросеSELECT *. Если вы указали PERIOD как столбцы HIDDEN, их значения не отображаются в запросе SELECT * . Если столбцы PERIOD скрыты, следует специально указать ссылку на столбцы 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 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 JOIN [dbo].[LocationDepartments]
    ON [CompanyLocation].LocID = LocationDepartments.LocID
LEFT JOIN [dbo].[Department]
    ON LocationDepartments.DeptID = [Department].DeptID;
GO

Вы можете запросить представление с помощью AS OF подклауза и литерала datetime2 :

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

Связанный контент