[250220] Developing Time-Oriented Applications in SQL

1. Chapter 6 Querying State Tables
2. Chapter 6: Querying State Tables Summary

1 Chapter 6 Querying State Tables

valid-time state table 에서 가장 많이 사용하는 쿼리는 “지금 현재 무엇이 참인가?” 이다. 비슷한 유형으로 “지금이 아닌 다른 시점의 무엇이 참인가?” 도 있다. 이러한 time-slice query는 쉽게 SQL로 변환할 수 있다.

반대로 예를 들어, “연간 수입이 5만 달러 이상인 사람은 누구인가?”라는 현재의 쿼리에 대해, “누가 연간 5만 달러 이상을 벌고 있는지, 언제 벌기 시작했는지”와 같은 시퀀스 쿼리가 있다. 이러한 쿼리를 SQL로 변환하는 것은 쉽지 않다.

1.1 현재상태추출

-- CF 6.1 What is Bob's current position?
SELECT JOB_TITLE_CODE1 FROM EMPLOYEES, INCUMBENTS, POSITIONS
WHERE FIRST_NAME = 'Bob'
  AND EMPLOYEES.SSN = INCUMBENTS.SSN
  AND INCUMBENTS.PCN = POSITIONS.PCN
  AND END_DATE = DATE '3000-01-01';

-- CF 6.2 What is Bob's current position?
SELECT JOB_TITLE_CODE1 FROM EMPLOYEES, INCUMBENTS, POSITIONS
WHERE FIRST_NAME = 'Bob'
  AND EMPLOYEES.SSN = INCUMBENTS.SSN
  AND INCUMBENTS.PCN = POSITIONS.PCN
  AND START_DATE <= CURRENT_DATE
  AND CURRENT_DATE < END_DATE;

직원의 현재 직위와 급여를 얻으려면, 현재 상태의 INCUMBENTS 테이블과 SAL HISTORY 테이블을 모두 사용해야 합니다.

-- CF 6.3 What is Bob's current position and salary?
SELECT JOB_TITLE_CODE1, AMOUNT
FROM EMPLOYEES, INCUMBENTS, POSITIONS, SAL_HISTORY
WHERE FIRST_NAME = 'Bob'
  AND EMPLOYEES.SSN = INCUMBENTS.SSN
  AND INCUMBENTS.PCN = POSITIONS.PCN
  AND START_DATE <= CURRENT_DATE 
    AND CURRENT_DATE < END_DATE
  AND HISTORY_START_DATE <= CURRENT_DATE
    AND CURRENT_DATE < HISTORY_END_DATE
  AND SAL_HISTORY.SSN = EMPLOYEES.SSN;

-- CF 6.4 What employees currently have no position?
SELECT FIRST_NAME
FROM EMPLOYEES
WHERE NOT EXISTS (
  SELECT *
  FROM INCUMBENTS
  WHERE INCUMBENTS.SSN = EMPLOYEES.SSN
    AND START_DATE <= CURRENT_DATE
    AND CURRENT_DATE < END_DATE
);

이렇게 현재 상태를 추출하는 것을 current valid time-slice query 라고 한다.

1.2 이전 상태 추출

타임슬라이스 쿼리가 현재에만 적용되는 것이 아니라, 이전 상태에도 적용될 수 있다.

-- Code Fragment 6.5 What was Bob's position at the beginning of 1997?
SELECT JOB_TITLE_CODE1
FROM EMPLOYEES, INCUMBENTS, POSITIONS
WHERE FIRST_NAME = 'Bob'
  AND EMPLOYEES.SSN = INCUMBENTS.SSN
  AND INCUMBENTS.PCN = POSITIONS.PCN
  AND START_DATE <= DATE '1997-01-01'
  AND DATE '1997-01-01' < END_DATE;

지금까지는 시간에 따라 변하는 테이블에서 특정 시점의 상태 를 추출하는 것에 대해 살펴보았다.

