MD 회의 자료의 「매장별 판매 TOP 3」, 같은 12월 데이터인데 뽑을 때마다 3위가 바뀐다

기초SELECT유통363 / 700

상황

본사 MD팀은 월간 회의 자료로 「매장별 판매 수량 TOP 3 상품」을 뽑는다. 12월 회의 전날 오과장이 같은 조건(2023년 12월)으로 두 번 뽑았는데, 1003 매장의 3위가 처음에는 바닐라라떼, 두 번째는 카라멜마키아토였다. 두 상품 모두 655개다. 회의 자료 작성 기준서에는 "수량이 같으면 공동 순위로 모두 싣고, 순위 번호는 1, 2, 2, 4처럼 매긴다"라고 적혀 있다. 개발자는 상위 N건을 뽑을 때 늘 쓰던 ROW_NUMBER를 썼다.

테이블과 인덱스

CF10 판매: 500,000행 · 4,583블록 · SLE_DT VARCHAR2(8) · SHP_NO 판매가 있는 매장 5곳(1001 ~ 1005) · 2023년 12월 21,264건
CF11 판매상품: 1,096,708행 · 8,204블록 · CF11_PK (SLE_NO, ITM_NO) · SLE_QTY / CF01 상품: 109행 · 5블록
인덱스 IX_CF10_SLE_DT (SLE_DT): BLEVEL 2 · 리프 1,393 · CF 9,921 · 통계 최신
2023년 12월 수량(일부): 1003 매장 (ICE)아메리카노 3,412 · 카페라떼 1,206 · 바닐라라떼 655 · 카라멜마키아토 655 · 카페모카 571 / 1005 매장 (ICE)아메리카노 2,980 · 카페라떼 701 · 바닐라라떼 701 · 카페모카 640 · 나머지 세 매장은 4위까지 동점이 없다

원본 SQL

sql

SELECT SHP_NO, rn AS RNK, ITM_NO, ITM_NM, QTY
  FROM (SELECT h.SHP_NO, d.ITM_NO, i.ITM_NM, SUM(d.SLE_QTY) AS QTY,
               ROW_NUMBER() OVER (PARTITION BY h.SHP_NO ORDER BY SUM(d.SLE_QTY) DESC) AS rn
          FROM CF10 h, CF11 d, CF01 i
         WHERE h.SLE_DT BETWEEN '20231201' AND '20231231'
           AND d.SLE_NO = h.SLE_NO
           AND i.ITM_NO = d.ITM_NO
         GROUP BY h.SHP_NO, d.ITM_NO, i.ITM_NM)
 WHERE rn <= 3
 ORDER BY SHP_NO, rn

실행계획 학습용 예시

xplan

----------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                         |                |      1 |        |     15 |00:00:00.42 |    8693 |          |
|*  1 |  VIEW                                    |                |      1 |    436 |     15 |00:00:00.42 |    8693 |          |
|*  2 |   WINDOW SORT PUSHED RANK                |                |      1 |    436 |     15 |00:00:00.42 |    8693 | 2048  (0)|
|   3 |    HASH GROUP BY                         |                |      1 |    436 |    436 |00:00:00.42 |    8693 | 1520K (0)|
|*  4 |     HASH JOIN                            |                |      1 |  46615 |  46640 |00:00:00.38 |    8693 | 1401K (0)|
|   5 |      TABLE ACCESS FULL                   | CF01           |      1 |    109 |    109 |00:00:00.01 |       5 |          |
|*  6 |      HASH JOIN                           |                |      1 |  46615 |  46640 |00:00:00.35 |    8688 | 1695K (0)|
|   7 |       TABLE ACCESS BY INDEX ROWID BATCHED| CF10           |      1 |  21250 |  21264 |00:00:00.01 |     484 |          |
|*  8 |        INDEX RANGE SCAN                  | IX_CF10_SLE_DT |      1 |  21250 |  21264 |00:00:00.01 |      62 |          |
|   9 |       TABLE ACCESS FULL                  | CF11           |      1 |   1096K|   1096K|00:00:00.12 |    8204 |          |
----------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("RN"<=3)
   2 - filter(ROW_NUMBER() OVER ( PARTITION BY "H"."SHP_NO" ORDER BY SUM("D"."SLE_QTY") DESC )<=3)
   4 - access("I"."ITM_NO"="D"."ITM_NO")
   6 - access("D"."SLE_NO"="H"."SLE_NO")
   8 - access("H"."SLE_DT">='20231201' AND "H"."SLE_DT"<='20231231')

