9-7-1. INLINEW VIEW와 TOP-N
MySQL에서는 Top-N을 구하기 위해 ORDER BY와 LIMIT를 조합해서 사용한다. 아래는 2022년 12월 주문이 가장많은 회원 Top-3를 구하는 SQL이다.
-- [SQL-9-7-1-a] 2022년 12월 주문 Top-3 회원
SELECT A.MemberId
,MAX(A.NickNm) NickNm
,COUNT(*) OrdCnt
FROM startdb.Member A
INNER JOIN startdb.Ord B
ON (B.MemberId = A.MemberId)
WHERE B.OrdDtm >= STR_TO_DATE('20221201','%Y%m%d')
AND B.OrdDtm < STR_TO_DATE('20230101','%Y%m%d')
GROUP BY A.MemberId
ORDER BY OrdCnt DESC
LIMIT 3;
MemberId NickNm OrdCnt
-------- ---------- ------
M9999 Water199 64
M9998 Thunder199 64
M1001 Air20 33
SQL
복사
위 SQL 결과로 얻은 회원(2022년 12월 주문 Top-3 회원)의 2023년 1월 인기 상품(Item)을 분석하려고 한다. 이런 경우 위 SQL 자체를 인라인 뷰 처리한 후에, 인라인 뷰의 결과와 2023년 1월 주문 데이터를 조인해 처리할 수 있다. VIP 회원의 관심을 추적하는 간단한 방법이라 할 수 있다.
-- [SQL-9-7-1-b] TOP-3 회원의 23년 1월 인기 상품
-- TOP-N을 인라인 뷰 처리
SELECT T4.ItemId
,MAX(T4.ItemNm) ItemNm
,SUM(T3.OrdQty) OrdQty
FROM (
-- 2022년 12월 주문 Top-3 회원
SELECT A.MemberId
,MAX(A.NickNm) NickNm
,COUNT(*) OrdCnt
FROM startdb.Member A
INNER JOIN startdb.Ord B
ON (B.MemberId = A.MemberId)
WHERE B.OrdDtm >= STR_TO_DATE('20221201','%Y%m%d')
AND B.OrdDtm < STR_TO_DATE('20230101','%Y%m%d')
GROUP BY A.MemberId
ORDER BY OrdCnt DESC
LIMIT 3
) T1
INNER JOIN startdb.Ord T2 -- 2023년 1월 주문 정보
ON (T2.MemberId = T1.MemberId)
INNER JOIN startdb.OrdDet T3
ON (T3.OrdNo = T2.OrdNo)
INNER JOIN startdb.Item T4
ON (T4.ItemId = T3.ItemId)
WHERE T2.OrdDtm >= STR_TO_DATE('20230101','%Y%m%d')
AND T2.OrdDtm < STR_TO_DATE('20230201','%Y%m%d')
GROUP BY T4.ItemId
ORDER BY OrdQty DESC;
ItemId ItemNm OrdQty
------ ------------------- ------
IAMB Iced Americano(B) 18
BGLR Bagel(R) 17
AMB Americano(B) 17
LEMR Lemonade(R) 16
AMR Americano(R) 16
IAMR Iced Americano(R) 16
HCHR Hot Chocolate(R) 16
CLR Cafe Latte(R) 15
CLB Cafe Latte(B) 15
HCHB Hot Chocolate(B) 13
ICLR Iced Cafe Latte(R) 13
BMFR Blueberry Muffin(R) 12
ICLB Iced Cafe Latte(B) 11
CMFR Chocolate Muffin(R) 9
SQL
복사
9-7-2. TOP AND TOP
인라인 뷰와 LIMIT를 사용해 두 구간이나 두 집단에서 동시에 TOP-N에 속하는 데이터를 찾아보자. 아래와 같은 로직의 데이터를 조회하려고 한다.
•
상품종류가 커피인 주문에 대해서만,
•
2023년 1월에 가장 많이 주문 된 상품 Top-3에 속하면서
•
2024년 1월에 가장 많이 주문 된 상품 Top-3
아래와 같이 SQL을 작성할 수 있다.
-- [SQL-9-7-2-a] 2023년 1월 Top-3 상품이면서 2024년 1월 Top-30 상품
SELECT T3.ItemNm ,T3.ItemId ,T1.OrdQty_202301 ,T2.OrdQty_202401
FROM (
SELECT B.ItemId ,SUM(B.OrdQty) OrdQty_202301
FROM startdb.Ord A
INNER JOIN startdb.OrdDet B
ON (A.OrdNo = B.OrdNo)
INNER JOIN startdb.Item C
ON (C.ItemID = B.ItemId)
WHERE A.OrdDtm >= STR_TO_DATE('20230101','%Y%m%d')
AND A.OrdDtm < STR_TO_DATE('20230201','%Y%m%d')
AND C.ItemCat = 'COF'
GROUP BY B.ItemId
ORDER BY SUM(B.OrdQty) DESC LIMIT 3
) T1
INNER JOIN (
SELECT B.ItemId ,SUM(B.OrdQty) OrdQty_202401
FROM startdb.Ord A
INNER JOIN startdb.OrdDet B
ON (A.OrdNo = B.OrdNo)
INNER JOIN startdb.Item C
ON (C.ItemID = B.ItemId)
WHERE A.OrdDtm >= STR_TO_DATE('20240101','%Y%m%d')
AND A.OrdDtm < STR_TO_DATE('20240201','%Y%m%d')
AND C.ItemCat = 'COF'
GROUP BY B.ItemId
ORDER BY SUM(B.OrdQty) DESC LIMIT 3
) T2
ON (T1.ItemId = T2.ItemId)
INNER JOIN startdb.Item T3
ON (T3.ItemID = T1.ItemId);
ItemNm ItemId OrdQty_202301 OrdQty_202401
------------------ ------ ------------- -------------
Iced Cafe Latte(R) ICLR 286 9091
SQL
복사
위 SQL의 결과를 보면 한 건의 데이터만 조회되었다. 아래 그림을 살펴보면 왜 한 건만 나왔는지 쉽게 알 수 있다.
BOOSTER QUIZ
BOOSTER QUIZ ERD
BOOSTER QUIZ 9-7-1
2022년에 커피 상품의 주문 수량이 가장 많은 매장 Top-5만 보여주세요.
매장의 이름과 매장운영유형에 대한 정보를 추가해주세요.
•
대상 테이블: 주문(Ord), 주문상세(OrdDet), 상품(Item), 매장(Shop)
•
조회 조건
◦
OrdDtm이 2022년인 모든 주문 데이터
◦
ItemCat(상품카테고리)가 'COF'(커피)만
•
조회 컬럼: ShopId, ShopNm ,ShopOperTp ,MemberGd, SUM_OrdQty
◦
SUM_OrdQty: ShopId별 커피 상품 주문 수량
•
추가 조건
◦
인라인 뷰를 활용
▪
커피상품 주문수량이 가장 많은 Top-5 Shop을 구하시오.
▪
인라인 뷰 안에서는 Ord와 OrdDet, Item 테이블만 사용
◦
인라인 뷰 바깥에서 Shop에 대한 추가 정보를 조인해서 보여주시오.
ShopID ShopNm ShopOperTp SUM_OrdQty
------ ----------------- ---------- ----------
S001 New York-1st FLAG 2293
S003 Chicago-1st FLAG 463
S200 Washington-10th FLAG 436
S007 San Antonio-1st FLAG 404
S016 San Francisco-1st FLAG 372
SQL
복사
BOOSTER QUIZ 9-7-2
2020년 12월에 주문금액이 가장 큰 Top-5 매장이면서, 동시에 2021년 12월에도 주문금액이 가장 큰 Top-5에 속하는 매장을 보여주세요.
•
대상 테이블: 매장(Shop), 주문(Ord)
•
조회 조건
◦
조건 A: 2020년 12월 주문에서 OrdAmt 합계가 가장 큰 다섯 개 매장
◦
조건 B: 2021년 12월 주문에서 OrdAmt 합계가 가장 큰 다섯 개 매장
◦
조건 A에서 Top-5이면서, 조건 B에서도 Top-5에 속하는 데이터만 추출
▪
조건 A와 조건 B를 동시에 만족하면 결과는 다섯 건 이하일 수 있다.
•
조회 컬럼: ShopId, ShopNm, ShopOperTp ,Amt_202012, Amt_202112
•
추가 조건:
◦
조건 A와 조건 B를 각각의 인라인 뷰로 구현할것
◦
Amt_202012는 조건 A에 해당하는 Shop의 OrdAmt에 대한 합계
◦
Amt_202112는 조건 B에 해당하는 Shop의 OrdAmt에 대한 합계
ShopID ShopNm ShopOperTp Amt_202012 Amt_202112
------ --------------- ---------- ----------- -----------
S001 New York-1st FLAG 3050000.000 4011000.000
S003 Chicago-1st FLAG 323000.000 330500.000
S007 San Antonio-1st FLAG 268500.000 271500.000
S011 Austin-1st FLAG 253000.000 245000.000
SQL
복사