1.3 sequenced query (시퀀스 쿼리, 시간순서 쿼리)

시퀀스 쿼리의 기존적인 operation(작업)은 다음과 같다. selection, projection, union,sorting, join, difference, and duplicate elimination

sequence selection 은 쉽다. 변경할 필요가 없다.

-- Code Fragment 6.6 Who makes or has made more than $50,000 annually?
SELECT DISTINCT SSN
FROM SALARY_HISTORY
WHERE AMOUNT > 50000

sequenced projection 도 쉽다. 단순히 select list 에 timestamp column(s) 를 포함시키면 된다.

-- Code Fragment 6.7 List the social security numbers of current and past employees.
SELECT SSN, HISTORY_START_DATE, HISTORY_END_DATE
FROM SAL_HISTORY;

1.3.1 Sequenced Join

각 직원의 급여와 직위 이력을 확인하려면, 각 시점에 대한 직원의 급여와 직위를 확인해야 한다. 급여는 SAL_HISTORY.AMOUNT 에서 나오고, 직책은 INCUMBENTS.PCN 에서 사용할 수 있다.

Figure 6.2 First case of a sequenced join.

    SAL_HISTORY
    ├─────────────────>

    INCUMBENTS
├───────────────────────────>

              Result
              ├─────────>

Figure 6.3 Second case of a sequenced join.

         SAL_HISTORY
         ├──────────────────────────>

INCUMBENTS
├──────────────────>

                   Result
                   ├─────────>

SQL 을 사용하기 전에 SAL_HISTORY, INCUMBENTS 테이블의 valid time 기간이 어떻게 겹치는지 확인해야 한다. 가능한 사례는 4가지이다.

SAL_HISTORY 행과 관련된 기간은 전적으로 INCUMBENTS 행과 관련된 기간에 포함. (Figure 6.2)
두 번째 경우에는 두 기간이 겹치지 않음(그림 6.3 참조).

나머지 두개는 자연스럽게 1,2번의 반대 경우를 나타낸다.

-- 첫 번째 쿼리: INCUMBENTS 기간이 SAL_HISTORY 기간을 완전히 포함하는 경우
SELECT S.SSN, AMOUNT, PCN,
    S.HISTORY_START_DATE, S.HISTORY_END_DATE
FROM SAL_HISTORY AS S, INCUMBENTS
WHERE S.SSN = INCUMBENTS.SSN
    AND INCUMBENTS.START_DATE <= S.HISTORY_START_DATE
    AND S.HISTORY_END_DATE <= INCUMBENTS.END_DATE
UNION ALL
-- 두 번째 쿼리: SAL_HISTORY 종료일이 INCUMBENTS 종료일을 초과하는 경우
SELECT S.SSN, AMOUNT, PCN,
    S.HISTORY_START_DATE, INCUMBENTS.END_DATE
FROM SAL_HISTORY AS S, INCUMBENTS
WHERE S.SSN = INCUMBENTS.SSN
    AND S.HISTORY_START_DATE >= INCUMBENTS.START_DATE
    AND INCUMBENTS.END_DATE < S.HISTORY_END_DATE
    AND S.HISTORY_START_DATE < INCUMBENTS.END_DATE
UNION ALL
-- 세 번째 쿼리: SAL_HISTORY 시작일이 INCUMBENTS 시작일보다 이전인 경우
SELECT S.SSN, AMOUNT, PCN,
    INCUMBENTS.START_DATE, S.HISTORY_END_DATE
FROM SAL_HISTORY AS S, INCUMBENTS
WHERE S.SSN = INCUMBENTS.SSN
    AND INCUMBENTS.START_DATE > S.HISTORY_START_DATE
    AND S.HISTORY.END_DATE <= INCUMBENTS.END_DATE
    AND INCUMBENTS.START_DATE < S.HISTORY_END_DATE
