상황: 커피매장과는 별개의 교육용 업무로, 회원별 월 요금 청구와 실제 분할 납부 내역을 관리한다. 회원 M1의 2024년 1월·2월 청구 및 납부를 조회한다. startdbpg에 실제로 생성한 일반 테이블을 사용하며 _big 테이블을 사용하지 않는다.
테이블 관계 (실제 PK·FK 설정 완료)
•
tr_mbr_fee_pay (회원요금결제): PRIMARY KEY (mbr_id, pay_ym); tot_amt=월 요금, unpaid_amt=미납금액.
•
tr_mbr_fee_pay_det (회원요금결제상세): PRIMARY KEY (mbr_id, pay_ym, pay_seq); pay_amt=실제 납부금액, pay_dt=납부일자.
•
상세에서 청구로 이어지는 복합 FK: (mbr_id, pay_ym) REFERENCES tr_mbr_fee_pay(mbr_id, pay_ym). 기존 ms_mbr와는 다른 업무를 가정하므로 해당 테이블로 연결되는 FK는 없다.
테이블 생성 SQL (실제 DB에서 실행 완료)
CREATE TABLE startdbpg.tr_mbr_fee_pay (
mbr_id varchar(40) NOT NULL,
pay_ym varchar(6) NOT NULL,
tot_amt numeric(18,3) NOT NULL,
unpaid_amt numeric(18,3) NOT NULL,
CONSTRAINT tr_mbr_fee_pay_pk PRIMARY KEY (mbr_id, pay_ym),
CONSTRAINT tr_mbr_fee_pay_ym_chk
CHECK (pay_ym ~ '^[0-9]{4}(0[1-9]|1[0-2])$'),
CONSTRAINT tr_mbr_fee_pay_amt_chk
CHECK (tot_amt >= 0 AND unpaid_amt >= 0 AND unpaid_amt <= tot_amt)
);
CREATE TABLE startdbpg.tr_mbr_fee_pay_det (
mbr_id varchar(40) NOT NULL,
pay_ym varchar(6) NOT NULL,
pay_seq integer NOT NULL,
pay_amt numeric(18,3) NOT NULL,
pay_dt date NOT NULL,
CONSTRAINT tr_mbr_fee_pay_det_pk PRIMARY KEY (mbr_id, pay_ym, pay_seq),
CONSTRAINT tr_mbr_fee_pay_det_fk01
FOREIGN KEY (mbr_id, pay_ym)
REFERENCES startdbpg.tr_mbr_fee_pay (mbr_id, pay_ym),
CONSTRAINT tr_mbr_fee_pay_det_amt_chk
CHECK (pay_seq > 0 AND pay_amt > 0)
);
SQL
복사
실습 데이터 (실제 입력 완료)
회원요금결제 mbr_id | pay_ym | tot_amt | unpaid_amt | |
M1 | 202401 | 10000 | 0 | |
M1 | 202402 | 10000 | 1000 | |
회원요금결제상세 mbr_id | pay_ym | pay_seq | pay_amt | pay_dt |
--- | --- | ---: | ---: | --- |
M1 | 202401 | 1 | 10000 | 2024-01-20 |
M1 | 202402 | 1 | 8000 | 2024-02-20 |
M1 | 202402 | 2 | 1000 | 2024-02-21 |
BAD SQL — 복합키에서 pay_ym 조인 조건 누락
SELECT p.mbr_id, p.pay_ym AS bill_ym,
d.pay_ym AS detail_ym, d.pay_seq, d.pay_amt
FROM startdbpg.tr_mbr_fee_pay p
JOIN startdbpg.tr_mbr_fee_pay_det d
ON d.mbr_id = p.mbr_id
WHERE p.mbr_id = 'M1'
ORDER BY p.pay_ym, d.pay_ym, d.pay_seq;
SQL
복사
회원ID만 연결하므로 월별 청구 2행 × 납부 상세 3행 = 6행이 발생한다. 1월 청구에 2월 납부 2건, 2월 청구에 1월 납부 1건이 잘못 결합된다.
GOOD SQL — 업무 관계에 필요한 복합키 두 컬럼 모두 JOIN
SELECT p.mbr_id, p.pay_ym AS bill_ym,
d.pay_ym AS detail_ym, d.pay_seq, d.pay_amt
FROM startdbpg.tr_mbr_fee_pay p
JOIN startdbpg.tr_mbr_fee_pay_det d
ON d.mbr_id = p.mbr_id
AND d.pay_ym = p.pay_ym
WHERE p.mbr_id = 'M1'
ORDER BY p.pay_ym, d.pay_ym, d.pay_seq;
SQL
복사
청구 월과 상세 월까지 일치시켜 정확히 3행만 연결한다.
실측 검증 결과: 월별 납부금액 집계 차이
청구월 | BAD: 결과행 | BAD: 납부합계 | GOOD: 결과행 | GOOD: 납부합계 |
202401 | 3 | 19000 (오류) | 1 | 10000 |
202402 | 3 | 19000 (오류) | 2 | 9000 |
합계 | 6 | 월별 중복 집계 | 3 | 월별 실제 납부 |
검증 SQL:
WITH joined AS (
SELECT 'BAD' AS sql_type, p.pay_ym AS bill_ym, d.pay_amt
FROM startdbpg.tr_mbr_fee_pay p
JOIN startdbpg.tr_mbr_fee_pay_det d
ON d.mbr_id = p.mbr_id
WHERE p.mbr_id = 'M1'
UNION ALL
SELECT 'GOOD' AS sql_type, p.pay_ym AS bill_ym, d.pay_amt
FROM startdbpg.tr_mbr_fee_pay p
JOIN startdbpg.tr_mbr_fee_pay_det d
ON d.mbr_id = p.mbr_id
AND d.pay_ym = p.pay_ym
WHERE p.mbr_id = 'M1'
)
SELECT sql_type, bill_ym,
COUNT(*) AS joined_rows, SUM(pay_amt) AS paid_sum
FROM joined
GROUP BY sql_type, bill_ym
ORDER BY sql_type, bill_ym;
SQL
복사
실제 실행계획 (EXPLAIN ANALYZE BUFFERS COSTS OFF): 아래는 전체 조회 컬럼(tot_amt, unpaid_amt, 납부일 포함)과 ORDER BY p.pay_ym, d.pay_ym, d.pay_seq를 사용하여 측정한 계획이다.
BAD SQL — 실제 계획
Sort (actual time=0.114..0.116 rows=6 loops=1)
Sort Key: p.pay_ym, d.pay_ym, d.pay_seq
Sort Method: quicksort Memory: 25kB
Buffers: shared hit=10
-> Nested Loop (actual time=0.028..0.034 rows=6 loops=1)
Buffers: shared hit=4
-> Bitmap Heap Scan on tr_mbr_fee_pay p (actual time=0.010..0.011 rows=2 loops=1)
Recheck Cond: ((mbr_id)::text = 'M1'::text)
Heap Blocks: exact=1
Buffers: shared hit=2
-> Bitmap Index Scan on tr_mbr_fee_pay_pk (actual time=0.004..0.004 rows=2 loops=1)
Index Cond: ((mbr_id)::text = 'M1'::text)
Buffers: shared hit=1
-> Materialize (actual time=0.008..0.010 rows=3 loops=2)
Buffers: shared hit=2
-> Bitmap Heap Scan on tr_mbr_fee_pay_det d (actual time=0.010..0.012 rows=3 loops=1)
Recheck Cond: ((mbr_id)::text = 'M1'::text)
Heap Blocks: exact=1
Buffers: shared hit=2
-> Bitmap Index Scan on tr_mbr_fee_pay_det_pk (actual time=0.005..0.006 rows=3 loops=1)
Index Cond: ((mbr_id)::text = 'M1'::text)
Buffers: shared hit=1
Planning:
Buffers: shared hit=126
Planning Time: 0.678 ms
Execution Time: 0.250 ms
Plain Text
복사
GOOD SQL — 실제 계획
Merge Join (actual time=0.027..0.038 rows=3 loops=1)
Merge Cond: ((d.pay_ym)::text = (p.pay_ym)::text)
Buffers: shared hit=4
-> Index Scan using tr_mbr_fee_pay_det_pk on tr_mbr_fee_pay_det d (actual time=0.011..0.012 rows=3 loops=1)
Index Cond: ((mbr_id)::text = 'M1'::text)
Buffers: shared hit=2
-> Index Scan using tr_mbr_fee_pay_pk on tr_mbr_fee_pay p (actual time=0.012..0.012 rows=2 loops=1)
Index Cond: ((mbr_id)::text = 'M1'::text)
Buffers: shared hit=2
Planning:
Buffers: shared hit=195
Planning Time: 1.055 ms
Execution Time: 0.232 ms
Plain Text
복사
실행계획 읽기: BAD는 Nested Loop가 청구 2행마다 납부 3행을 반복 결합하여 6행을 만들었다. GOOD에서는 Merge Cond: d.pay_ym = p.pay_ym으로 결제월까지 확인해 3행이 생성되었다. 작은 교육용 데이터이므로 두 실행시간(0.250ms·0.232ms)의 차이에 성능 의미를 부여하지 않는다.
핵심
•
복합 PK·FK 관계에서 JOIN에 필요한 키 일부를 빼면 같은 회원의 서로 다른 결제월 데이터까지 결합되어 조회 건수와 집계금액이 틀어진다. JOIN 조건을 적기 전 어떤 컬럼들의 조합이 한 업무 건을 식별하는지 확인하자.
참고·예외
•
모든 JOIN에 PK 컬럼 전체를 사용하라는 뜻은 아니다. 실제 업무 관계가 1:N이면 관련 FK 컬럼만으로 JOIN하는 것이 정상이다. 이 실습에서는 상세가 월 청구의 하위 데이터이므로 (mbr_id, pay_ym) 두 컬럼을 사용해야 한다. 참고로 복합 FK 제약이 DB에 있어도 SELECT의 잘못된 JOIN 조건을 자동으로 고쳐주지는 않는다. 두 테이블과 M1 데이터는 교육을 위해 신규 생성했으며 실제 운영 회원요금 수납 기록이 아니다.