BOOSTER QUIZ 11-1-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.OrdNo ASC;
-- (결과-1)
OrdNo ShopId OrdDtm OrdAmt
----- ------ ------------------- --------
16617 S092 2021-09-04 13:30:00 3500.000
16831 S092 2021-09-06 13:30:00 7500.000
16896 S092 2021-09-08 13:30:00 4500.000
17119 S092 2021-09-13 13:30:00 4500.000
17202 S092 2021-09-21 13:30:00 7000.000
-- (SQL-2)
SELECT T1.OrdNo ,T1.ShopId ,T1.OrdDtm, T1.OrdAmt
,SUM(T1.OrdAmt) OVER() OrdOverAmt
,COUNT(*) OVER() CntOver
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.OrdNo ASC;
-- (결과-2)
OrdNo ShopId OrdDtm OrdAmt OrdOverAmt CntOver
----- ------ ------------------- -------- ---------- -------
16617 S092 2021-09-04 13:30:00 3500.000
16831 S092 2021-09-06 13:30:00 7500.000
16896 S092 2021-09-08 13:30:00 4500.000
17119 S092 2021-09-13 13:30:00 4500.000
17202 S092 2021-09-21 13:30:00 7000.000
SQL
복사
풀이
BOOSTER QUIZ 11-1-2
(SQL-1)과 (결과-1)을 참고해, (SQL-2)를 실행하면 나올 (결과-2)를 채우시오. 그리고 (SQL-1)이 출려하는 리스트의 의미를 서술하시오.
•
(SQL-2)는 (SQL-1)에 분석함수만 추가한 SQL입니다.
•
SQL을 실행하지 않고 (결과-2)를 예측해서 채우시오.
-- (SQL-1)
SELECT T1.ShopId ,T1.ShopNm ,T1.ChairQty
FROM startdb.Shop T1
WHERE T1.ShopOperTp = 'FLAG'
AND EXISTS(
SELECT COUNT(*)
FROM startdb.Ord X
WHERE X.ShopId = T1.ShopId
AND X.OrdDtm >= STR_TO_DATE('20211101','%Y%m%d')
AND X.OrdDtm < STR_TO_DATE('20211201','%Y%m%d')
HAVING COUNT(*) >= 30
)
ORDER BY T1.ShopId;
-- (결과-1)
ShopId ShopNm ChairQty
------ ------ --------
S001 NM0001 8
S003 NM0003 14
S007 NM0007 28
S011 NM0011 13
-- (SQL-2)
SELECT T1.ShopId ,T1.ShopNm ,T1.ChairQty
,SUM(T1.ChairQty) OVER() SumOverChair
,COUNT(*) OVER() CntOver
,ROUND(AVG(T1.ChairQty) OVER(),1) AvgOverChair
FROM startdb.Shop T1
WHERE T1.ShopOperTp = 'FLAG'
AND EXISTS(
SELECT COUNT(*)
FROM startdb.Ord X
WHERE X.ShopId = T1.ShopId
AND X.OrdDtm >= STR_TO_DATE('20211101','%Y%m%d')
AND X.OrdDtm < STR_TO_DATE('20211201','%Y%m%d')
HAVING COUNT(*) >= 30
)
ORDER BY T1.ShopId;
-- (결과-2)
ShopId ShopNm ChairQty SumOverChair CntOver AvgOverChair
------ ------ -------- ------------ ------- ------------
S001 NM0001 8
S003 NM0003 14
S007 NM0007 28
S011 NM0011 13
SQL
복사
풀이
BOOSTER QUIZ 11-1-3
2022년 1월 1일 주문 목록을 조회해주세요. 조회된 주문의 전체주문금액과 전체주문건수도 컬럼으로 추가해서 보여주세요.
•
대상 테이블: 주문(Ord)
•
조회 조건: OrdDtm이 2022년 1월 1일인 데이터
•
조회 컬럼: OrdNo, OrdDtm, OrdAmt, SumOverAmt, CntOver
•
추가 조건:
◦
SumOverAmt는 2022년 1월 1일 주문의 OrdAmt의 합입니다. 분석함수로 처리하시오.
◦
CntOver는 2022년 1월 1일 주문의 건수입니다. 분석함수로 처리하시오.
OrdNo OrdDtm OrdAmt SumOverAmt CntOver
----- ------------------- -------- ---------- -------
10835 2021-01-01 08:00:00 3500.000 11500.000 3
10836 2021-01-01 08:00:00 4000.000 11500.000 3
10837 2021-01-01 08:00:00 4000.000 11500.000 3
SQL
복사
풀이
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
복사
풀이
BOOSTER QUIZ 11-3-1
2022년 1월 주문에 대해, 상품카테고리별 주문수량을 구해주세요, 상품카테고리별 주문수량에 대한 순위도 구해주세요.
•
대상 테이블: 주문(Ord), 주문상세(OrdDet), 상품(Item), 상품카테고리(ItemCat)
•
조회 조건: OrdDtm이 2022년 1월인 주문데이터
•
조회 컬럼: ItemCat ,ItemCatNm ,SumOverOrdQty, RankOverOrdQty
•
추가 조건
◦
ItemCat별 GROUP BY 처리하시오.
◦
SumOverOrdQty는 ItemCat별 OrdQty의 SUM
◦
RankOverOrdQty는 SumOverOrdQty에 따른 순위로서 판매수량이 가장 많으면 1이 됩니다.
ItemCat ItemCatNm SumOverOrdQty RankOverOrdQty
------- --------- ------------- --------------
COF Coffee 664 1
BEV Beverage 252 2
BKR Bakery 248 3
SQL
복사
풀이
BOOSTER QUIZ 11-3-2
2022년 1월 주문에 대해, 매장운영유형별 주문금액을 구해주세요. 조회된 데이터의 전체주문금액도 컬럼으로 추가해주시고, 매장운영유형별 주문금액 비율도 구해주세요.
•
대상 테이블: 매장(Shop), 주문(Ord), 기준코드(BaseCd)
•
조회 조건: OrdDtm이 2022년 1월인 주문데이터
•
조회 컬럼: ShopOperTp, ShopOperTpNm, SumOrdAmt, SumOrdAmtPercent
•
추가 조건
◦
ShopOperTp별 GROUP BY 처리하시오.
◦
ShopOperTpNm은 ShopOperTp에 대한 명칭으로 BaseCd에서 가져오시오.(스칼라 서브쿼리 사용)
◦
SumOrdAmt는 ShopOperTp별 OrdAmt의 SUM
◦
SumOverOrdAmt는 조회된 SumOrdAmt의 합으로서 분석함수로 처리합니다.
◦
SumOrdAmtPercent = SumOrdAmt / SumOverOrdAmt * 100
ShopOperTp ShopOperTpNm SumOrdAmt SumOverOrdAmt SumOrdAmtPercent
---------- ------------ ----------- ------------- ----------------
FLAG Flagship 2753000.000 4488500.000 61.33
DIST Distributor 991500.000 4488500.000 22.09
DRCT Directly 744000.000 4488500.000 16.58
SQL
복사
풀이
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
복사
풀이
BOOSTER QUIZ 11-5-1
(SQL-1)과 (결과-1)을 참고해, (SQL-2)를 실행하면 나올 (결과-2)를 채우시오.
•
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.OrdNo ASC;
-- (결과-1)
OrdNo ShopId OrdDtm OrdAmt
----- ------ ------------------- --------
16617 S092 2021-09-04 13:30:00 3500.000
16831 S092 2021-09-06 13:30:00 7500.000
16896 S092 2021-09-08 13:30:00 4500.000
17119 S092 2021-09-13 13:30:00 4500.000
17202 S092 2021-09-21 13:30:00 7000.000
-- (SQL-2)
SELECT T1.OrdNo ,T1.ShopId ,T1.OrdDtm, T1.OrdAmt
,LAG(T1.OrdAmt) OVER(ORDER BY T1.OrdDtm ASC) LagAscAmt
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.OrdNo ASC;
-- (결과-2)
OrdNo ShopId OrdDtm OrdAmt LagAscAmt
----- ------ ------------------- -------- ---------
16617 S092 2021-09-04 13:30:00 3500.000
16831 S092 2021-09-06 13:30:00 7500.000
16896 S092 2021-09-08 13:30:00 4500.000
17119 S092 2021-09-13 13:30:00 4500.000
17202 S092 2021-09-21 13:30:00 7000.000
SQL
복사
BOOSTER QUIZ 11-5-2
2022년 전체 주문에 대해 주문년월별 주문금액을 구해주세요. 전월 대비 현재월 주문금액 증감도 보여주세요.
•
대상 테이블: 주문(Ord)
•
조회 조건: OrdDtm이 2022년인 주문 데이터
•
조회 컬럼: OrdYm, SumOrdAmt, BeforeAmt, NowBeforeDiff
•
추가 조건
◦
OrdYm은 OrdDtm을 년월형태로 변환한 값입니다.
◦
OrdYm 별로 GROUP BY 하시오.
◦
SumOrdAmt는 OrdYm별 OrdAmt를 SUM한 값
◦
BeforeAmt는 바로 이전 월의 OrdAmt입니다.
◦
BeforeAmt는 LAG 분석함수로 처리하시오.
▪
분석함수 특성상 202201의 이전값은 NULL로 나와도 괜찮습니다
◦
NowBeforeDiff는 SumOrdAmt에서 BeforeAmt를 뺀 값입니다.(주문금액 증감)
OrdYm SumOrdAmt BeforeAmt NowBeforeDiff
------ ------------ ------------ -------------
202201 4488500.000 NULL NULL
202202 4702500.000 4488500.000 214000.000
202203 5995000.000 4702500.000 1292500.000
202204 476500.000 5995000.000 -5518500.000
202205 9270000.000 476500.000 8793500.000
202206 4300500.000 9270000.000 -4969500.000
202207 19053500.000 4300500.000 14753000.000
202208 19568000.000 19053500.000 514500.000
202209 19510500.000 19568000.000 -57500.000
202210 19400500.000 19510500.000 -110000.000
202211 19280500.000 19400500.000 -120000.000
202212 91350000.000 19280500.000 72069500.000
SQL
복사
풀이
BOOSTER QUIZ 11-6-1
2022년 12월 20일부터 2022년 12월 25일까지 아메리카노빅사이즈의 일별 주문 수량을 보여주세요. 일별로 주문수량이 증가하는 누계도 보여주세요.
•
대상 테이블: 주문(Ord), 주문상세(OrdDet), 상품(Item)
•
조회 조건: OrdDtm이 2022년 12월 20일부터 2022년 12월 25일이면서 ItemId가 AMB인 경우만
•
조회 컬럼: ItemId, ItemNm, OrdYmd, SumOrdQty, RunTotal
•
추가 조건
◦
OrdYmd는 OrdDtm을 %Y%m%d로 변환한 일자 데이터입니다.
◦
ItemId, OrdYmd별로 집계해서 주문수량합계(SumOrdQty)를 구해주세요.
◦
위의 결과를 인라인 뷰 처리후에, 인라인 뷰 바깥에서 분석함수를 사용해 누계(RunTotal)를 구합니다.
ItemId ItemNm OrdYmd SumOrdQty RunTotal
------ ------------ -------- --------- --------
AMB Americano(B) 20221220 1 1
AMB Americano(B) 20221221 17 18
AMB Americano(B) 20221222 206 224
AMB Americano(B) 20221223 252 476
AMB Americano(B) 20221224 205 681
AMB Americano(B) 20221225 205 886
SQL
복사
풀이
BOOSTER QUIZ 11-6-2
[BOOSTER QUIZ 11-6-1]의 SQL에서 아이스아메리카노빅(IAMB)도 추가해주세요. 누계를 상품별로 구하는 걸로 변경해주세요.
•
대상 테이블: 주문(Ord), 주문상세(OrdDet), 상품(Item)
•
조회 조건: OrdDtm이 2022년 12월 20일부터 2022년 12월 25일이면서 ItemId가 AMB이거나 IAMB인 경우만
•
조회 컬럼: ItemId, ItemNm, OrdYmd, SumOrdQty, RunTotal
•
추가 조건
◦
OrdYmd는 OrdDtm을 %Y%m%d로 변환한 일자 데이터입니다.
◦
ItemId, OrdYmd별로 집계해서 주문수량합계(SumOrdQty)를 구해주세요.
◦
위의 결과를 인라인 뷰 처리후에, 인라인 뷰 바깥에서 분석함수를 사용해 누계(RunTotal)를 구합니다. 이때, ItemId별로 누계를 처리합니다.
ItemId ItemNm OrdYmd SumOrdQty RunTotal
------ ----------------- -------- --------- --------
AMB Americano(B) 20221220 1 1
AMB Americano(B) 20221221 17 18
AMB Americano(B) 20221222 206 224
AMB Americano(B) 20221223 252 476
AMB Americano(B) 20221224 205 681
AMB Americano(B) 20221225 205 886
IAMB Iced Americano(B) 20221220 1 1
IAMB Iced Americano(B) 20221221 18 19
IAMB Iced Americano(B) 20221222 197 216
IAMB Iced Americano(B) 20221223 242 458
IAMB Iced Americano(B) 20221224 198 656
IAMB Iced Americano(B) 20221225 196 852
SQL
복사
풀이
BOOSTER QUIZ 11-6-3
2022년 7월의 회원가입수를 일별로 보여주세요. 회원가입이 없는 날도 0으로 출력되도록 해주세요. 최근10일간에 회원가입이 가장 많았던 수치도 보여주세요.
•
대상 테이블: 회원(Member), 기준일자(BaseDt)
•
조회 조건: JoinDtm이 2022년 7월인 회원
•
조회 컬럼: BaseYmd, JoinCnt, JoinCnt_MAX10
•
추가 조건
◦
가입이 없던 날도 출력하기 위해 BaseDt 테이블을 활용합니다.
◦
Member 테이블을 DATE_FORMAT(JoinDtm,’%Y%m%d’)별로 GROUP BY처리해서 인라인 뷰로 처리합니다.
▪
DATE_FORMAT(JoinDtm,’%Y%m%d’) 별로 회원수를 카운트(JoinCnt)합니다.
◦
BaseDt 테이블을 기준집합으로 Member 인라인 뷰와 아우터 조인합니다.
◦
조인된 결과에 대해 JoinCnt_MAX10을 구합니다.
▪
JoinCnt_MAX10은 오늘을 포한한 최근 10일간의 JoinCnt중에서 최대값입니다.
▪
아래와 같이 MAX OVER 분석함수의 ROWS를 지정해 처리합니다.
•
ROWS BETWEEN 9 PRECEDING AND CURRENT ROW
BaseYmd JoinCnt JoinCnt_MAX10
-------- ------- -------------
20220701 96 96
20220702 97 97
20220703 98 98
20220704 90 98
20220705 87 98
20220706 77 98
20220707 75 98
20220708 71 98
20220709 64 98
20220710 63 98
20220711 50 98
20220712 43 98
20220713 36 90
20220714 34 87
20220715 36 77
20220716 31 75
20220717 28 71
20220718 14 64
20220719 11 63
20220720 7 50
20220721 5 43
20220722 0 36
20220723 1 36
20220724 0 36
20220725 0 31
20220726 0 28
20220727 0 14
20220728 0 11
20220729 0 7
20220730 0 5
SQL
복사
풀이