UNION ALL
-- 네 번째 쿼리: SAL_HISTORY 기간이 INCUMBENTS 기간을 완전히 포함하는 경우
SELECT S.SSN, AMOUNT, PCN,
    INCUMBENTS.START_DATE, INCUMBENTS.END_DATE
FROM SAL_HISTORY AS S, INCUMBENTS
WHERE S.SSN = INCUMBENTS.SSN
    AND INCUMBENTS.START_DATE > S.HISTORY_START_DATE
    AND INCUMBENTS.END_DATE < S.HISTORY_END_DATE

위 쿼리를 이해하기 위한 중요한 내용은 다음과 같다.

데이터 구조:
SAL_HISTORY 테이블: 직원의 급여 이력을 저장 (SSN, AMOUNT, 시작일, 종료일)
INCUMBENTS 테이블: 직원의 직위 이력을 저장 (SSN, PCN, 시작일, 종료일)
쿼리의 목적:
모든 직원의 급여와 직위 이력을 시간순으로 결합하여 보여주는 것
두 테이블의 시간 범위가 겹치는 부분을 정확하게 매칭
쿼리 구조:
4개의 SELECT 문이 UNION ALL로 결합됨
각 SELECT 문은 서로 다른 시간 관계를 처리:
- 첫 번째: s finishes i (종료 시점이 일치)
- 두 번째: s overlaps^(-1) i (역방향 겹침)
- 세 번째: s overlaps i (정방향 겹침)
- 네 번째: s during^(-1) i (포함관계)
예시 결과:
Table 6.3의 데이터(SAL_HISTORY)와 INCUMBENTS 테이블이 결합되어
Table 6.4와 같은 결과가 생성됨
예: 1995-03-01부터 1996-01-01까지의 기간에 대해 SSN 111223333인 직원의 급여는 15.75이고 PCN은 341288
중복 처리:
UNION ALL을 사용하여 효율적으로 처리
쿼리 설계상 중복이 발생하지 않도록 각 케이스가 서로 배타적

중복처리에 대해서 UNION ALL 과 UNION 의 차이를 설명:

UNION ALL vs UNION의 차이:
UNION: 결과에서 중복된 행을 자동으로 제거하지만, 이를 위해 추가적인 처리 작업이 필요
UNION ALL: 중복 제거 작업을 수행하지 않고 모든 결과를 그대로 합침
이 쿼리에서는 UNION ALL이 더 효율적인데, 그 이유는 쿼리 설계 자체가 중복을 방지하도록 되어있기 때문
중복이 발생하지 않는 이유:
4개의 SELECT 문이 각각 서로 다른 시간 관계를 처리하도록 설계됨
각 케이스가 서로 배타적(mutually exclusive)이어서 동일한 기간이 여러 케이스에 걸쳐 처리되지 않음
예를 들어, 특정 기간이 "s finishes i" 관계에 해당하면 다른 관계(overlaps, during 등)에는 해당되지 않음
중복 처리 옵션들:

만약 기본 테이블에 중복이 있다면, 다음 세 가지 방법 중 하나를 선택할 수 있음: a. 결과에 중복을 그대로 유지 b. UNION ALL을 UNION으로 변경하여 조인 후 중복 제거 c. 조인 전에 중복을 제거한 후 쿼리 실행

성능 고려사항:
UNION ALL은 중복 제거 작업이 없어서 UNION보다 일반적으로 더 빠름
이 쿼리에서는 중복이 발생하지 않도록 설계되었기 때문에 UNION ALL 사용이 최적화된 선택
UNION을 사용하면 불필요한 중복 검사 작업이 발생하여 성능이 저하될 수 있음

역방향 겹침이란 무엇이지?

일반적인 겹침(overlaps)과 역방향 겹침(overlaps^(-1))의 차이는 다음과 같다.

