Запрос данных в темпоральной таблице с системным управлением версиями
Применимо к: SQL Server 2016 (13.x) и более поздних версий Azure SQL Database Управляемый экземпляр SQL Azure
Если требуется получить актуальное (текущее) состояние данных в темпоральной таблице, можно отправить запрос точно так же, как в нетемпоральной таблице. Если столбцы PERIOD
не скрыты, их значения отображаются в запросе SELECT *
. Если вы указали PERIOD
столбцы как HIDDEN
, их значения не отображаются в запросе SELECT *
. Если столбцы PERIOD
скрыты, следует специально указать ссылку на столбцы PERIOD
в предложении SELECT
, чтобы вернуть значения этих столбцов.
Для выполнения всех видов анализа на основе времени следует использовать новое предложение FOR SYSTEM_TIME
с четырьмя вложенными предложениями для темпоральных таблиц, чтобы запрашивать данные в текущих таблицах и таблицах журнала. Дополнительные сведения об этих предложениях см. в разделах Темпоральные таблицы и FROM (Transact-SQL).
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';
Кроме того, можно запросить представление с помощью вложенного AS OF
списка с локальным часовом поясом и AT TIME ZONE
:
/* Querying view AS OF with local time*/
DECLARE @LocalTime DATETIMEOFFSET = '2021-09-01 10:00:00.7230011 -07:00';
SELECT * FROM [vw_GetOrgChart]
FOR SYSTEM_TIME AS OF @LocalTime AT TIME ZONE 'UTC';
Запрос изменений в определенных строках со временем
Темпоральные вложенные предложения 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;
Дальнейшие действия
- Темпоральные таблицы
- FROM (Transact-SQL)
- AT TIME ZONE (Transact-SQL)
- Создание темпоральной таблицы с системным управлением версиями
- Изменение данных в темпоральной таблице с системным управлением версиями
- Изменение схемы темпоральной таблицы с системным управлением версиями
- Остановка системного управления версиями в темпоральной таблице с системным управлением версиями
Обратная связь
https://aka.ms/ContentUserFeedback.
Ожидается в ближайшее время: в течение 2024 года мы постепенно откажемся от GitHub Issues как механизма обратной связи для контента и заменим его новой системой обратной связи. Дополнительные сведения см. в разделеОтправить и просмотреть отзыв по