「결제수단별 월 주문 건수」 표의 카드, 계좌이체, 간편결제 세 칸이 매달 똑같은 숫자다

기초SELECT이커머스370 / 700

이 문제는 교재 18화 졸업 과제와 같은 이커머스 데이터베이스를 씁니다. 『튜너의 1년』을 순서대로 읽고 있다면 졸업 과제를 먼저 풀고 오세요. 아래 「테이블과 인덱스」 칸에 과제의 답 일부(제약 조건, 인덱스, 통계의 실제 상태)가 나옵니다.

상황

운영팀의 월간 보고 「결제수단별 주문 건수」는 2024년 달마다 한 줄에 결제수단 세 칸을 보여 준다. 재무팀이 PG사 정산서와 맞춰 보다가, 세 칸이 매달 똑같은 숫자이고 세 칸을 더하면 그달 주문 수의 세 배가 된다는 것을 찾았다. 보고서를 처음 만든 지 넉 달째다. 개발자는 "CASE로 결제수단마다 나눠 셌는데 왜 같은 숫자가 나오는지 모르겠다"라고 한다.

테이블과 인덱스

orders: 34,908행 · 193블록 · order_date DATE(시분초 있음, 2020 ~ 2024) · 2024년 8,113건 · PK만
payments: 34,908행 · 192블록 · order_id와 1:1 · payment_method NOT NULL 3종(이 문제에서는 'CARD' 약 61% · 'BANK' 약 24% · 'EASY' 약 15%) · PK만
통계 최신 · 보고서는 한 달에 한 번 돌고, 양식을 고칠 때 몇 번 더 돈다

원본 SQL

sql

SELECT TO_CHAR(o.order_date, 'YYYY-MM') AS ym,
       COUNT(CASE WHEN p.payment_method = 'CARD' THEN 1 ELSE 0 END) AS card_cnt,
       COUNT(CASE WHEN p.payment_method = 'BANK' THEN 1 ELSE 0 END) AS bank_cnt,
       COUNT(CASE WHEN p.payment_method = 'EASY' THEN 1 ELSE 0 END) AS easy_cnt
  FROM orders o, payments p
 WHERE p.order_id = o.order_id
   AND o.order_date >= DATE '2024-01-01'
   AND o.order_date <  DATE '2025-01-01'
 GROUP BY TO_CHAR(o.order_date, 'YYYY-MM')
 ORDER BY ym

실행계획 학습용 예시

xplan