일반적인 겹침(overlaps):
SAL_HISTORY의 시작일이 INCUMBENTS의 시작일보다 늦고
SAL_HISTORY의 종료일이 INCUMBENTS의 종료일보다 이름

``` INCUMBENTS: |------------–—| SAL_HISTORY: |---–—| ```

역방향 겹침(overlaps^(-1)):
SAL_HISTORY의 시작일이 INCUMBENTS의 시작일보다 빠르고
SAL_HISTORY의 종료일이 INCUMBENTS의 종료일 이전

``` INCUMBENTS: |---–—| SAL_HISTORY: |------------–—| ```

1.4 Sequenced EXCEPT (시퀀스 차집합)

SQL의 NOT EXISTS는 두 테이블의 차이를 구할 때 사용되는데, 시간 차원이 추가되면 더 복잡해진다. 예를 들어, 부서장이면서 교수가 아닌 사람을 찾는 경우를 살펴보자.

-- 시간 없는 기본 버전
SELECT SSN 
FROM INCUMBENTS AS I1
WHERE PCN = 455332  -- department head
AND NOT EXISTS (
    SELECT * 
    FROM INCUMBENTS AS I2
    WHERE I2.SSN = I1.SSN
    AND I2.PCN = 821197  -- professor
);

시퀀스 버전에서는 department head였지만 professor가 아니었던 기간을 찾아야 합니다. 이를 위해서는 네 가지 경우를 고려해야 한다.

교수가 되기 전에 부서장이 된 경우
교수에서 강등된 후 부서장이 된 경우
교수였다가 강등되고, 다시 교수가 된 중간 기간에 부서장이었던 경우
부서장 재직 기간 동안 한 번도 교수가 아니었던 경우

1.5 Nonsequenced Variants (비시퀀스 변형)

비시퀀스 쿼리는 시간 컬럼을 일반 컬럼처럼 취급한다.

-- 언제 직원들이 급여 인상을 받았는지
SELECT S2.SSN, S2.HISTORY_START_DATE AS RAISE_DATE
FROM SAL_HISTORY AS S1, SAL_HISTORY AS S2
WHERE S2.AMOUNT > S1.AMOUNT
AND S1.SSN = S2.SSN
AND S1.HISTORY_END_DATE = S2.HISTORY_START_DATE;

1.6 Eliminating Duplicates (중복 제거)

중복 제거는 여러 유형이 있습니다:

Value-equivalent duplicates: 시간 컬럼을 제외한 모든 값이 같은 경우
Current duplicates: 현재 시점에서 중복인 경우
Sequenced duplicates: 어떤 시점에서든 중복이 있는 경우

특히 Coalescing(병합)은 중요한 작업인데, 이는 연속된 시간 기간을 가진 동일한 값들을 하나로 합치는 것.

1.6.1 기본적인 중복 제거

-- 1. 비시퀀스 중복 제거: 모든 컬럼이 정확히 일치하는 경우
SELECT DISTINCT * 
FROM INCUMBENTS;

-- 2. 값-동등성 중복 제거: 시간 컬럼을 제외한 값이 같은 경우
SELECT DISTINCT SSN, PCN 
FROM INCUMBENTS;

-- 3. 현재 중복 제거: 현재 시점에서 중복인 경우
SELECT DISTINCT SSN, PCN 
FROM INCUMBENTS 
WHERE END_DATE = DATE '3000-01-01';

1.6.2 Coalescing (병합)

Coalescing은 중복 제거보다 복잡한 작업으로, 같은 값을 가진 연속된 기간을 하나로 합치는 작업.

SSN       PCN    START_DATE   END_DATE
111223333 120033 1996-01-01   1996-06-01
111223333 120033 1996-04-01   1996-10-01
111223333 120033 1996-04-01   1996-10-01
111223333 120033 1996-10-01   1998-01-01
111223333 120033 1997-12-01   1998-01-01

Coalescing은 두 가지 방식으로 수행될 수 있다.

