적용 대상:
SQL Server 2016 (13.x) 및 이후 버전: Azure SQL 데이터베이스,Azure SQL Managed Instance,Microsoft Fabric의 SQL 데이터베이스
시간 테이블에서 데이터의 최신(현재) 상태를 얻으려면, 비시간 테이블과 같은 방식으로 쿼리하세요.
PERIOD 열이 숨겨져 있지 않은 경우 해당 값은 SELECT * 쿼리에 나타납니다. 열을 HIDDENPERIOD로 지정하면 해당 값은 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 수 있습니다. 일반적인 테이블 표현식, 테이블 값 함수, 저장 프로시저 안에서 사용할 수 있습니다. temporal 테이블과 함께 테이블 별칭을 사용할 때는 temporal 테이블 이름과 별칭 사이에 FOR SYSTEM_TIME 절을 포함하세요(AS OF 하위 절을 사용하여 특정 시간에 대해 쿼리의 두 번째 예를 참조하세요).
AS OF 하위 절을 사용한 특정 시간의 쿼리
하위 절을 AS OF 사용하여 과거 특정 시점의 데이터 상태를 재구성하세요.
열 정의에서 지정한 PERIOD 형식의 정밀도로 데이터를 다시 구성할 수 있습니다.
상수 리터럴이나 변수가 있는 서브절을 사용하여 AS OF 시간 조건을 동적으로 지정하세요. 제공하신 값은 UTC 시간으로 해석됩니다.
이 첫 번째 예시는 테이블 AS OF 의 dbo.Department 상태를 과거의 특정 날짜로 반환합니다.
-- 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 하기 때문입니다. 또한 같은 뷰에서 temporal 테이블을 비 temporal 테이블과 결합할 수 있으며 AS OF는 temporal 테이블에만 적용됩니다. 뷰에서 temporal 테이블을 최소 하나 이상 참조하지 않는 경우 임시 쿼리 절을 적용하면 오류가 발생하면서 실패합니다.
다음 샘플 코드에서는 3개의 temporal 테이블(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;
시간 창 전반에 걸친 추세를 분석하세요
일정 기간 동안 데이터가 어떻게 나타나는지 요약하려면 GROUP BY을(를) FOR SYSTEM_TIME BETWEEN ... AND 및 집계 함수와 결합하세요. 이 기법을 기술 통계와 추세 분석에 활용하세요. 단일 시점을 재구성하는 대신, 해당 기간 동안 활성 상태였던 모든 행 버전을 집계합니다.
다음 예에서는 일정 시간 구간에 걸친 직원 급여의 기술 통계량을 부서별로 계산합니다. 이 테이블은 시간 테이블과 시간 테이블 사용 시나리오에서 정의된 시스템 버전 기반 테이블을 사용합니다Employee.
AnnualSalary 열은 숫자 측정값으로, MIN, MAX, STDEV, AVG 함수에 적합합니다.
이 절은 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;