11-6-1. ROWS와 RANGE
분석함수의 OVER 절에 ROWS나 RANGE를 지정해 레코드별 분석대상을 세밀하게 나눌 수 있다.
먼저 간단한 예제를 살펴보자. 아래는 분석함수를 적용하기 전 기본 데이터다.
-- [SQL-11-6-1-a] 기본 데이터, 일자별 주문 건수
SELECT DATE_FORMAT(T1.OrdDtm,'%Y%m%d') Ymd
,COUNT(*) OrdCnt
FROM startdb.Ord T1
WHERE T1.OrdDtm >= STR_TO_DATE('20230101','%Y%m%d')
AND T1.OrdDtm < STR_TO_DATE('20230106','%Y%m%d')
GROUP BY DATE_FORMAT(T1.OrdDtm,'%Y%m%d')
ORDER BY Ymd ASC;
Ymd OrdCnt
-------- ------
20230101 9
20230102 300
20230103 9
20230104 9
20230105 495
SQL
복사
위 SQL은 일자별로 주문 건수를 구하고 있다. 위 SQL(데이터)을 인라인 뷰로 처리한 후에 아래와 같이 SUM OVER 분석함수를 적용해보자. OVER 절에는 ORDER BY와 함께 ROWS를 적용하고 있다.
-- [SQL-11-6-1-b] ORDER BY ~ ROWS를 사용해 분석대상을 레코드별로 다르게 지정
SELECT T2.Ymd ,T2.OrdCnt
,SUM(T2.OrdCnt) OVER() SumOvOrdCnt
,SUM(T2.OrdCnt) OVER(
ORDER BY T2.Ymd ASC
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) SumOv2PrCur
FROM (
SELECT DATE_FORMAT(T1.OrdDtm,'%Y%m%d') Ymd
,COUNT(*) OrdCnt
FROM startdb.Ord T1
WHERE T1.OrdDtm >= STR_TO_DATE('20230101','%Y%m%d')
AND T1.OrdDtm < STR_TO_DATE('20230106','%Y%m%d')
GROUP BY DATE_FORMAT(T1.OrdDtm,'%Y%m%d')
ORDER BY Ymd ASC
) T2;
Ymd OrdCnt SumOvOrdCnt SumOv2PrCur
-------- ------ ----------- -----------
20230101 9 822 9
20230102 300 822 309
20230103 9 822 318
20230104 9 822 318
20230105 495 822 513
SQL
복사
•
SUM() OVER(): 레코드별로 분석함수를 제외한 결과 집합 전체가 분석대상이다.
•
SUM() OVER(ORDER BY T2.Ymd ASC ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)
◦
레코드별로 분석대상은 분석함수를 제외한 결과 집합에서,
▪
Ymd ASC 방향 기준으로,
▪
2건 이전(2 PRECEDING)부터 현재 레코드(CURRENT ROW)까지가
▪
분석대상이다.
이처럼 OVER 절에 ORDER BY와 함께 ROWS를 지정해 레코드별로 분석대상을 다르게 지정할 수 있다.
ROWS와 RANGE의 기본 사용법은 다음과 같다.
•
SUM(OrdAmt) OVER(ORDER BY OrdYm ROWS BETWEEN [A] AND [B])
◦
레코드별로 분석대상을 세밀하게 나누는 ROWS와 RANGE는 OVER 절의 ORDER BY에 영향을 받는다.
◦
OVER 절에 PARTITION BY가 사용되었다면, PARTITION 별로 ROWS와 RANGE가 적용된다.
•
'ROWS BETWEEN [A] AND [B]' 또는 'RANGE BETWEEN [A] AND [B]'로 분석 대상을 지정
◦
각 로우 별로, [A]부터 [B]까지만 분석 처리
◦
ROWS는 레코드 단위로 분석대상을 지정, RANGE는 값 기준으로 분석대상을 지정한다.
◦
[A]와 [B]에는 다음의 항목들이 올 수 있다.
◦
UNBOUNDED PRECEDING: 분석대상의 가장 첫 번째 데이터(레코드)를 뜻한다.
◦
n PRECEDING: 분석대상 내에서 현재 레코드 이전 몇 단계의 레코드를 뜻한다.
◦
UNBOUNDED FOLLOWING: 분석대상의 가장 마지막 데이터(레코드)를 뜻한다.
◦
n FOLLOWING: 분석대상 내에서 현재 레코드 이후 몇 단계의 레코드를 뜻한다.
◦
CURRENT ROW: 현재 레코드를 뜻한다.
◦
첫 번째와 마지막, 몇 단계 이전과 이후는 OVER 절의 ORDER BY에 따라 달라진다.
ROWS와 RANGE의 차이를 이해하기는 좀 어렵다. 간단히 말하면 ROWS는 물리적 레코드 단위로 이전과 이후를 구분한다. RANGE는 값으로 이전과 이후를 구분한다. 같은 값을 가진 여러 레코드를 하나의 단위로 처리한다. 필자의 경우 RANGE를 써야할 경우는 지금까지 잘 없었다. ROWS만 이해하고 넘어가도 우선은 충분하리라 생각한다. 특수한 경우, ROWS를 해결할 수 없을 때 이 설명을 다시 생각해보기 바란다.
이번에는 레코드별 분석대상으로 1 건 이전부터 1건 이후까지로 처리해보자. 다음과 같다.
-- [SQL-11-6-1-c] 1건 이전부터 1건 이후까지
SELECT T2.Ymd ,T2.OrdCnt
,SUM(T2.OrdCnt) OVER(
ORDER BY T2.Ymd ASC
ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) SumOv1Pr1Fo
FROM (
SELECT DATE_FORMAT(T1.OrdDtm,'%Y%m%d') Ymd
,COUNT(*) OrdCnt
FROM startdb.Ord T1
WHERE T1.OrdDtm >= STR_TO_DATE('20230101','%Y%m%d')
AND T1.OrdDtm < STR_TO_DATE('20230106','%Y%m%d')
GROUP BY DATE_FORMAT(T1.OrdDtm,'%Y%m%d')
ORDER BY Ymd ASC
) T2;
Ymd OrdCnt SumOv1Pr1Fo
-------- ------ -----------
20230101 9 309
20230102 300 318
20230103 9 318
20230104 9 513
20230105 495 504
SQL
복사
이처럼 레코드별로 분석대상을 세밀하게 조정이 가능하다는 것을 기억하고 필요한 곳에 잘 사용할 수 있기 바란다.
11-6-2. RUNNING TOTAL(누계)
Running total(누계)는 순차적으로 누적되는 합계를 뜻한다. OVER 절에 ROWS나 RANGE를 적용해 손쉽게 누계를 구현할 수 있다.
앞에서 사용한 SQL에 ROWS를 변경해 누계를 구해보자.
-- [SQL-11-6-2-a] 누계 구하기, 분석대상 처음부터 자신까지
SELECT T2.Ymd ,T2.OrdCnt
,SUM(T2.OrdCnt) OVER(
ORDER BY T2.Ymd ASC
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) RunTotal
FROM (
SELECT DATE_FORMAT(T1.OrdDtm,'%Y%m%d') Ymd
,COUNT(*) OrdCnt
FROM startdb.Ord T1
WHERE T1.OrdDtm >= STR_TO_DATE('20230101','%Y%m%d')
AND T1.OrdDtm < STR_TO_DATE('20230106','%Y%m%d')
GROUP BY DATE_FORMAT(T1.OrdDtm,'%Y%m%d')
ORDER BY Ymd ASC
) T2;
Ymd OrdCnt RunTotal
-------- ------ --------
20230101 9 9
20230102 300 309
20230103 9 318
20230104 9 327
20230105 495 822
SQL
복사
UNBOUNDED PRECEDING은 분석대상 중 가장 첫 번째 레코드를 의미한다. 이때, 가장 첫 번째의 기준은 OVER 절의 ORDER BY에 의해 결정된다. 레코드별로 UNBOUNDED PRECEDING은 동일하며, CURRENT ROW는 레코드별로 자기 자신이 되므로 자연스럽게 누계가 만들어진다.
11-6-3. DEFAULT RANGE
앞에서 누계를 구하기 위해 OVER 절에 ORDER BY와 함께 ROWS를 사용했다. 이번에는 다음과 같이 ORDER BY는 유지하고 ROWS는 생략하고 SQL을 실행해보자. ROWS를 지정하지 않았지만 누계가 구해지는 것을 알 수 있다.
-- [SQL-11-6-3-a] 주문금액 누계 추가(OVER 절 ROWS,RANGE 생략)
SELECT T2.*
,SUM(T2.SumOrdAmt) OVER(ORDER BY T2.Ym) RunTotalAmt
FROM (
SELECT DATE_FORMAT(T1.OrdDtm,'%Y%m') Ym
,SUM(T1.OrdAmt) SumOrdAmt
FROM startdb.Ord T1
WHERE T1.OrdDtm >= STR_TO_DATE('20230101','%Y%m%d')
AND T1.OrdDtm < STR_TO_DATE('20230601','%Y%m%d')
GROUP BY DATE_FORMAT(T1.OrdDtm,'%Y%m')
ORDER BY Ym ASC
) T2;
Ym SumOrdAmt RunTotalAmt
------ ------------- --------------
202301 16395000.000 16395000.000
202302 17027000.000 33422000.000
202303 110821000.000 144243000.000
202304 517937500.000 662180500.000
202305 535422500.000 1197603000.000
SQL
복사
이처럼 ROWS를 지정하지 않아도 누계가 구해지는 이유는 OVER 절의 ORDER BY만 사용해도 디폴트로 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW가 적용되기 때문이다. ROWS나 RANGE 모두 결과가 같은 이유는 ORDER BY의 컬럼인 Ym이 레코드별로 모두 다르기 때문이다. RANGE는 값에 따라 분석대상 단위를 구분해낸다. 만약에 Ym 값이 같은 레코드가 존재한다면 해당 레코드들은 하나의 분석대상 단위로 움직이게 된다. 상황에 따라 ROWS나 RANGE를 적절하게 선택해 사용하기 바란다.
11-6-4. PARTITION BY
OVER 절에 PARTITION BY를 사용한 후에 ROWS나 RANGE를 사용하면 PARTITION 별로 나누어진 분석대상에 대해서만 ROWS와 RANGE가 적용된다. 다음 SQL을 살펴보자.
-- [SQL-11-6-4-a] PARTITION BY별로 누계 처리하기
SELECT T4.*
,SUM(T4.SumOrdQty) OVER(
PARTITION BY T4.ItemId
ORDER BY T4.OrdYm ASC
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
) SumOrdQtyBf2Now
FROM (
SELECT DATE_FORMAT(T1.OrdDtm,'%Y%m') OrdYm ,T3.ItemId
,MAX(T3.ItemNm) ItemNm
,SUM(T2.OrdQty) SumOrdQty
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('20220101','%Y%m%d')
AND T1.OrdDtm < STR_TO_DATE('20220601','%Y%m%d')
AND T1.ShopId = 'S001'
AND T3.ItemCat = 'BKR'
GROUP BY DATE_FORMAT(T1.OrdDtm,'%Y%m') ,T3.ItemId
) T4;
OrdYm ItemId ItemNm SumOrdQty SumOrdQtyBf2Now
------ ------ ------------------- --------- ---------------
202201 BGLR Bagel(R) 15 15
202202 BGLR Bagel(R) 9 24
202203 BGLR Bagel(R) 13 37
202205 BGLR Bagel(R) 13 35
202201 BMFR Blueberry Muffin(R) 15 15
202202 BMFR Blueberry Muffin(R) 10 25
202203 BMFR Blueberry Muffin(R) 12 37
202205 BMFR Blueberry Muffin(R) 17 39
202201 CMFR Chocolate Muffin(R) 10 10
202202 CMFR Chocolate Muffin(R) 5 15
202203 CMFR Chocolate Muffin(R) 14 29
202205 CMFR Chocolate Muffin(R) 8 27
SQL
복사
11-6-5. 이동평균 구하기
이동평균은 최근 데이터 몇 건에 대한 평균 수치다. 이동평균은 일별로 편차가 큰 수치를 평균을 이용해 부드러운 움직임을 표현하기 위해 사용한다. 특히 주식 분석에서 자주 사용되는 기법이지만, 주식뿐만이 아닌 다양한 곳에서 활용 가능하다.
이동평균의 기본 공식은 다음과 같다.
•
3일 이동평균: 오늘을 포함한 3일간 수치의 평균 값
◦
(2일전 + 1일전 + 오늘) / 3
아래는 삼성전자 주가 차트에 이동평균을 추가한 것이다. 이동평균의 구간이 크면 클수록 주가의 흐름이 완만하게 보이는 것을 알 수 있다.
실습 데이터를 통해 이동평균을 구현해보자. 아래는 S001 매장의 2022년 1월 주문을 일자별로 조회하는 SQL이다. 주문이 없는 일자도 출력될 수 있도록 날짜 테이블과 매장 테이블을 크로스 조인 처리한 다음에 일별 주문 데이터를 아우터 조인 처리하고 있다.
-- [SQL-11-6-5-a] 매장별 일별 주문
SELECT T1.ShopId ,T1.ShopNm ,T1.BaseDt OrdDt,IFNULL(T2.OrdQty,0) OrdQty
FROM (
SELECT A.ShopId
,A.ShopNm
,B.BaseDt
FROM startdb.Shop A
CROSS JOIN startdb.BaseDt B
WHERE A.ShopId = 'S001'
AND B.BaseDt >= STR_TO_DATE('20220101','%Y%m%d')
AND B.BaseDt < STR_TO_DATE('20220201','%Y%m%d')
) T1
LEFT OUTER JOIN (
SELECT C.ShopId ,DATE(C.OrdDtm) OrdDt ,COUNT(*) OrdQty
FROM startdb.Ord C
WHERE C.ShopId = 'S001'
AND C.OrdDtm >= STR_TO_DATE('20220101','%Y%m%d')
AND C.OrdDtm < STR_TO_DATE('20220201','%Y%m%d')
GROUP BY C.ShopId ,DATE(C.OrdDtm)
) T2
ON (T2.ShopId = T1.ShopId
AND T2.OrdDt = T1.BaseDt)
ORDER BY T1.BaseDt;
ShopId ShopNm OrdDt OrdQty
------ ------------ ---------- ------
S001 New York-1st 2022-01-01 0
S001 New York-1st 2022-01-02 19
S001 New York-1st 2022-01-03 0
S001 New York-1st 2022-01-04 0
S001 New York-1st 2022-01-05 26
S001 New York-1st 2022-01-06 0
S001 New York-1st 2022-01-07 0
S001 New York-1st 2022-01-08 0
S001 New York-1st 2022-01-09 0
... 생략 ...
SQL
복사
위 SQL의 데이터를 시각화 해보면 다음과 같다. 주문수량이 대다수의 날은 0이고 주문 수량이 존재하는 날은 20개 이상인 것을 알 수 있다. (실습을 위한 테스트 데이터이므로 현실적인 데이터와는 차이가 있다.)
데이터의 시각화 결과가 위와 같다면, 데이터의 흐름이 눈에 잘 들어오지 않는다. 데이터의 흐름이 더 잘보이게 하기 위해서 다음과 같이 이동평균을 추가해보자.
-- [SQL-11-6-5-b] 매장별 일별 주문(MA7)
SELECT T1.ShopId ,T1.ShopNm ,T1.BaseDt OrdDt,IFNULL(T2.OrdQty,0) OrdQty
,ROUND(
AVG(IFNULL(T2.OrdQty,0))
OVER( ORDER BY T1.BaseDt
ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)
,2) OrdQtyMA7
FROM (
SELECT A.ShopId
,A.ShopNm
,B.BaseDt
FROM startdb.Shop A
CROSS JOIN startdb.BaseDt B
WHERE A.ShopId = 'S001'
AND B.BaseDt >= STR_TO_DATE('20220101','%Y%m%d')
AND B.BaseDt < STR_TO_DATE('20220201','%Y%m%d')
) T1
LEFT OUTER JOIN (
SELECT C.ShopId ,DATE(C.OrdDtm) OrdDt ,COUNT(*) OrdQty
FROM startdb.Ord C
WHERE C.ShopId = 'S001'
AND C.OrdDtm >= STR_TO_DATE('20220101','%Y%m%d')
AND C.OrdDtm < STR_TO_DATE('20220201','%Y%m%d')
GROUP BY C.ShopId ,DATE(C.OrdDtm)
) T2
ON (T2.ShopId = T1.ShopId
AND T2.OrdDt = T1.BaseDt)
ORDER BY T1.BaseDt;
ShopId ShopNm OrdDt OrdQty OrdQtyMA7
------ ------------ ---------- ------ ---------
S001 New York-1st 2022-01-01 0 0.00
S001 New York-1st 2022-01-02 19 9.50
S001 New York-1st 2022-01-03 0 6.33
S001 New York-1st 2022-01-04 0 4.75
S001 New York-1st 2022-01-05 26 9.00
S001 New York-1st 2022-01-06 0 7.50
S001 New York-1st 2022-01-07 0 6.43
S001 New York-1st 2022-01-08 0 6.43
... 생략 ...
SQL
복사
AVG OVER 분석함수를 사용하면서 ROWS를 6 PRECEDING AND CURRENT ROW로 지정해 6건 전 레코드부터 자신까지의 평균을 구해 7일 이동평균을 구현했다. 위 결과를 시각화해보면 다음과 같다. 테스트 데이터 자체가 임의로 만들었기 때문에, MA7(7일이동평균)의 움직임이 그럴싸하지는 않다.
이동평균을 구하기 위해 AVG OVER를 사용했다. 만약에 최근 100일간의 고가나 최근 100일간의 저가를 구한다면 MAX OVER나 MIN OVER를 ROWS BETWEEN 100 PRECEDING AND CURRENT ROW로 정의해서 쉽게 구해낼 수 있다.
여러분들이 다루는 데이터에 일별로 들쑥날쑥한 데이터가 있다면 이동평균 분석 기법을 추가해보기 바란다.
BOOSTER QUIZ
BOOSTER QUIZ ERD
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
복사