중복 제거와 함께 병합:

SSN       PCN    START_DATE   END_DATE
111223333 120033 1996-01-01   1998-01-01

중복 제거 없이 병합:

SSN       PCN    START_DATE   END_DATE
111223333 120033 1996-01-01   1996-06-01
111223333 120033 1996-04-01   1996-10-01
111223333 120033 1996-04-01   1998-01-01
111223333 120033 1997-12-01   1998-01-01

1.6.3 Coalescing 구현방법

프로시저 사용

PROCEDURE Do_COALESCE() LANGUAGE SQL;
BEGIN
  -- 임시 테이블 생성
  CREATE TABLE Temp(SSN CHAR(9), PCN INT,
    START_DATE DATE, END_DATE DATE);
    
  -- 데이터 복사
  INSERT INTO Temp
  SELECT * FROM INCUMBENTS;
  
  -- 종료 날짜 확장
  LOOP
    UPDATE Temp AS T1
    SET END_DATE = (
      SELECT MAX(T2.END_DATE)
      FROM Temp AS T2
      WHERE T1.SSN = T2.SSN 
      AND T1.PCN = T2.PCN
      AND T1.START_DATE < T2.START_DATE
      AND T1.END_DATE >= T2.START_DATE
      AND T1.END_DATE < T2.END_DATE
    )
    WHERE EXISTS (...);
  END LOOP;
  
  -- 포함된 행 제거
  DELETE FROM Temp AS T1
  WHERE EXISTS (
    SELECT * FROM Temp AS T2
    WHERE T1.SSN = T2.SSN 
    AND T1.PCN = T2.PCN
    AND ((T1.START_DATE > T2.START_DATE
          AND T1.END_DATE <= T2.END_DATE)
    OR (T1.START_DATE >= T2.START_DATE
        AND T1.END_DATE < T2.END_DATE))
  );
END;

COUNT

CREATE VIEW V1 (SSN, PCN, START_DATE, END_DATE)
AS SELECT F.SSN, F.PCN, F.START_DATE, L.END_DATE
FROM INCUMBENTS AS F, INCUMBENTS AS L, INCUMBENTS AS E
WHERE F.END_DATE <= L.END_DATE
AND F.SSN = L.SSN AND F.SSN = E.SSN
AND F.PCN = L.PCN AND F.PCN = E.PCN
GROUP BY F.SSN, F.PCN, F.START_DATE, L.END_DATE
HAVING COUNT(CASE
    WHEN (E.START_DATE < F.START_DATE
    AND F.START_DATE <= E.END_DATE)
    OR (E.START_DATE <= L.END_DATE
    AND L.END_DATE < E.END_DATE)
    THEN 1 END) = 0;

커서 사용

CREATE TABLE Temp(SSN CHAR(9), PCN INT, START_DATE DATE, END_DATE DATE);

DECLARE CURSOR INC_CURSOR IS
  SELECT *
  FROM INCUMBENTS
  ORDER BY SSN, PCN, START_DATE;  -- 정렬된 순서가 중요!

DECLARE
  StartRow INC_CURSOR%ROWTYPE;  -- 병합 시작 행
  PrevRow INC_CURSOR%ROWTYPE;   -- 이전 행
  CurrRow INC_CURSOR%ROWTYPE;   -- 현재 행