-----------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name     | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Used-Mem |
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |          |      1 |        |     12 |00:00:00.02 |     385 |          |
|   1 |  SORT GROUP BY     |          |      1 |   8113 |     12 |00:00:00.02 |     385 | 2048  (0)|
|*  2 |   HASH JOIN        |          |      1 |   8113 |   8113 |00:00:00.02 |     385 | 1461K (0)|
|*  3 |    TABLE ACCESS FULL| ORDERS  |      1 |   8113 |   8113 |00:00:00.01 |     193 |          |
|   4 |    TABLE ACCESS FULL| PAYMENTS|      1 |  34908 |  34908 |00:00:00.01 |     192 |          |
-----------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("P"."ORDER_ID"="O"."ORDER_ID")
   3 - filter(("O"."ORDER_DATE">=TO_DATE(' 2024-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
              "O"."ORDER_DATE"<TO_DATE(' 2025-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')))

[결과 · 앞의 세 달]
  ym        card_cnt  bank_cnt  easy_cnt
  2024-01        688       688       688
  2024-02        641       641       641
  2024-03        702       702       702

문제

세 칸이 같은 숫자가 되는 까닭을 COUNT가 세는 기준으로 설명하시오.
결제수단별 건수가 맞게 나오도록 SQL을 고치고, CASE로 쓰는 법과 PIVOT으로 쓰는 법을 견주시오.
고친 뒤 결과를 무엇과 맞춰 보면 되는지, 읽는 블록 수는 어떻게 되는지 쓰시오.

개선 SQL원본 SQL을 고쳐 쓰세요. 인덱스 DDL과 힌트도 쓸 수 있습니다.

개선 사유무엇이 문제였고 왜 이렇게 고쳤는지 쓰세요.0자

모범답안

모범 답안 SQL

sql

-- COUNT 는 NULL 이 아닌 값을 센다 → 해당하지 않는 행은 NULL 이 되게 ELSE 를 뺀다
SELECT TO_CHAR(o.order_date, 'YYYY-MM') AS ym,
       COUNT(CASE WHEN p.payment_method = 'CARD' THEN 1 END) AS card_cnt,
       COUNT(CASE WHEN p.payment_method = 'BANK' THEN 1 END) AS bank_cnt,
       COUNT(CASE WHEN p.payment_method = 'EASY' THEN 1 END) AS easy_cnt
  FROM orders o, payments p
 WHERE p.order_id = o.order_id
   AND o.order_date >= DATE '2024-01-01'
   AND o.order_date <  DATE '2025-01-01'
 GROUP BY TO_CHAR(o.order_date, 'YYYY-MM')
 ORDER BY ym;

-- 같은 답 · PIVOT 판 (칸마다 그 값인 행만 센다)
-- SELECT *
--   FROM (SELECT TO_CHAR(o.order_date, 'YYYY-MM') AS ym, p.payment_method
--           FROM orders o, payments p
--          WHERE p.order_id = o.order_id
--            AND o.order_date >= DATE '2024-01-01' AND o.order_date < DATE '2025-01-01')
--  PIVOT (COUNT(*) FOR payment_method IN ('CARD' AS card_cnt, 'BANK' AS bank_cnt, 'EASY' AS easy_cnt))
--  ORDER BY ym;

개선 후 실행계획 학습용 예시

xplan

-----------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name     | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Used-Mem |
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |          |      1 |        |     12 |00:00:00.02 |     385 |          |
|   1 |  SORT GROUP BY     |          |      1 |   8113 |     12 |00:00:00.02 |     385 | 2048  (0)|
|*  2 |   HASH JOIN        |          |      1 |   8113 |   8113 |00:00:00.02 |     385 | 1461K (0)|
|*  3 |    TABLE ACCESS FULL| ORDERS  |      1 |   8113 |   8113 |00:00:00.01 |     193 |          |
|   4 |    TABLE ACCESS FULL| PAYMENTS|      1 |  34908 |  34908 |00:00:00.01 |     192 |          |
-----------------------------------------------------------------------------------------------------------

[결과 · 앞의 세 달]
  ym        card_cnt  bank_cnt  easy_cnt   (합 = 그달 주문 수)
  2024-01        420       165       103   688
  2024-02        392       153        96   641
  2024-03        427       170       105   702

모범 답안 서술

COUNT(식)은 식의 값이 NULL이 아닌 행을 센다. CASE WHEN p.payment_method = 'CARD' THEN 1 ELSE 0 END는 카드면 1, 아니면 0을 돌려주는데, 0도 NULL이 아니므로 COUNT는 0인 행도 한 건으로 센다. 그래서 세 칸 모두 그달 주문을 전부 센 숫자(1월 688)가 되었다. 1번 줄 SORT GROUP BY가 12개 묶음을 만들면서 세 칸에 같은 일을 시킨 셈이다. ELSE 0을 빼서 COUNT(CASE WHEN … THEN 1 END)로 쓰면 카드가 아닌 행은 NULL이 되어 세지 않는다. SUM(CASE WHEN … THEN 1 ELSE 0 END)로 더해도 같다. PIVOT (COUNT(*) FOR payment_method IN ('CARD' AS card_cnt, …))으로 쓰면 칸마다 그 값인 행만 세므로 이런 실수가 생길 자리가 없다. CASE 쪽은 칸을 마음대로 만들 수 있고(예: 카드 가운데 할부만), PIVOT 쪽은 값 목록이 정해진 표를 짧게 쓸 수 있다. 셋 모두 orders와 payments를 한 번씩 읽는 같은 계획이라 Buffers는 385 그대로다. 고친 뒤에는 세 칸을 더한 값이 그달 주문 수와 같은지(1월 420 + 165 + 103 = 688) 확인하고, 결제수단별 숫자는 PG사 정산서의 수단별 건수와 맞춰 본다. 넉 달 동안 나간 보고서도 다시 뽑아 고쳐 보낸다.

해설

세 번 읽던 판으로 돌아가지 않는다: 결제수단마다 WHERE payment_method = 'CARD' SQL을 따로 돌리면 숫자는 맞지만 두 테이블을 세 번 읽는다. 한 번 읽고 칸마다 나눠 세는 지금 구조는 그대로 두고 세는 법만 고친다.
AVG도 같은 함정이 있다: AVG(CASE WHEN … THEN total_amount ELSE 0 END)는 해당하지 않는 행의 0까지 넣어 나누므로 평균이 작게 나온다. 평균은 ELSE를 빼서 NULL이 되게 한다. SUM만 ELSE 0이 있어도 답이 같다.
이 보고서는 넉 달 동안 틀린 숫자를 냈지만 아무도 몰랐다. 칸을 나눠 센 표에는 '칸의 합 = 전체 건수' 같은 대조 줄을 함께 두면 이런 실수를 바로 잡을 수 있다(교재 08화 §10).

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

개선 후 실행계획 학습용 예시 ​

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황