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 결과로 얻은 회원(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
인라인 뷰와 LIMIT를 사용해 두 구간이나 두 집단에서 동시에 TOP-N에 속하는 데이터를 찾아보자. 아래와 같은 로직의 데이터를 조회하려고 한다.
아래와 같이 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의 결과를 보면 한 건의 데이터만 조회되었다. 아래 그림을 살펴보면 왜 한 건만 나왔는지 쉽게 알 수 있다.