BEGIN
  OPEN INC_CURSOR;
  
  -- 첫 번째 행을 읽어서 시작행과 이전행으로 설정
  FETCH INC_CURSOR INTO PrevRow;
  StartRow := PrevRow;
  
  -- 두 번째 행부터 처리 시작
  FETCH INC_CURSOR INTO CurrRow;
  
  WHILE INC_CURSOR%FOUND LOOP
    -- 새로운 그룹 시작 또는 간격이 있는 경우
    IF (StartRow.SSN <> CurrRow.SSN OR 
        StartRow.PCN <> CurrRow.PCN OR
        PrevRow.END_DATE < CurrRow.START_DATE)
    THEN
      -- 현재까지의 병합된 기간을 저장
      INSERT INTO Temp VALUES (
        StartRow.SSN, 
        StartRow.PCN,
        StartRow.START_DATE,
        PrevRow.END_DATE
      );
      -- 새로운 병합 그룹 시작
      StartRow := CurrRow;
    END IF;
    
    PrevRow := CurrRow;
    FETCH INC_CURSOR INTO CurrRow;
  END LOOP;
  
  -- 마지막 그룹 처리
  INSERT INTO Temp VALUES (
    StartRow.SSN, 
    StartRow.PCN,
    StartRow.START_DATE,
    PrevRow.END_DATE
  );
  
  CLOSE INC_CURSOR;
END;

2 Chapter 6: Querying State Tables Summary

2.1 1. 쿼리의 기본 유형

2.1.1 Current Query (현재 상태 쿼리)

"지금 무엇이 참인가?"에 대한 쿼리
현재 상태를 추출하는 가장 일반적인 쿼리 유형
간단한 WHERE 절 추가로 구현 (START_DATE <= CURRENT_DATE AND CURRENT_DATE < END_DATE)

2.1.2 Time-slice Query (시점 상태 쿼리)

특정 시점의 상태를 추출하는 쿼리
Current Query와 유사하지만 CURRENT_DATE 대신 특정 날짜 사용
과거나 미래의 특정 시점 상태 조회 가능

2.1.3 Sequenced Query (시퀀스 쿼리)

"언제부터 언제까지 무엇이 참이었는가?"에 대한 쿼리
시간에 따른 이력 정보를 추출
구현이 복잡할 수 있음

2.2 2. 주요 연산별 구현 방법

2.2.1 간단한 연산

Selection: 변경 불필요 (WHERE 절 그대로 사용)
Projection: 타임스탬프 컬럼 포함
Union: UNION ALL 사용 (중복이 없는 경우)
Sorting: 타임스탬프 컬럼을 정렬 키에 포함

2.2.2 복잡한 연산

Sequenced Join

-- 4가지 케이스 처리 필요
1. 첫 번째 테이블 기간이 두 번째 테이블 기간에 포함
2. 기간이 겹치지 않음
3. 첫 번째 케이스의 반대
4. 두 번째 케이스의 반대

Sequenced EXCEPT
- NOT EXISTS를 사용한 구현
- 복잡한 시간 관계 처리 필요

2.3 3. 중복 제거 (Duplicate Elimination)

2.3.1 기본적인 중복 제거

-- 비시퀀스 중복 제거
SELECT DISTINCT * FROM TABLE;

-- 값-동등성 중복 제거
SELECT DISTINCT columns_except_dates FROM TABLE;

-- 현재 중복 제거
SELECT DISTINCT columns 
FROM TABLE 
WHERE END_DATE = DATE '3000-01-01';

2.3.2 Coalescing (병합)

PSM 사용 구현
- 종료 날짜 확장
- 포함된 행 제거
- 중복 제거
COUNT 사용 구현
- GROUP BY와 HAVING 절 활용
- 확장 가능한 기간 확인
커서 기반 구현
- 정렬된 데이터 순차 처리
- 메모리 효율적
- 단일 패스 처리

2.4 4. 구현 시 고려사항

2.4.1 성능 최적화

적절한 인덱스 사용
중간 결과 최소화
DBMS 특성 활용

2.4.2 데이터 정합성

시간 관계의 정확한 처리
NULL 값 처리
엣지 케이스 관리

2.4.3 구현 선택

데이터 크기
쿼리 패턴
DBMS 기능 지원
유지보수 용이성

이러한 개념들은 시간 데이터를 다루는 애플리케이션 개발에서 핵심적인 역할을 하며, 상황에 따라 적절한 접근 방식을 선택하는 것이 중요합니다.