[결과 · 1003 매장 · 같은 조건으로 두 번]
  첫 번째   1 (ICE)아메리카노 3,412 · 2 카페라떼 1,206 · 3 바닐라라떼 655
  두 번째   1 (ICE)아메리카노 3,412 · 2 카페라떼 1,206 · 3 카라멜마키아토 655

문제

같은 데이터로 두 번 뽑았는데 1003 매장의 3위가 바뀐 까닭을 정렬 키와 ROW_NUMBER의 성질로 설명하시오.
작성 기준서에 맞게 SQL을 고치고, 1003 매장과 1005 매장에서 ROW_NUMBER, RANK, DENSE_RANK가 각각 몇 줄을 돌려주는지 쓰시오.
고친 SQL에서도 순위 계산이 매장마다 위의 몇 줄만 남기며 정렬하는지 실행계획으로 확인하고, 읽는 블록 수가 달라지는지 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- 기준서대로 : 공동 순위는 모두 싣고 번호는 1, 2, 2, 4 → RANK
SELECT SHP_NO, RNK, ITM_NO, ITM_NM, QTY
  FROM (SELECT h.SHP_NO, d.ITM_NO, i.ITM_NM, SUM(d.SLE_QTY) AS QTY,
               RANK() OVER (PARTITION BY h.SHP_NO ORDER BY SUM(d.SLE_QTY) DESC) AS RNK
          FROM CF10 h, CF11 d, CF01 i
         WHERE h.SLE_DT BETWEEN '20231201' AND '20231231'
           AND d.SLE_NO = h.SLE_NO
           AND i.ITM_NO = d.ITM_NO
         GROUP BY h.SHP_NO, d.ITM_NO, i.ITM_NM)
 WHERE RNK <= 3
 ORDER BY SHP_NO, RNK, ITM_NO      -- 공동 순위 안의 표시 순서까지 정한다

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

xplan

----------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                         |                |      1 |        |     16 |00:00:00.42 |    8693 |          |
|   1 |  SORT ORDER BY                           |                |      1 |    436 |     16 |00:00:00.42 |    8693 | 2048  (0)|
|*  2 |   VIEW                                   |                |      1 |    436 |     16 |00:00:00.42 |    8693 |          |
|*  3 |    WINDOW SORT PUSHED RANK               |                |      1 |    436 |     16 |00:00:00.42 |    8693 | 2048  (0)|
|   4 |     HASH GROUP BY                        |                |      1 |    436 |    436 |00:00:00.42 |    8693 | 1520K (0)|
|*  5 |      HASH JOIN                           |                |      1 |  46615 |  46640 |00:00:00.38 |    8693 | 1401K (0)|
|   6 |       TABLE ACCESS FULL                  | CF01           |      1 |    109 |    109 |00:00:00.01 |       5 |          |
|*  7 |       HASH JOIN                          |                |      1 |  46615 |  46640 |00:00:00.35 |    8688 | 1695K (0)|
|   8 |        TABLE ACCESS BY INDEX ROWID BATCHED| CF10          |      1 |  21250 |  21264 |00:00:00.01 |     484 |          |
|*  9 |         INDEX RANGE SCAN                 | IX_CF10_SLE_DT |      1 |  21250 |  21264 |00:00:00.01 |      62 |          |
|  10 |        TABLE ACCESS FULL                 | CF11           |      1 |   1096K|   1096K|00:00:00.12 |    8204 |          |
----------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("RNK"<=3)
   3 - filter(RANK() OVER ( PARTITION BY "H"."SHP_NO" ORDER BY SUM("D"."SLE_QTY") DESC )<=3)
   5 - access("I"."ITM_NO"="D"."ITM_NO")
   7 - access("D"."SLE_NO"="H"."SLE_NO")
   9 - access("H"."SLE_DT">='20231201' AND "H"."SLE_DT"<='20231231')

