오즈코딩스쿨 AI 헬스케어 3기 SQL 특강(2026-10-06 ~ 10-08, 3일)에서 진행한 실습과 과제를 정리한 저장소입니다. 헬스케어 현장에서 실제로 받는 질문("이번 달 외래 중 금액이 큰 청구는?", "폐암 환자의 1년 사망률이 진단 연도에 따라 달라졌나?")에 SQL로 답하는 연습을 했습니다.
| 데이터베이스 | 내용 | 주요 테이블 |
|---|---|---|
kr_claims |
건강보험 청구(명세서) 자료를 본뜬 실습용 데이터. 2024-01-01 ~ 2025-12-31. 명세서·수진자·요양기관은 가상 기록이고, 상병마스터·코드표·병원 목록은 심평원 공개 자료 | claim(38,375건), patient, institution, sick_master, code_table, hospital_open |
kr_lung_cancer |
국립암센터 "폐암 다기관 암 임상 라이브러리 합성데이터"(공공데이터포털) | lung_pt_bsnf(환자 9,897명), lung_oprt_nfrm, lung_trtm_casb, lung_trtm_rd, lung_dead_nfrm |
두 데이터 모두 실제 환자 기록이 아닙니다. 아래 숫자를 실제 통계나 치료 성적으로 해석하지 않습니다.
sql/
├── practice_1-5.sql # 수업 실습 1~5
├── assignment_1.sql # 과제 1. 2025년 7월 외래 명세서 읽기
├── assignment_2.sql # 과제 2. 상병별 집계와 당뇨병 환자의 방문 간격
└── assignment_3.sql # 과제 3. 병기 그룹별 3년 안 사망률
- SQL
- MySQL 8.4 (LTS)
- DBeaver (SQL 편집기)
- 데이터 조회:
SELECT,ORDER BY,LIMIT으로 명세서 훑어보기 - 조건 검색:
WHERE,BETWEEN,LIKE,IN,IS NOT NULL - GROUP BY: 종별·상병별·병기별 집계,
HAVING으로 그룹 거르기 - JOIN: 명세서 + 상병마스터 + 요양기관 + 코드표 다중 조인, 사망 기록을 붙이는
LEFT JOIN - 데이터 집계:
COUNT,COUNT(DISTINCT),SUM,AVG, 비율 계산 - 윈도우 함수:
ROW_NUMBER,LAG, 누적SUM ... OVER로 환자별 방문 순번·간격·누적 진료비 - CTE(
WITH): 코호트 정의 → 결과(outcome) 계산 → 집계로 단계 나누기 - CASE: 병기값을 4개 그룹으로 묶기, 0/1 플래그로 비율 계산
| 실습 | 현장의 질문 | 핵심 구문 |
|---|---|---|
| 실습 1 | 2025년 12월 외래 청구 중 금액이 큰 건은 무슨 병이었나 | WHERE, JOIN, ORDER BY |
| 실습 2 | 큰 병원일수록 건당 진료비와 본인부담률이 얼마나 다른가 | GROUP BY, HAVING, 다중 JOIN |
| 실습 3 | 고혈압 환자 중 석 달 넘게 병원을 비운 사람은 누구인가 | 윈도우 함수, WITH |
| 실습 4 | 폐암 1년 사망률이 진단 연도에 따라 달라졌나 | CTE, LEFT JOIN |
| 실습 5 | 병기별로 수술·항암·방사선을 받은 비율은 | CASE |
윈도우 함수는 WHERE 보다 나중에 계산되므로 WHERE 안에서 LAG 를 바로 쓸 수 없습니다(ERROR 3593). CTE로 먼저 간격을 계산해 이름을 붙이고 바깥에서 거릅니다.
WITH v AS (
SELECT jid, recu_fr_dd,
ROW_NUMBER() OVER (PARTITION BY jid ORDER BY recu_fr_dd) AS visit_no,
DATEDIFF(recu_fr_dd, LAG(recu_fr_dd) OVER (PARTITION BY jid ORDER BY recu_fr_dd)) AS gap_days,
SUM(rvd_rpe_tamt_amt) OVER (PARTITION BY jid ORDER BY recu_fr_dd) AS cum_amt
FROM claim
WHERE main_sick = 'E119' AND fom_tp_cd = '031'
)
SELECT * FROM v
WHERE gap_days > 90
ORDER BY gap_days DESC;코호트(누구를) → 결과(무슨 일이) → 집계(얼마나) 순서로 CTE를 나눴습니다.
사망 기록이 없는 환자도 분모에 남아야 하므로 JOIN 이 아니라 LEFT JOIN 을 씁니다.
WITH cohort AS (
SELECT pt_sbst_no, bspt_frst_diag_ymd AS index_dd,
CASE WHEN bspt_stag_vl IN ('0', '1', '2') THEN '1) 초기 (0~2기)'
WHEN bspt_stag_vl = '3' THEN '2) 국소 진행 (3기)'
WHEN bspt_stag_vl = '4' THEN '3) 전이 (4기)'
ELSE '4) 병기 미상' END AS stage_group
FROM lung_pt_bsnf
WHERE bspt_frst_diag_ymd >= '2010-01-01' AND bspt_frst_diag_ymd < '2018-01-01'
),
outcome AS (
SELECT c.pt_sbst_no, c.stage_group,
CASE WHEN d.dead_ymd <= c.index_dd + INTERVAL 1095 DAY THEN 1 ELSE 0 END AS dead_3y
FROM cohort c
LEFT JOIN lung_dead_nfrm d ON d.pt_sbst_no = c.pt_sbst_no
)
SELECT stage_group, COUNT(*) AS patients, SUM(dead_3y) AS dead_3y,
ROUND(AVG(dead_3y) * 100, 1) AS dead_3y_pct
FROM outcome
GROUP BY stage_group
ORDER BY stage_group;나머지 쿼리는 sql/ 폴더에 있습니다.
| 문제 | 원인 | 해결 |
|---|---|---|
DBeaver에서 MySQL 연결 실패 (Public Key Retrieval is not allowed) |
MySQL 8의 기본 인증 방식 | 연결 우클릭 → Edit Connection → Driver Properties → allowPublicKeyRetrieval 을 true 로 변경 |
고혈압 명세서가 0건 (main_sick = 'I10') — 에러 없음 |
데이터에는 세부 코드 I109 로 저장됨 |
sick_master 에서 LIKE 'I10%' 로 실제 값을 확인한 뒤 I109 사용. 결과가 0행이면 쿼리보다 값부터 확인 |
ERROR 1055 ... not in GROUP BY clause |
only_full_group_by 모드에서 SELECT에 묶이지 않은 열(main_sick)이 있음 |
그 열을 GROUP BY 에 넣거나 COUNT(DISTINCT ...) 같은 집계 함수로 감쌈 |
ERROR 3593 You cannot use the window function 'lag' in this context |
윈도우 함수는 WHERE 이후에 계산됨 |
CTE(WITH v AS (...))에서 먼저 계산하고 바깥 쿼리에서 거름 |
| 1년 사망률이 두 배 가까이 높게 나옴 (2010년 31.4% → 48.4%) — 에러 없음 | JOIN 이 사망 기록 없는 환자를 통째로 제외해 분모가 8,436명 → 3,293명으로 줄어듦 |
LEFT JOIN 으로 변경. 단계마다 행 수를 세어 코호트 인원과 맞는지 확인 |
| 병기 그룹이 4개가 아니라 3개, 초기 그룹이 3,813명이 아니라 4,163명 (Warning 1292) | 문자 열 bspt_stag_vl 을 숫자와 비교하자 'x' 가 0으로 바뀌어 초기 그룹에 섞임 |
문자는 문자로 비교: IN ('0', '1', '2'). 합계뿐 아니라 그룹 수와 값별 인원도 함께 확인 |
모든 숫자는 로컬 MySQL 8.4.11에서 직접 실행해 확인했습니다.
-
실습 1: 2025년 12월 외래 1,913건 중 진료비 1위는 명세서 5032853, C509(유방의 악성 신생물) 1,175,300원
-
실습 2: 종별 건당 진료비와 본인부담률
종별 명세서 수 건당 진료비(원) 본인부담률(%) 상급종합병원 2,050 499,558 19.8 종합병원 3,255 262,177 21.0 병원 2,742 118,056 22.5 의원 30,328 36,019 27.2 큰 병원일수록 건당 진료비는 높고 본인부담률은 낮습니다.
-
실습 3: 고혈압 외래 환자 423명(4,402건) 중 직전 방문과 90일 넘게 벌어진 방문은 6건, 최장 100일(환자 100707). 단, 마지막 방문 뒤 돌아오지 않은 환자 69명은
LAG로는 잡히지 않음 -
실습 4: 2010~2019 진단 코호트 8,436명의 1년 안 사망률은 2010년 31.4%에서 2019년 11.2%로 낮아짐. 뒤 연도일수록 추적이 끊긴 환자가 많아 생기는 착시일 수 있음
-
실습 5: 초기(0~2기)는 수술 74.1%, 3기·4기는 항암(67.1%, 68.4%)과 방사선(48.5%, 38.5%) 비율이 높음
- Q1: 2025년 7월 외래 명세서 1,450건
- Q2: 진료비 1위는 명세서 5006454, N400(합병증을 동반하지 않은 전립선증식증) 3,182,000원
- Q3: 입내원일수 1위는 G819 상세불명의 편마비, 138일 (명세서 5016201, 2024-05-07 ~ 2024-09-21)
-
Q1: 2024년 외래 주상병 상위 5개
주상병 상병명 명세서 수 환자 수 1인당 명세서 수 J209 상세불명의 급성 기관지염 2,554 894 2.86 I109 기타 및 상세불명의 원발성 고혈압 2,226 393 5.66 E119 합병증을 동반하지 않은 2형 당뇨병 726 143 5.08 M545 요통 627 179 3.50 M170 양쪽 원발성 무릎관절증 598 114 5.25 과제 안내 페이지는 "2024년", 2일차 실습 문서는 "2025년"으로 적혀 있습니다. 여기서는 제출 안내 페이지 기준(2024년)으로 풀었습니다. 2025년 기준이면 1위는 J209 2,724건 / 991명입니다.
-
Q2: 명세서 500건 이상인 상병은 7개 (위 5개 + M480 척추협착 550건, J304 알레르기비염 547건)
-
Q3: 당뇨병(E119) 외래에서 90일을 넘은 방문은 1건, 94일 (환자 101084, 2025-05-23, 4번째 방문)
-
Q4: 고혈압·당뇨병·무릎관절증 같은 만성질환은 약을 계속 처방받으려고 정기적으로 내원해 환자 1명당 명세서가 5건 이상입니다. 급성 기관지염이나 알레르기비염처럼 짧게 앓고 끝나는 병은 한두 번 진료로 끝나 1명당 명세서가 2~3건에 그칩니다.
-
Q1: 2010-01-01 ~ 2017-12-31 진단 환자 6,873명
-
Q2:
병기 그룹 환자 수 3년 안 사망 3년 안 사망률(%) 1) 초기 (0~2기) 2,666 347 13.0 2) 국소 진행 (3기) 1,098 535 48.7 3) 전이 (4기) 2,879 1,838 63.8 4) 병기 미상 230 109 47.4 네 그룹 합계 6,873명으로 Q1과 같습니다.
-
Q3: 사망 기록은 2021-07-31까지 있습니다. 2017년 말 진단자도 3년(1095일) 뒤가 2020년 말이라 기록 범위 안에 들어오지만, 2018년 이후 진단자는 3년을 다 지켜보지 못한 사람이 섞여 사망률이 낮게 나오기 때문입니다.
-
Q4: 사망 기록이 없다고 3년을 살아남았다는 뜻은 아닙니다. 이 코호트 6,873명 중 3,138명(45.7%)은 사망 기록이 없고 마지막 내원이 진단 뒤 3년이 되기 전이라 생존 여부를 알 수 없는데, 쿼리는 이들을 모두 "생존"으로 셉니다. 따라서
100 - 사망률을 3년 생존율이라 부를 수 없고, 추적이 끊긴 환자를 따로 다루는 생존분석(Kaplan-Meier 등)이 필요합니다. 병원 밖 사망이 빠졌을 수 있고 합성데이터라는 점도 함께 밝혀야 합니다.
- 에러가 안 나도 틀릴 수 있다.
I10vsI109,JOINvsLEFT JOIN, 문자'x'와 숫자 비교 모두 에러 없이 잘못된 숫자를 냈습니다. 결과가 0행이면 값부터 보고, 단계마다 행 수를 세어 분모가 맞는지 확인하는 습관이 필요합니다. - 분모에 누가 있는지부터 묻는다. 비율은 분모가 바뀌면 전혀 다른 이야기가 됩니다. 코호트 범위를 정하고, 그 범위를 결과와 함께 적어야 합니다.
- 쿼리가 질문에 다 답했는지 확인한다.
LAG로 찾은 "90일 넘게 비운 사람"에는 돌아오지 않은 환자가 빠져 있었습니다. 실행 성공과 질문에 대한 답은 다릅니다. - SQL 실행 순서를 알면 에러가 읽힌다.
FROM → WHERE → GROUP BY → HAVING → SELECT(윈도우 함수) → ORDER BY순서를 알면 ERROR 1055, 3593 이 왜 나는지 이해됩니다. - CTE로 단계를 나누면 읽기 쉽다. 코호트 → 결과 → 집계로 나누니 긴 쿼리도 중간 결과를 하나씩 확인할 수 있었습니다.
- 청구 데이터와 임상 데이터는 답할 수 있는 질문이 다르다. 청구 자료는 돈과 방문을, 임상 자료는 진단·치료·사망을 기록합니다.
- 저장소: https://github.com/jihyeongh21-svg/nano-dree-sql
- 실습·과제 SQL:
sql/