Oracle 실습 — 안과 정기검진 데이터 모델 조회 (PIVOT vs CASE)

✨ 실습으로 만든 안과 정기검진 모델과, 환자×방문(CHECKUP) 단위로 검사1~검사10 소요시간(분)을 두 가지 방식(PIVOT, CASE+집계)으로 조회한 내용을 정리합니다 🎉

조인은 모두 Classic Oracle 전통 조인 문법을 사용했습니다.

1) 🧩 E-R Diagram

PATIENT 👤
├─ ID (PK)
├─ NAME
└─ BIRTH_DATE

CHECKUP 🗓️
├─ ID (PK)
├─ PATIENT_ID (FK → PATIENT.ID)
├─ START_TIME
└─ END_TIME

CHECKUP_ITEM 🧪
├─ ID (PK)
├─ ITEM_CODE
├─ ITEM_NAME
└─ DISPLAY_NO  ← 검사1~검사10 고정 매핑

CHECKUP_LOG 📄
├─ ID (PK)
├─ CHECKUP_ID (FK → CHECKUP.ID)
├─ ITEM_ID    (FK → CHECKUP_ITEM.ID)
├─ START_TIME
├─ END_TIME
├─ STATUS        (예: 'DONE')
└─ RESULT_VALUE  (예: '정상', '0.8', 'TBUT 8s'…)

관계 🕸️

👤 PATIENT        1 ──── N  🗓️ CHECKUP
🗓️ CHECKUP        1 ──── N  📄 CHECKUP_LOG
🧪 CHECKUP_ITEM   1 ──── N  📄 CHECKUP_LOG

---

2) 🏗️ 스키마 DDL (제약은 최소화한 실습용)

-- 1. 환자
CREATE TABLE PATIENT (
  ID         NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  NAME       VARCHAR2(100) NOT NULL,
  BIRTH_DATE DATE
);

-- 2. 검진(방문) — 한 번의 방문에서 여러 검사 항목을 수행
CREATE TABLE CHECKUP (
  ID         NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  PATIENT_ID NUMBER NOT NULL,
  START_TIME DATE NOT NULL,
  END_TIME   DATE NOT NULL
);

-- 3. 검사 항목 기준 (검사1~검사10은 DISPLAY_NO로 고정 매핑)
CREATE TABLE CHECKUP_ITEM (
  ID         NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  ITEM_CODE  VARCHAR2(30),
  ITEM_NAME  VARCHAR2(100) NOT NULL,
  DISPLAY_NO NUMBER NOT NULL
);

-- 4. 검사 로그 (항목별 시작/종료/상태/결과값)
CREATE TABLE CHECKUP_LOG (
  ID         NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  CHECKUP_ID NUMBER NOT NULL,
  ITEM_ID    NUMBER NOT NULL,
  START_TIME DATE NOT NULL,
  END_TIME   DATE NOT NULL,
  STATUS     VARCHAR2(20),     -- 예: 'DONE'
  RESULT_VALUE VARCHAR2(200)   -- 예: '정상', '0.8', 'TBUT 8s' 등
);

실습 편의를 위해 외래키/인덱스는 생략. 실 운영시, CHECKUP(PATIENT_ID), CHECKUP_LOG(CHECKUP_ID), CHECKUP_LOG(ITEM_ID), CHECKUP_ITEM(DISPLAY_NO) 등에 인덱스 고려.

3) 🔍 조회: 환자×방문별 검사1~10 소요시간(분): 환자×방문별 검사1~10 소요시간(분)

핵심 계산식
ROUND((cl.END_TIME - cl.START_TIME) * 1440, 1) → DATE 차이를 분 단위로 변환

A. PIVOT (전통 조인 + 인라인뷰)

SELECT
  환자명, 검진ID, 검진일자, 검진_시작, 검진_종료, 검진_소요뷴,
  "검사1_소요분","검사2_소요분","검사3_소요분","검사4_소요분","검사5_소요분",
  "검사6_소요분","검사7_소요분","검사8_소요분","검사9_소요분","검사10_소요분"
FROM 
(
	 SELECT
	 	  P.ID								  AS 환자ID,
	      P.NAME                              AS 환자명,
	      C.ID                                AS 검진ID,
	      TO_CHAR(C.START_TIME,'YYYY-MM-DD')  AS 검진일자,
	      TO_CHAR(C.START_TIME,'HH24:MI')     AS 검진_시작,
	      TO_CHAR(C.END_TIME,  'HH24:MI')     AS 검진_종료,
	      ROUND((C.END_TIME - C.START_TIME) * 1440, 1) AS 검진_소요뷴,
	      CI.ID                     		  AS 검사항목ID,
	      ROUND((CL.END_TIME - CL.START_TIME) * 1440, 1) AS 항목_소요분
	  FROM PATIENT P, 
	  	   CHECKUP C,
	  	   CHECKUP_LOG CL,
	       CHECKUP_ITEM CI
	  WHERE P.ID = C.PATIENT_ID
	    AND C.ID = CL.CHECKUP_ID
	    AND CL.ITEM_ID = CI.ID
	    AND CL.STATUS = 'DONE'
	  ORDER BY P.ID, C.ID, CI.DISPLAY_NO 
) DATA