[결과 · 1003 매장]  1 (ICE)아메리카노 · 2 카페라떼 · 3 바닐라라떼 · 3 카라멜마키아토 (뽑을 때마다 같다)

모범 답안 서술

1003 매장의 3위 자리에는 바닐라라떼와 카라멜마키아토가 655개로 동점이다. ROW_NUMBER는 동점이어도 1, 2, 3처럼 번호를 하나씩 따로 준다. 정렬 키(SUM(d.SLE_QTY) DESC)로는 두 상품의 순서가 정해지지 않으므로 어느 쪽이 3번을 받을지는 SQL에 적혀 있지 않고, 해시 집계가 내놓은 순서나 정렬 중에 만난 순서에 따라 아무 하나가 3위로 들어간다. 그래서 같은 데이터로 뽑아도 결과가 바뀔 수 있다. 버그가 아니라 SQL이 동점의 순서를 정하지 않은 결과다. 기준서는 동점을 모두 싣고 번호를 1, 2, 2, 4처럼 매기라고 하므로 RANK가 맞다. 매장별로 세면 1003 매장은 ROW_NUMBER 3줄(동점 중 하나만), RANK 4줄(1, 2, 3, 3), DENSE_RANK 4줄(1, 2, 3, 3)이다. 1005 매장은 2위가 701개로 동점이라 ROW_NUMBER 3줄, RANK 3줄(1, 2, 2 다음 카페모카는 4위라 빠진다), DENSE_RANK 4줄(1, 2, 2, 3으로 카페모카가 3위가 된다)이다. DENSE_RANK는 번호를 건너뛰지 않아 '세 번째로 큰 수량까지'라는 뜻이 되고, 기준서의 1, 2, 2, 4와 다르다. 다섯 매장을 합치면 ROW_NUMBER 15줄, RANK 16줄, DENSE_RANK 17줄이다. 실행계획의 일량은 그대로다. RANK도 ROW_NUMBER처럼 바깥 조건 RNK <= 3이 정렬 단계로 내려가 WINDOW SORT PUSHED RANK가 되고, 매장마다 위의 몇 줄만 남기며 정렬한다(작업 공간 2KB 남짓). 읽는 블록은 CF11 전체 8,204블록과 12월 판매 484블록이라 Buffers 8,693 그대로다. 공동 순위 안에서 어느 상품을 먼저 보일지는 바깥 ORDER BY에 ITM_NO를 더해 정했다.

해설

ROW_NUMBER에 동점 깨기 컬럼만 붙이는 답: ORDER BY SUM(d.SLE_QTY) DESC, d.ITM_NO로 쓰면 뽑을 때마다 바뀌는 문제는 사라진다. 하지만 동점 상품 하나가 빠지는 것은 그대로라 기준서를 어긴다. 순위 함수는 요구서의 문장으로 고른다.
RANK는 동점이 많으면 매장마다 3줄보다 많이 돌려준다. 회의 자료 양식이 매장마다 세 칸으로 고정되어 있다면 양식부터 확인해야 한다.
집계 결과에 순위를 매길 때는 ORDER BY SUM(d.SLE_QTY)처럼 집계식을 그대로 쓰면 된다. 순위 조건(RNK <= 3)은 인라인 뷰 바깥에서 거른다. 분석 함수의 결과는 같은 쿼리 블록의 WHERE 절에서 거를 수 없다.
한 달에 한 번 뽑는 자료라 12월 판매만 인덱스로 고르고 CF11은 FULL로 읽는 지금 모양이 적당하다. 매장 수가 늘어도 PUSHED RANK의 작업 공간은 '매장 수 × 몇 줄' 정도로 늘 뿐이다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황