11-2-1. RANK
RANK, DENSE_RANK, ROW_NUMBER는 레코드별 순위를 구하기 위한 분석함수다. 분석함수이므로 다른 분석함수와 마찬가지로 분석대상에 대해 분석을 수행한다. RANK와 같은 순위 분석함수는 OVER 절에 반드시 ORDER BY를 사용해야 한다. ORDER BY에 따라 레코드별 분석대상이 달라지고 이에 따라 순위가 결정된다.
아래는 매장 데이터를 조회하는 SQL이다. RANK OVER 분석함수를 사용해 ChairQty에 따른 순위를 구하고 있다.
-- [SQL-11-2-1-a]
SELECT T1.ShopId ,T1.ShopNm ,T1.ChairQty ,T1.ShopStartYmd
,RANK() OVER(ORDER BY T1.ChairQty DESC) ChairRank
FROM startdb.Shop T1
WHERE T1.ShopOperTp = 'FLAG'
AND T1.ShopStartYmd LIKE '201803%'
ORDER BY T1.ChairQty DESC;
ShopId ShopNm ChairQty ShopStartYmd ChairRank
------ ---------------- -------- ------------ ---------
S027 San Antonio-2nd 38 20180323 1
S026 Philadelphia-2nd 35 20180323 2
S009 Dallas-1st 34 20180318 3
S008 San Diego-1st 31 20180318 4
S014 Columbus-1st 24 20180327 5
S031 Austin-2nd 22 20180327 6
S013 Fort Worth-1st 21 20180323 7
S022 Los Angeles-2nd 20 20180318 8
S012 Jacksonville-1st 16 20180323 9
S011 Austin-1st 13 20180324 10
SQL
복사
의자수에 따라 순위가 매겨진 것을 알 수 있다. OVER 절의 ORDER BY에 ChairQty를 DESC로 지정했기 때문에 의자수가 많을 수록 1위가 된다. 만약에 OVER 절의 ORDER BY에 ChairQty를 ASC로 지정했다면 의자수가 가장 작은 S011(Austin-1st) 매장이 1위가 되었을 것이다.
한 가지 주의할 점은 분석함수의 OVER 절에 정의한 ORDER BY는 데이터 출력 순서에 영향을 주지 않는다는 점이다. DB 내부적으로 분석함수를 처리하면서 운 좋게 원하는 정렬 순서로 데이터가 출력될 수 있지만 절대적이지는 않다. 데이터 정렬 순서는 항상 SQL 끝의 ORDER BY에 의해 좌우된다.
위 SQL에 ShopStartYmd에 대한 순위를 추가해보자. 그리고 SQL 마지막 부분의 ORDER BY도 ShopStartYmd로 같이 변경해주자. RANK 함수가 잘 적용되었는지 쉽게 파악하기 위해서다.
-- [SQL-11-2-1-b]
SELECT T1.ShopId ,T1.ShopNm ,T1.ChairQty ,T1.ShopStartYmd
,RANK() OVER(ORDER BY T1.ShopStartYmd ASC) ShopStartRank -- > 추가
,RANK() OVER(ORDER BY T1.ChairQty DESC) ChairRank
FROM startdb.Shop T1
WHERE T1.ShopOperTp = 'FLAG'
AND T1.ShopStartYmd LIKE '201803%'
ORDER BY T1.ShopStartYmd ASC; -- > 변경
ShopId ShopNm ChairQty ShopStartYmd ShopStartRank ChairRank
------ ---------------- -------- ------------ ------------- ---------
S009 Dallas-1st 34 20180318 1 3
S008 San Diego-1st 31 20180318 1 4
S022 Los Angeles-2nd 20 20180318 1 8
S027 San Antonio-2nd 38 20180323 4 1
S026 Philadelphia-2nd 35 20180323 4 2
S013 Fort Worth-1st 21 20180323 4 7
S012 Jacksonville-1st 16 20180323 4 9
S011 Austin-1st 13 20180324 8 10
S014 Columbus-1st 24 20180327 9 5
S031 Austin-2nd 22 20180327 9 6
SQL
복사
OVER 절의 ORDER BY에 ShopStartYmd를 ASC(오름차순)로 지정했다. 그러므로 가장 빠른 ShopStartYmd를 가진 데이터가 1위가 된다. ShopStartYmd가 2018년 3월 18일인 데이터 세 건이 1위를 차지하고 있다. 1위가 세 건이므로 2, 3위는 생략되고 바로 이어서 4위가 출력되고 있다. RANK는 이처럼 동률에 대해서는 같은 순위를 부여하고 다음 순위를 건너뛴다.
11-2-2. DENSE_RANK와 ROW_NUMBER
RANK 외에도 DENSE_RANK와 ROW_NUMBER 라는 순위 분석함수가 있다. RANK와 DENSE_RANK는 동률 처리에 차이가 있다. RANK는 동률이 나오면 동률 수만큼 건너뛰고 다음 순위를 부여한다. 반면에 DENSE_RANK는 동률이 나와도 다음 순위를 바로 연속해서 부여한다. ROW_NUMBER는 동률 자체가 나오지 않도록 순위가 부여된다. ROW_NUMBER는 정의된 순서대로 줄번호를 부여하는 기능으로 순위 분석함수라 말하기에 적절하지 않을 수 있다. 다만, RANK와 마찬가지로 OVER 절의 ORDER BY에 따라 번호를 부여하므로 기능 자체는 순위 분석함수와 다를 바 없다. 아래 SQL을 통해 순위 분석함수별 결과를 확인해보기 바란다.
-- [SQL-11-2-2-a]
SELECT T1.ShopId ,T1.ShopNm ,T1.ChairQty ,T1.ShopStartYmd
,RANK() OVER(ORDER BY T1.ShopStartYmd ASC) ShopStartRank
,DENSE_RANK() OVER(ORDER BY T1.ShopStartYmd ASC) ShopStartDenseRank
,ROW_NUMBER() OVER(ORDER BY T1.ShopStartYmd ASC) ShopStartRowNumber
FROM startdb.Shop T1
WHERE T1.ShopOperTp = 'FLAG'
AND T1.ShopStartYmd LIKE '201803%'
ORDER BY T1.ShopStartYmd ASC, T1.ShopId ASC;
ShopId ShopNm ChairQty ShopStartYmd ShopStartRank ShopStartDenseRank ShopStartRowNumber
------ ---------------- -------- ------------ ------------- ------------------ ------------------
S008 San Diego-1st 31 20180318 1 1 1
S009 Dallas-1st 34 20180318 1 1 2
S022 Los Angeles-2nd 20 20180318 1 1 3
S012 Jacksonville-1st 16 20180323 4 2 4
S013 Fort Worth-1st 21 20180323 4 2 5
S026 Philadelphia-2nd 35 20180323 4 2 6
S027 San Antonio-2nd 38 20180323 4 2 7
S011 Austin-1st 13 20180324 8 3 8
S014 Columbus-1st 24 20180327 9 4 9
S031 Austin-2nd 22 20180327 9 4 10
SQL
복사
11-2-3. TOP-N
앞에서는 ORDER BY~LIMIT를 활용해 Top-N 처리를 구현했다. 순위 분석함수로도 Top-N 구현을 할 수 있다. 아래는 순위 분석함수와 인라인 뷰를 활용한 Top-N 처리 SQL이다.
-- [SQL-11-2-3-a]
SELECT T2.*
FROM (
SELECT T1.OrdNo ,T1.OrdDtm ,T1.MemberId ,T1.OrdAmt
,ROW_NUMBER() OVER(ORDER BY T1.OrdDtm DESC) RankDtm
FROM startdb.Ord T1
WHERE T1.MemberId = 'M0100'
ORDER BY T1.OrdDtm DESC
) T2
WHERE T2.RankDtm <= 3;
OrdNo OrdDtm MemberId OrdAmt RankDtm
----- ------------------- -------- -------- -------
89839 2023-03-31 09:00:00 M0100 4500.000 1
84642 2023-03-25 09:00:00 M0100 9500.000 2
77543 2023-03-22 09:00:00 M0100 5000.000 3
SQL
복사
뒤에서 배울 PARTITION BY 기능을 순위 분석함수에 추가해서 사용하면 좀 더 다채로운 TOP-N 분석이 가능하다. 아마도 복잡한 분석일수록 ORDER BY LIMIT보다는 순위 분석함수를 사용해야 할 확률이 더 높다. 또한 순위 분석함수는 순위에 따른 번호를 붙여주는 장점이 있다.
BOOSTER QUIZ
BOOSTER QUIZ ERD
BOOSTER QUIZ ERD 11-2-1
(SQL-1)과 (결과-1)을 참고해, (SQL-2)를 실행하면 나올 (결과-2)를 채우시오.
•
(SQL-2)는 (SQL-1)에 순위 분석함수를 추가한 SQL이다.
•
SQL을 실행하지 않고 (결과-2)를 예측해서 채우시오.
-- (SQL-1)
SELECT T1.OrdNo ,T1.ShopId ,T1.OrdDtm, T1.OrdAmt
FROM startdb.Ord T1
WHERE T1.ShopId = 'S092'
AND T1.OrdDtm>=STR_TO_DATE('20210901','%Y%m%d')
AND T1.OrdDtm< STR_TO_DATE('20211001','%Y%m%d')
ORDER BY T1.OrdDtm DESC;
-- (결과-1)
OrdNo ShopId OrdDtm OrdAmt
----- ------ ------------------- --------
17202 S092 2021-09-21 13:30:00 7000.000
17119 S092 2021-09-13 13:30:00 4500.000
16896 S092 2021-09-08 13:30:00 4500.000
16831 S092 2021-09-06 13:30:00 7500.000
16617 S092 2021-09-04 13:30:00 3500.000
-- (SQL-2)
SELECT T1.OrdNo ,T1.ShopId ,T1.OrdDtm, T1.OrdAmt
,RANK() OVER(ORDER BY T1.OrdDtm ASC) RankOverDtm
FROM startdb.Ord T1
WHERE T1.ShopId = 'S092'
AND T1.OrdDtm>=STR_TO_DATE('20210901','%Y%m%d')
AND T1.OrdDtm< STR_TO_DATE('20211001','%Y%m%d')
ORDER BY T1.OrdDtm DESC;
-- (결과-2)
OrdNo ShopId OrdDtm OrdAmt RankOverDtm
----- ------ ------------------- -------- -----------
17202 S092 2021-09-21 13:30:00 7000.000
17119 S092 2021-09-13 13:30:00 4500.000
16896 S092 2021-09-08 13:30:00 4500.000
16831 S092 2021-09-06 13:30:00 7500.000
16617 S092 2021-09-04 13:30:00 3500.000
SQL
복사
BOOSTER QUIZ 11-2-2
회원 등급이 플래티넘이면서 가입일시가 2021년 6월인 회원을 가입일시에 따라 순위를 구해서 보여주세요.
•
대상 테이블: 회원(Member)
•
조회 조건: MemberGd가 PLAT이면서, JoinDtm이 2021년 6월
•
조회 컬럼: MemberId, NickNm, JoinDtm, MemberGd, RankJoin
•
추가 조건
◦
RankJoin은 조회된 데이터의 JoinDtm에 따른 순번입니다.
▪
JoinDtm이 가장 빠른 사람이 1번입니다.
◦
RankJoin은 RANK 분석함수를 사용해 구하시오.
MemberId NickNm JoinDtm MemberGd RankJoin
-------- -------- ------------------- -------- --------
M2681 Nick2681 2021-06-16 00:00:00 PLAT 1
M2611 Nick2611 2021-06-17 00:00:00 PLAT 2
M2680 Nick2680 2021-06-18 00:00:00 PLAT 3
M2691 Nick2691 2021-06-21 00:00:00 PLAT 4
M2621 Nick2621 2021-06-22 00:00:00 PLAT 5
M2661 Nick2661 2021-06-22 00:00:00 PLAT 5
M2651 Nick2651 2021-06-24 00:00:00 PLAT 7
M2641 Nick2641 2021-06-25 00:00:00 PLAT 8
M2631 Nick2631 2021-06-29 00:00:00 PLAT 9
SQL
복사
