이력 테이블과 시점 조회

2. 핵심 원리

2.1 유효 기간 컬럼과 반개구간

CREATE TABLE emp_hist (
    emp_id     INT
  , dept       VARCHAR(10)
  , grade      VARCHAR(10)
  , valid_from DATE
  , valid_to   DATE
  , PRIMARY KEY (emp_id, valid_from)
);

한 행은 "emp_id 사원이 valid_from 부터 valid_to 직전까지 이 부서·직급이었다" 를 뜻합니다. 현재 행은 끝을 모르므로 valid_to 에 아주 먼 날짜 DATE '9999-12-31' 을 넣습니다. 기본 키를 (emp_id, valid_from) 으로 잡으면 같은 사원에 같은 시작일이 두 번 들어가지 못합니다.

끝 날짜는 포함하지 않는 반개구간 [시작, 끝) 으로 둡니다. 이 방식이면 다음 행의 valid_from 이 이전 행의 valid_to 와 같은 날짜가 됩니다. 2023-03-01 에 대리로 승진했다면 사원 행은 valid_to = 2023-03-01, 대리 행은 valid_from = 2023-03-01 입니다.

끝을 포함하는 방식은 사원 행이 2023-02-28 로 끝나야 해서 "-1일" 계산이 필요합니다. Oracle DATE 는 시·분·초를 가질 수 있어서 2023-02-28 00:00:00 까지만 유효한 것인지 그날 종일인지 애매해지고 경계 시각이 틀리기 쉽습니다.

핵심

유효 기간은 [valid_from, valid_to) 반개구간으로 둡니다. 다음 행의 시작이 이전 행의 끝과 같아서 겹침·구멍 검사가 단순해집니다.

2.2 현재와 특정 날짜의 상태

현재 상태는 날짜 조건의 특수한 경우입니다. d 가 오늘이면 같은 행이 나오지만, 미래 날짜의 변경을 미리 넣어 둔 이력(예약 변경)에서는 다릅니다. 이때 9999-12-31 행은 "가장 마지막 행" 이고, 오늘 유효한 행은 날짜 조건으로만 찾을 수 있습니다.

최신 1건을 ROW_NUMBER() 로 고르는 방식은 고급 01 순위 함수와 같은 패턴입니다. 결과는 같지만 정렬 비용이 들고, 이력이 잘못되어 현재 행이 없는 사원도 가장 최근 과거 행이 골라진다는 차이가 있습니다.

2.3 시점 조인

주문 표와 이력 표를 조인할 때 조건에 기간을 넣으면 "주문일 당시의 부서" 가 붙습니다.

  JOIN emp_hist h ON h.emp_id = o.emp_id
                 AND h.valid_from <= o.ord_date
                 AND o.ord_date < h.valid_to

반개구간이라 주문일이 경계일과 같아도 정확히 한 행에만 붙습니다. 이력이 겹치면 주문 하나가 여러 행으로 늘어나 합계가 부풀고, 구멍이 있으면 그 기간의 주문이 조인에서 사라집니다. 그래서 겹침·구멍 검사가 중요합니다.

2.4 이력 변경 처리

값이 바뀌면 행을 고치지 않고 닫고 새로 엽니다. 두 문장이 한 트랜잭션으로 묶여야 합니다.

1. 기존 현재 행의 valid_to 를 변경일로 바꿔 닫는다.
2. 변경일을 valid_from 으로 하고 valid_to = 9999-12-31 인 새 행을 넣는다.

중간에 실패하면 현재 행이 없거나 두 개가 되므로 둘은 같이 커밋하거나 같이 롤백합니다. 고급 08 MERGE 의 UPSERT 는 값을 덮어쓰는 방식이라 이력에는 맞지 않습니다.

2.5 기간 겹침과 구멍

두 구간 A, B 가 겹칠 조건은 다음 한 줄입니다.

a.start < b.end  AND  b.start < a.end

반개구간이라 끝과 시작이 같은 이웃 행은 겹치지 않는다고 판정됩니다(b.start < a.end 가 거짓). 같은 사원의 행끼리만 비교하고, 자기 자신은 a.valid_from < b.valid_from 으로 제외하면 같은 짝이 두 번 나오는 것도 막습니다.

구멍은 이전 행의 valid_to 가 이번 행의 valid_from 보다 이른 경우입니다. LAG 로 이전 행의 valid_to 를 가져와 비교합니다(고급 03 LAG·LEAD).

이 네 DB 에는 기간 겹침을 막는 선언적 제약이 없습니다. 저장 전에 검사 쿼리를 돌리거나 트리거로 막고, 배치로 주기 검사를 합니다.

2.6 DB 내장 이력 기능

H2 는 이 기능을 지원하지 않아 아래 코드는 문법 검토만 합니다.

-- MSSQL: 시스템 버전 임시 테이블 (2016 부터, H2 미지원)
SELECT * FROM emp_hist FOR SYSTEM_TIME AS OF '2024-06-30';

-- Oracle: Flashback Query (언두 보존 기간 안에서만, H2 미지원)
SELECT * FROM emp AS OF TIMESTAMP TO_TIMESTAMP('2026-09-29 09:00:00', 'YYYY-MM-DD HH24:MI:SS');

시스템 버전은 "DB 가 그 행을 기록한 시각" 이고 업무상 "그 날짜부터 유효" 와는 다릅니다. 소급 적용이나 예약 변경이 있는 업무에는 직접 만든 valid_from·valid_to 이력이 필요합니다. Flashback 은 언두 데이터가 남아 있는 동안만 되므로 업무 이력을 대신하지 못합니다.