PIVOT 
(
	  MAX(항목_소요분) FOR 검사항목ID IN (
	     1  AS "검사1_소요분",
	     2  AS "검사2_소요분",
	     3  AS "검사3_소요분",
	     4  AS "검사4_소요분",
	     5  AS "검사5_소요분",
	     6  AS "검사6_소요분",
	     7  AS "검사7_소요분",
	     8  AS "검사8_소요분",
	     9  AS "검사9_소요분",
	     10 AS "검사10_소요분"
	  )
)
ORDER BY 환자명, 검진ID;

B. CASE + 집계 (전통 조인 + GROUP BY)

SELECT
    환자명,
    검진ID,
    검진일자,
    검진_시작,
    검진_종료,
    MAX(검진_소요분) AS 검진_소요분,
    MAX(CASE WHEN 검사항목ID = 1  THEN 항목_소요분 END) AS "검사1_소요분",
    MAX(CASE WHEN 검사항목ID = 2  THEN 항목_소요분 END) AS "검사2_소요분",
    MAX(CASE WHEN 검사항목ID = 3  THEN 항목_소요분 END) AS "검사3_소요분",
    MAX(CASE WHEN 검사항목ID = 4  THEN 항목_소요분 END) AS "검사4_소요분",
    MAX(CASE WHEN 검사항목ID = 5  THEN 항목_소요분 END) AS "검사5_소요분",
    MAX(CASE WHEN 검사항목ID = 6  THEN 항목_소요분 END) AS "검사6_소요분",
    MAX(CASE WHEN 검사항목ID = 7  THEN 항목_소요분 END) AS "검사7_소요분",
    MAX(CASE WHEN 검사항목ID = 8  THEN 항목_소요분 END) AS "검사8_소요분",
    MAX(CASE WHEN 검사항목ID = 9  THEN 항목_소요분 END) AS "검사9_소요분",
    MAX(CASE WHEN 검사항목ID = 10 THEN 항목_소요분 END) AS "검사10_소요분"
FROM (
    SELECT
          P.ID                                 AS 환자ID,
          P.NAME                               AS 환자명,
          C.ID                                 AS 검진ID,
          TO_CHAR(C.START_TIME,'YYYY-MM-DD')   AS 검진일자,
          TO_CHAR(C.START_TIME,'HH24:MI')      AS 검진_시작,
          TO_CHAR(C.END_TIME,  'HH24:MI')      AS 검진_종료,
          ROUND((C.END_TIME - C.START_TIME) * 1440, 1) AS 검진_소요분,
          CI.ID                                 AS 검사항목ID,
          ROUND((CL.END_TIME - CL.START_TIME) * 1440, 1) AS 항목_소요분
    FROM PATIENT P,
         CHECKUP C,
         CHECKUP_LOG CL,
         CHECKUP_ITEM CI
    WHERE P.ID = C.PATIENT_ID
      AND C.ID = CL.CHECKUP_ID
      AND CL.ITEM_ID = CI.ID
      AND CL.STATUS = 'DONE'
) DATA
GROUP BY
    환자명, 검진ID, 검진일자, 검진_시작, 검진_종료
ORDER BY
    환자명, 검진ID;

PIVOT vs CASE

• PIVOT: DISPLAY_NO가 1~10으로 고정일 때 가독성 좋음.
• CASE: 범용적이며 옵티마이저가 단순 집계로 풀기 쉬워 대용량 안정성이 좋음.
• 빈 칸을 0으로 보이고 싶다면 SELECT에서 "검사1_소요분" 등 컬럼에 NVL(..., 0) 감싸서 사용

결과 예시

| 환자명 | 검진ID | 검진일자 | 검진_시작 | 검진_종료 | 검진_소요분 | 검사1_소요분 | 검사2_소요분 | 검사3_소요분 | 검사4_소요분 | 검사5_소요분 | 검사6_소요분 | 검사7_소요분 | 검사8_소요분 | 검사9_소요분 | 검사10_소요분 | | —— | —— | ———- | ——— | ——— | ———– | ———— | ———— | ———— | ———— | ———— | ———— | ———— | ———— | ———— | ————- | | 김영희 | 6 | 2025-09-02 | 14:00 | 15:30 | 90 | 7 | 12 | 14 | 14 | 9 | 6 | 6 | 7 | 7 | 9 | | 김철수 | 3 | 2025-09-06 | 09:30 | 10:20 | 50 | 15 | 20 | | | | | | | | | | 홍길동 | 1 | 2025-09-01 | 09:00 | 10:00 | 60 | 10 | 10 | 20 | | | | | | | | | 홍길동 | 2 | 2025-09-06 | 14:00 | 15:20 | 80 | 12 | 15 | 25 | | | | | | | | —

📝 메모

• 실제 화면 UI는 검사1~검사10 고정 컬럼 구조이므로 피벗된 스키마가 적합.
• 로그성 테이블이 커질수록 CHECKUP_LOG(CHECKUP_ID, ITEM_ID, STATUS) 조합의 선택도를 높이는 인덱스가 유효.