11-7-1. FIRST_VALUE, LAST_VALUE
FIRST_VALUE와 LAST_VALUE는 레코드별로 분석대상의 첫 번째 값이 마지막 값을 보여주기 위해 사용한다. LAG, LEAD와 마찬가지로 첫 번째와 마지막의 기준은 OVER 절의 ORDER BY에 따라 결정된다.
FIRST_VALUE와 LAST_VALUE를 막상 사용해보면 FIRST_VALUE는 쉽게 잘 나오지만, LAST_VALUE는 잘 나오지 않는다. 다음과 같이 실행해보자.
-- [SQL-11-7-1-a] LAST_VALUE는 적절하지 않다.
SELECT T1.ShopId ,T1.OrdNo ,T1.OrdDtm ,T1.OrdAmt
,FIRST_VALUE(T1.OrdDtm) OVER(ORDER BY T1.OrdDtm ASC) FirstOrdDtm
,LAST_VALUE(T1.OrdDtm) OVER(ORDER BY T1.OrdDtm ASC) LastOrdDtm
FROM startdb.Ord T1
WHERE T1.OrdDtm >= STR_TO_DATE('20220101','%Y%m%d')
AND T1.OrdDtm < STR_TO_DATE('20220201','%Y%m%d')
AND T1.ShopId = ('S195')
ORDER BY T1.OrdDtm ASC;
ShopId OrdNo OrdDtm OrdAmt FirstOrdDtm LastOrdDtm
------ ----- ------------------- --------- ------------------- -------------------
S195 25285 2022-01-02 09:30:00 8000.000 2022-01-02 09:30:00 2022-01-02 09:30:00
S195 25443 2022-01-05 11:30:00 8500.000 2022-01-02 09:30:00 2022-01-05 11:30:00
S195 25610 2022-01-11 11:30:00 12500.000 2022-01-02 09:30:00 2022-01-11 11:30:00
S195 25795 2022-01-23 11:30:00 6500.000 2022-01-02 09:30:00 2022-01-23 11:30:00
S195 25962 2022-01-29 11:30:00 6500.000 2022-01-02 09:30:00 2022-01-29 11:30:00
S195 26054 2022-01-30 09:30:00 8000.000 2022-01-02 09:30:00 2022-01-30 09:30:00
SQL
복사
위 SQL의 결과를 보면 FirstOrdDtm은 제대로 나왔지만, LastOrdDtm은 현재 레코드의 OrdDtm과 같은 값이 나온 것을 알 수 있다. 이는 OVER 절의 ORDER BY 뒤에 디폴트로 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW가 적용되었기 때문이다. 레코드별로 CURRENT ROW 까지만 분석대상이므로 자신이 마지막 레코드로 처리된 것이다. LastOrdDtm을 전체 분석대상에서 마지막 레코드로 처리하고 싶다면 아래와 같이 ORDER BY 다음에 RANGE나 ROWS를 적절하게 명시해주어야 한다.
-- [SQL-11-7-1-b] LAST_VALUE ROWS 조정
SELECT T1.ShopId ,T1.OrdNo ,T1.OrdDtm ,T1.OrdAmt
,FIRST_VALUE(T1.OrdDtm) OVER(ORDER BY T1.OrdDtm ASC) FirstOrdDtm
,LAST_VALUE(T1.OrdDtm) OVER(ORDER BY T1.OrdDtm ASC
ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) LastOrdDtm
FROM startdb.Ord T1
WHERE T1.OrdDtm >= STR_TO_DATE('20220101','%Y%m%d')
AND T1.OrdDtm < STR_TO_DATE('20220201','%Y%m%d')
AND T1.ShopId = ('S195')
ORDER BY T1.OrdDtm ASC;
ShopId OrdNo OrdDtm OrdAmt FirstOrdDtm LastOrdDtm
------ ----- ------------------- --------- ------------------- -------------------
S195 25285 2022-01-02 09:30:00 8000.000 2022-01-02 09:30:00 2022-01-30 09:30:00
S195 25443 2022-01-05 11:30:00 8500.000 2022-01-02 09:30:00 2022-01-30 09:30:00
S195 25610 2022-01-11 11:30:00 12500.000 2022-01-02 09:30:00 2022-01-30 09:30:00
S195 25795 2022-01-23 11:30:00 6500.000 2022-01-02 09:30:00 2022-01-30 09:30:00
S195 25962 2022-01-29 11:30:00 6500.000 2022-01-02 09:30:00 2022-01-30 09:30:00
S195 26054 2022-01-30 09:30:00 8000.000 2022-01-02 09:30:00 2022-01-30 09:30:00
SQL
복사
11-7-2. NTILE
NTILE은 조회 결과를 지정된 수에 따라 동일한 크기로 나누는 역할을 해주는 분석함수다. 아래 SQL을 살펴보자.
-- [SQL-11-7-2-a] 기본 데이터, 매장별 주문금액합계
SELECT T1.ShopId ,MAX(T1.ShopNm) ShopNm ,IFNULL(SUM(T2.OrdAmt),0) SumOrdAmt
FROM startdb.Shop T1
LEFT OUTER JOIN startdb.Ord T2
ON (T2.ShopId = T1.ShopId
AND T2.OrdDtm >= STR_TO_DATE('20220101','%Y%m%d')
AND T2.OrdDtm < STR_TO_DATE('20230101','%Y%m%d'))
WHERE T1.ShopStartYmd LIKE '201803%'
GROUP BY T1.ShopId
ORDER BY SumOrdAmt DESC;
ShopId ShopNm SumOrdAmt
------ ----------------- -----------
S011 Austin-1st 2425500.000
S026 Philadelphia-2nd 1738500.000
S013 Fort Worth-1st 1725500.000
S022 Los Angeles-2nd 1646500.000
S012 Jacksonville-1st 1534000.000
S031 Austin-2nd 1528000.000
S008 San Diego-1st 1447500.000
S025 Phoenix-2nd 1433000.000
S027 San Antonio-2nd 1397500.000
S009 Dallas-1st 1382000.000
S014 Columbus-1st 1099000.000
S023 Chicago-2nd 1075000.000
S040 Washington-2nd 1014500.000
S029 Dallas-2nd 992000.000
S042 Los Angeles-3rd 887000.000
S033 Fort Worth-2nd 787000.000
S010 San Jose-1st 732000.000
S028 San Diego-2nd 621000.000
S024 Houston-2nd 596000.000
S041 New York-3rd 536000.000
S050 San Jose-3rd 487500.000
S036 San Francisco-2nd 0.000
S037 Indianapolis-2nd 0.000
S038 Seattle-2nd 0.000
S039 Denver-2nd 0.000
SQL
복사
위 SQL은 2018년 3월에 개장한 매장에 대해, 매장별 주문금액을 집계하고 있다. 총 25개의 매장이 출력된다. 위 결과를 주문금액합계(SumOrdAmt)에 따라 다섯개 그룹으로 나누려고 한다. 아래와 같이 NTILE 분석함수를 적용할 수 있다.
-- [SQL-11-7-2-b] 주문금액에 따라 다섯개 그룹으로 나누기
SELECT T3.ShopId ,T3.ShopNm ,T3.SumOrdAmt
,NTILE(5) OVER(ORDER BY T3.SumOrdAmt DESC) AmtGroup
FROM (
SELECT T1.ShopId ,MAX(T1.ShopNm) ShopNm ,IFNULL(SUM(T2.OrdAmt),0) SumOrdAmt
FROM startdb.Shop T1
LEFT OUTER JOIN startdb.Ord T2
ON (T2.ShopId = T1.ShopId
AND T2.OrdDtm >= STR_TO_DATE('20220101','%Y%m%d')
AND T2.OrdDtm < STR_TO_DATE('20230101','%Y%m%d'))
WHERE T1.ShopStartYmd LIKE '201803%'
GROUP BY T1.ShopId
ORDER BY SumOrdAmt DESC
) T3;
ShopId ShopNm SumOrdAmt AmtGroup
------ ----------------- ----------- --------
S011 Austin-1st 2425500.000 1
S026 Philadelphia-2nd 1738500.000 1
S013 Fort Worth-1st 1725500.000 1
S022 Los Angeles-2nd 1646500.000 1
S012 Jacksonville-1st 1534000.000 1
S031 Austin-2nd 1528000.000 2
S008 San Diego-1st 1447500.000 2
S025 Phoenix-2nd 1433000.000 2
S027 San Antonio-2nd 1397500.000 2
S009 Dallas-1st 1382000.000 2
S014 Columbus-1st 1099000.000 3
S023 Chicago-2nd 1075000.000 3
S040 Washington-2nd 1014500.000 3
S029 Dallas-2nd 992000.000 3
S042 Los Angeles-3rd 887000.000 3
S033 Fort Worth-2nd 787000.000 4
S010 San Jose-1st 732000.000 4
S028 San Diego-2nd 621000.000 4
S024 Houston-2nd 596000.000 4
S041 New York-3rd 536000.000 4
S050 San Jose-3rd 487500.000 5
S036 San Francisco-2nd 0.000 5
S037 Indianapolis-2nd 0.000 5
S038 Seattle-2nd 0.000 5
S039 Denver-2nd 0.000 5
SQL
복사
AmtGroup을 확인해보면 SumOrdAmt에 따라 다섯개 그룹 번호가 만들어진 것을 알 수 있다. 이를 통해 상위 20%에 해당하는 매장 리스트를 쉽게 구할 수 있다. 위 SQL을 다시 인라인 뷰로 처리한 후에 AmtGroup이 1인 데이터만 출력하면 된다.
회원등급이 골드와 실버인 회원들에 대해, 등급별로 2022년 주문금액이 가장높은 상위 1% 회원을 찾아 회원등급을 승급하려고 한다. 이 경우 다음과 같이 NTILE 분석함수에 PARTITION BY를 사용하면 쉽게 원하는 결과를 얻을 수 있다.
-- [SQL-11-7-2-c] 주문금액에 따라 승급 대상 회원 조회하기
SELECT T4.*
,CASE WHEN T4.MemberGd = 'GOLD' THEN '플래티넘승급대상'
WHEN T4.MemberGd = 'SILV' THEN '골드승급대상' END Memo
FROM (
SELECT T3.MemberId ,T3.NickNm ,T3.MemberGd ,T3.SumOrdAmt
,NTILE(100) OVER(PARTITION BY T3.MemberGd ORDER BY T3.SumOrdAmt DESC) MemberGrByGd
,RANK() OVER(PARTITION BY T3.MemberGd ORDER BY T3.SumOrdAmt DESC) RankAmt
FROM (
SELECT T1.MemberId
,MAX(T1.NickNm) NickNm
,MAX(T1.MemberGd) MemberGd
,IFNULL(SUM(T2.OrdAmt),0) SumOrdAmt
FROM startdb.Member T1
INNER JOIN startdb.Ord T2
ON (T2.MemberId = T1.MemberId
AND T2.OrdDtm >= STR_TO_DATE('20220101','%Y%m%d')
AND T2.OrdDtm < STR_TO_DATE('20230101','%Y%m%d'))
WHERE T1.MemberGd IN ('GOLD','SILV')
GROUP BY T1.MemberId
) T3
ORDER BY T3.MemberGd, T3.SumOrdAmt DESC
) T4
WHERE T4.MemberGrByGd = 1;
MemberId NickNm MemberGd SumOrdAmt MemberGrByGd RankAmt Memo
-------- ---------- -------- --------- ------------ ------- ----------------
M7040 Spark140 GOLD 25500.000 1 1 플래티넘승급대상
M8792 Stone175 GOLD 25000.000 1 2 플래티넘승급대상
M4825 Meteor96 GOLD 24000.000 1 3 플래티넘승급대상
M4945 Sun98 GOLD 22500.000 1 4 플래티넘승급대상
M6122 Light122 GOLD 22500.000 1 4 플래티넘승급대상
M4901 Air98 GOLD 22000.000 1 6 플래티넘승급대상
M3122 Light62 GOLD 21500.000 1 7 플래티넘승급대상
M5542 Stone110 GOLD 21500.000 1 7 플래티넘승급대상
M5875 Meteor117 GOLD 21500.000 1 7 플래티넘승급대상
M9313 Flame186 GOLD 21000.000 1 10 플래티넘승급대상
M3919 Ice78 GOLD 21000.000 1 10 플래티넘승급대상
M5410 Earth108 GOLD 21000.000 1 10 플래티넘승급대상
M1759 Diamond35 GOLD 21000.000 1 10 플래티넘승급대상
M8963 Flame179 GOLD 21000.000 1 10 플래티넘승급대상
M3521 Leaf70 GOLD 21000.000 1 10 플래티넘승급대상
M5165 Frost103 GOLD 21000.000 1 10 플래티넘승급대상
M2311 Ember46 GOLD 21000.000 1 10 플래티넘승급대상
M6401 Air128 GOLD 21000.000 1 10 플래티넘승급대상
M4712 Fire94 GOLD 21000.000 1 10 플래티넘승급대상
M6055 Comet121 GOLD 21000.000 1 10 플래티넘승급대상
M9191 Star183 GOLD 21000.000 1 10 플래티넘승급대상
M1883 Quantum37 GOLD 21000.000 1 10 플래티넘승급대상
M4112 Fire82 GOLD 20500.000 1 23 플래티넘승급대상
M9401 Air188 GOLD 20500.000 1 23 플래티넘승급대상
M3191 Star63 GOLD 20500.000 1 23 플래티넘승급대상
M3470 Lake69 GOLD 20500.000 1 23 플래티넘승급대상
M6001 Air120 GOLD 20500.000 1 23 플래티넘승급대상
M2312 Fire46 GOLD 20500.000 1 23 플래티넘승급대상
M4803 Cloud96 GOLD 20500.000 1 23 플래티넘승급대상
M3835 Ruby76 GOLD 20500.000 1 23 플래티넘승급대상
M5652 Apple113 GOLD 20500.000 1 23 플래티넘승급대상
M3549 Water70 GOLD 20500.000 1 23 플래티넘승급대상
M9861 Ember197 SILV 17000.000 1 1 골드승급대상
M6429 Ocean128 SILV 17000.000 1 1 골드승급대상
M7025 Meteor140 SILV 17000.000 1 1 골드승급대상
M5315 Frost106 SILV 16500.000 1 4 골드승급대상
M3965 Frost79 SILV 16500.000 1 4 골드승급대상
M3170 Lake63 SILV 16500.000 1 4 골드승급대상
M5033 Quantum100 SILV 16500.000 1 4 골드승급대상
M6759 Diamond135 SILV 16500.000 1 4 골드승급대상
M5529 Ocean110 SILV 16500.000 1 4 골드승급대상
M6960 Earth139 SILV 16500.000 1 4 골드승급대상
M4128 Nebula82 SILV 16000.000 1 11 골드승급대상
M6492 Stone129 SILV 16000.000 1 11 골드승급대상
M3603 Cloud72 SILV 16000.000 1 11 골드승급대상
M8685 Ruby173 SILV 16000.000 1 11 골드승급대상
M9582 Pink191 SILV 16000.000 1 11 골드승급대상
M3452 Apple69 SILV 16000.000 1 11 골드승급대상
M5339 Space106 SILV 16000.000 1 11 골드승급대상
M6443 Storm128 SILV 16000.000 1 11 골드승급대상
M8762 Fire175 SILV 16000.000 1 11 골드승급대상
M6593 Storm131 SILV 16000.000 1 11 골드승급대상
M7679 Ocean153 SILV 16000.000 1 11 골드승급대상
M7148 Thunder142 SILV 16000.000 1 11 골드승급대상
M8891 Star177 SILV 16000.000 1 11 골드승급대상
M7905 Comet158 SILV 16000.000 1 11 골드승급대상
M3759 Diamond75 SILV 16000.000 1 11 골드승급대상
M3939 Space78 SILV 16000.000 1 11 골드승급대상
M9063 Flame181 SILV 15500.000 1 27 골드승급대상
M9339 Space186 SILV 15500.000 1 27 골드승급대상
M5415 Frost108 SILV 15500.000 1 27 골드승급대상
M7668 Green153 SILV 15500.000 1 27 골드승급대상
M5433 Quantum108 SILV 15500.000 1 27 골드승급대상
M4991 Star99 SILV 15500.000 1 27 골드승급대상
SQL
복사
