11-4-1. PARTITION BY
분석함수는 분석대상에 대해 분석을 수행한다. 분석함수의 OVER 절에 PARTITION BY와 ORDER BY, 그리고 RANGE/ROWS를 사용해 레코드별로 분석대상을 세밀하게 나눌 수 있다.
PARTITION BY를 사용해 특정 컬럼의 값에 따라 레코드별로 분석대상을 나눌 수 있다. 아래는 SUM OVER 분석함수를 사용한 SQL이다.
-- [SQL-11-4-1-a] PARTITION BY
SELECT T1.ShopId ,T1.OrdNo ,T1.OrdDtm ,T1.OrdAmt
,SUM(T1.OrdAmt) OVER() SumOverOrdAmt
,SUM(T1.OrdAmt) OVER(PARTITION BY T1.ShopId) SumOverOrdAmtByShop
FROM startdb.Ord T1
WHERE T1.OrdDtm >= STR_TO_DATE('20220101','%Y%m%d')
AND T1.OrdDtm < STR_TO_DATE('20220110','%Y%m%d')
AND T1.ShopId IN ('S195','S027')
ORDER BY T1.ShopId ,T1.OrdDtm ASC;
ShopId OrdNo OrdDtm OrdAmt SumOverOrdAmt SumOverOrdAmtByShop
------ ----- ------------------- -------- ------------- -------------------
S027 25441 2022-01-05 11:00:00 7500.000 28500.000 12000.000
S027 25485 2022-01-05 12:00:00 4500.000 28500.000 12000.000
S195 25285 2022-01-02 09:30:00 8000.000 28500.000 16500.000
S195 25443 2022-01-05 11:30:00 8500.000 28500.000 16500.000
SQL
복사
•
SumOverOrdAmt (전체 주문 금액)
◦
SUM(T1.OrdAmt) OVER()
◦
OVER() 절 내에 아무런 파티션 구분이 지정되지 않았기 때문에, 전체 레코드를 대상으로 주문 금액의 합계를 계산한다.
◦
모든 레코드에서 계산된 합계 값은 동일하며, 이는 전체 주문 금액의 총합을 나타낸다.
•
SumOverOrdAmtByShop (매장별 주문 금액)
◦
SUM(T1.OrdAmt) OVER(PARTITION BY T1.ShopId)
◦
OVER() 절 내에 PARTITION BY T1.ShopId가 포함되어 있어, ShopId별로 데이터를 그룹화하여 분석한다.
◦
이 구분에 따라 각 매장 ShopId가 같은 레코드들끼리 그룹화되어 그 그룹 내에서만 주문 금액의 합계가 계산된다.
◦
결과적으로 각 매장별로 주문 금액의 합계가 계산되며, 같은 ShopId를 가진 레코드들은 동일한 합계 값을 가집니다.
◦
PARTITION BY는 GROUP BY와 달리, 같은 값을 가진 레코드를 하나로 모으지 않고 각각 독립적으로 유지한다. 분석함수는 데이터의 건수를 변화시키지 않으면서, 각 레코드를 개별적으로 분석할 수 있도록 해준다는 것을
◦
GROUP BY 처럼 같은 값을 가진 레코드를 모아서 한 건으로 표현하지 않는다. 분석함수는 레코드의 건수를 변형시키지 않은 채 분석을 수행하는 특징이 있다.
11-4-2. PARTITION BY ~ ORDER BY
OVER 절에는 PARTITION BY와 ORDER BY를 동시에 사용할 수 있다. RANK OVER 분석함수에 PARTITION BY를 추가하면 파티션별로 순위를 구할 수 있다. 아래는 ShopOperTp 별로 TableQty에 따른 순위를 구하는 SQL이다. PARTITION BY를 먼저 정의하고 ORDER BY를 뒤에 정의한다.
-- [SQL-11-4-2-a] 파티션별 테이블수량 순위 구하기
SELECT T1.ShopID ,T1.ShopNm ,T1.TableQty ,T1.ShopOperTp
,RANK() OVER(ORDER BY T1.TableQty DESC) RankTableQty
,RANK() OVER(PARTITION BY T1.ShopOperTp ORDER BY T1.TableQty DESC) RankTableQtyByOperTp
FROM startdb.Shop T1
WHERE T1.ShopStartYmd = '20180409'
ORDER BY T1.ShopOperTp ASC, T1.TableQty DESC;
ShopID ShopNm TableQty ShopOperTp RankTableQty RankTableQtyByOperTp
------ ---------------- -------- ---------- ------------ --------------------
S049 Dallas-3rd 21 DIST 1 1
S006 Philadelphia-1st 9 DIST 4 2
S048 San Diego-3rd 20 DRCT 2 1
S007 San Antonio-1st 10 FLAG 3 1
S021 New York-2nd 7 FLAG 5 2
S020 Washington-1st 6 FLAG 6 3
SQL
복사
PARTITION BY에도 여러 컬럼을 지정할 수 있다. PARTITION BY로 정의한 컬럼들이 끝나고 ORDER BY를 사용할 때는 콤마가 아닌 빈칸으로만 구분한다는 점에 주의하기 바란다.
•
PARTITION BY COL1, ORDER BY COL3: 비정상
•
PARTITION BY COL1 ORDER BY COL3: 정상
•
PARTITION BY COL1, COL2, ORDER BY COL3: 비정상
•
PARTITION BY COL1, COL2 ORDER BY COL3: 정상
아래는 ShopId, MemberId별로 순위를 구하는 SQL이다.
-- [SQL-11-4-2-b]
SELECT T1.ShopId ,T1.MemberId ,T1.OrdDtm ,T1.OrdNo
,ROW_NUMBER() OVER(ORDER BY T1.OrdDtm ASC) RankOverDt
,ROW_NUMBER() OVER(
PARTITION BY T1.ShopId ,T1.MemberId
ORDER BY T1.OrdDtm ASC) RankOverDtShopMember
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 IN ('S195','S027')
ORDER BY T1.ShopId ,T1.MemberId ASC;
ShopId MemberId OrdDtm OrdNo RankOverDt RankOverDtShopMember
------ -------- ------------------- ----- ---------- --------------------
S027 M1295 2022-01-05 12:00:00 25485 4 1
S027 M1295 2022-01-11 12:00:00 25652 7 2
S027 M1295 2022-01-23 12:00:00 25837 10 3
S027 M1295 2022-01-29 12:00:00 26004 13 4
S027 M2293 2022-01-05 11:00:00 25441 2 1
S027 M2293 2022-01-11 11:00:00 25608 5 2
S027 M2293 2022-01-23 11:00:00 25793 8 3
S027 M2293 2022-01-29 11:00:00 25960 11 4
S195 M0207 2022-01-05 11:30:00 25443 3 1
S195 M0207 2022-01-11 11:30:00 25610 6 2
S195 M0207 2022-01-23 11:30:00 25795 9 3
S195 M0207 2022-01-29 11:30:00 25962 12 4
S195 M1210 2022-01-02 09:30:00 25285 1 1
S195 M1210 2022-01-30 09:30:00 26054 14 2
SQL
복사
이처럼 RANK 분석함수에 PARTITION BY를 적용하면 특정 그룹별 순위를 쉽게 구할 수 있다.
11-4-3. PARTITION BY 컬럼의 변형
컬럼을 변환해서 GROUP BY에 사용할 수 있는 것처럼, PARTITION BY의 컬럼도 변환해서 사용이 가능하다. 아래는 년월별로 상품별 주문수량 순위를 구해서 Top-3 까지만 출력하는 SQL이다.
-- [SQL-11-4-2-c] 주문년월별 Top-3 상품
SELECT T5.*
FROM (
SELECT T4.OrdYm ,T4.ItemId ,T4.ItemNm ,T4.OrdQty
,RANK() OVER(PARTITION BY T4.OrdYm ORDER BY T4.OrdQty DESC) RankByYm
FROM (
SELECT DATE_FORMAT(T1.OrdDtm,'%Y%m') OrdYm ,T3.ItemId ,MAX(T3.ItemNm) ItemNm
,SUM(T2.OrdQty) OrdQty
FROM startdb.Ord T1
INNER JOIN startdb.OrdDet T2
ON (T2.OrdNo = T1.OrdNo)
INNER JOIN startdb.Item T3
ON (T3.ItemID = T2.ItemId)
WHERE T1.OrdDtm >= STR_TO_DATE('20230101','%Y%m%d')
AND T1.OrdDtm < STR_TO_DATE('20230301','%Y%m%d')
GROUP BY DATE_FORMAT(T1.OrdDtm,'%Y%m') ,T3.ItemId
) T4
) T5
WHERE T5.RankByYm <= 3;
OrdYm ItemId ItemNm OrdQty RankByYm
------ ------ ------------------ ------ --------
202301 IAMR Iced Americano(R) 287 1
202301 ICLR Iced Cafe Latte(R) 286 2
202301 AMR Americano(R) 286 2
202302 ICLR Iced Cafe Latte(R) 299 1
202302 AMR Americano(R) 299 1
202302 IAMR Iced Americano(R) 298 3
SQL
복사
BOOSTER QUIZ
BOOSTER QUIZ ERD
BOOSTER QUIZ 11-4-1
2023년 1월 주문에서 M0200,M0201,M0202 회원의 데이터만 조회해주세요. 주문 리스트와 회원별 주문금액합계를 컬럼으로 추가해 보여주세요.
•
대상 테이블: 주문(Ord)
•
조회 조건: OrdDtm이 2023년 1월이면서 MemberId가 M0200,M0201,M0202인 데이터
•
조회 컬럼: MemberId, OrdDtm, OrdAmt, SumOverAmtMember
•
추가 조건
◦
SumOverAmtMember는 조회된 데이터에서 회원별 주문금액 합계입니다. 분석함수에 PARTITION을 적용해 구합니다.
◦
MemberId, OrdDtm으로 오름차순 정렬해주세요.
MemberId OrdDtm OrdAmt SumOverAmtMember
-------- ------------------- -------- ----------------
M0200 2023-01-02 10:00:00 4500.000 12500.000
M0200 2023-01-30 10:00:00 8000.000 12500.000
M0201 2023-01-05 10:00:00 9000.000 27500.000
M0201 2023-01-11 10:00:00 9500.000 27500.000
M0201 2023-01-23 10:00:00 4500.000 27500.000
M0201 2023-01-29 10:00:00 4500.000 27500.000
M0202 2023-01-02 11:00:00 4500.000 9500.000
M0202 2023-01-30 11:00:00 5000.000 9500.000
SQL
복사
BOOSTER QUIZ 11-4-2
2023년 1월부터 2023년 3월 주문에 대해, 주문년월별로 주문금액이 가장 높은 매장 하나씩을 조회해주세요.
•
대상 테이블: 매장(Shop), 주문(Ord)
•
조회 조건: OrdDtm이 2023년 1월부터 2023년 3월까지인 주문 데이터
•
조회 컬럼: OrdYm, ShopId, ShopNm, SumOrdAmt, RankByYm
•
추가 조건
◦
단계적으로 SQL을 작성합니다.
◦
첫 번째 인라인 뷰(IV1)
▪
OrdYm은 OrdDtm을 년월형태로 변환한 값이다.
▪
ShopId별 OrdYm별 GROUP BY 처리하시오
▪
SumOrdAmt는 OrdYm, ShopId별 OrdAmt를 SUM한 값
◦
두 번째 인라인 뷰(IV2)
▪
첫 번째 인라인 뷰(IV1)를 활용해 RankByYm 구하기
▪
RankByYm은 OrdYm별로 OrdAmt에 따른 순위
•
RANK 분석함수와 PARTITION BY를 사용해 구한다.
◦
메인 쿼리
▪
두 번째 인라인 뷰(IV2)에서 RankByYm이 1인 데이터만 조회
OrdYm ShopId ShopNm SumOrdAmt RankByYm
------ ------ ------------- ---------- --------
202301 S230 San Jose-12th 210000.000 1
202302 S244 Houston-13th 173500.000 1
202303 S134 Columbus-7th 557000.000 1
SQL
복사
