11-1-1. 분석함수
분석함수는 조회 결과에 추가적인 분석을 처리하는 함수다. SUM, MIN, MAX, AVG, COUNT와 같은 집계함수는 분석함수 용도로도 사용할 수 있다. 이로 인해 집계함수와 분석함수를 사용하는데 혼선이 생긴다. 집계함수와 분석함수는 사용법과 동작 방식, 처리 대상도 완전히 다르다. 집계함수는 그룹별 데이터에 대해 집계를 처리하는 반면에 분석함수는 조회된 레코드의 상태를 유지하며 레코드별로 분석대상에 대해 분석을 수행할 수 있다.
분석함수를 활용하면 다음과 같은 작업들을 비교적 쉽게 처리할 수 있다.
•
전체 수치 대비 현재 레코드의 수치 비율 구하기
•
현재 레코드의 수치 누계(RUNNING TOTAL) 구하기
•
현재 레코드의 기준에 따른 순위 번호 부여하기
분석함수에는 집계 분석함수와 순위 분석함수 그리고 다른 레코드의 값에 접근하는 분석함수가 있다. 종류별로 정리해보면 다음과 같다.
•
집계 분석함수: SUM, MIN, MAX, AVG, COUNT
•
순위 분석함수: RANK ,DENSE_RANK, ROW_NUMBER
•
다른 레코드 접근 분석함: LEAD, LAG, FIRST_VALUE, LAST_VALUE
이외에도 다양한 분석함수가 있으니, 아래 URL을 참고하기 바란다.
11-1-2. 분석대상
분석함수를 잘 사용하려면 “분석대상”에 대한 개념을 이해해야 한다. 분석함수의 분석대상은 분석함수를 제외한(분석함수를 적용하기 전) SQL 결과다. 분석함수는 항상 OVER 절을 동반한다. 그리고 OVER 절을 활용해 레코드별로 분석대상을 다르게 조정할 수도 있다.
아래 SQL을 살펴보자. COUNT() OVER() 분석함수를 사용한 SQL이다.
-- [SQL-11-1-2-a] COUNT 분석함수
SELECT T1.ShopID ,T1.ShopNm ,T1.ShopOperTp
,COUNT(*) OVER() AllCnt
FROM startdb.Shop T1
WHERE T1.ShopOperTp = 'DRCT'
AND T1.ShopStartYmd LIKE '2018%'
ORDER BY T1.ShopId;
ShopID ShopOperTp ShopStartYmd AllCnt
------ ---------- ------------ ------
S015 DRCT 20180403 7
S024 DRCT 20180324 7
S028 DRCT 20180327 7
S045 DRCT 20180402 7
S047 DRCT 20180405 7
S048 DRCT 20180409 7
S050 DRCT 20180318 7
SQL
복사
레코드별로 COUNT OVER()의 결과로 7이 출력되고 있다. 왜 7이 나온 것인지 이해하려면 분석대상에 대해 알아야 한다. 위 SQL에서 분석함수를 제외하면 아래와 같다. 바로, 아래 SQL의 결과가 분석대상이다. “분석함수를 제외한 SQL의 결과 집합이 분석대상이다.” 이 부분을 반드시 기억하자. 주의할 점은 아래와 같이 SELECT 절에 올 수 있는 모든 컬럼은 분석대상이 된다는 점이다.
-- [SQL-11-1-2-b] 분석함수를 제외한 SQL 결과(=분석대상)
SELECT T1.*
FROM startdb.Shop T1
WHERE T1.ShopOperTp = 'DRCT'
AND T1.ShopStartYmd LIKE '2018%'
ORDER BY T1.ShopId;
ShopId ShopNm ShopSize ShopOperTp ... ShopEndYmd
------ --------------- -------- ---------- ... ----------
S015 Charlotte-1st 55 DRCT ... NULL
S024 Houston-2nd 73 DRCT ... NULL
S028 San Diego-2nd 81 DRCT ... NULL
S045 Phoenix-3rd 115 DRCT ... NULL
S047 San Antonio-3rd 119 DRCT ... NULL
S048 San Diego-3rd 121 DRCT ... NULL
S050 San Jose-3rd 125 DRCT ... NULL
SQL
복사
분석대상으로 출력된 데이터는 일곱 건이다. 레코드별로 분석함수의 결과는 모두 7이었다. 우리는 아래와 같이 분석함수를 사용했다.
•
COUNT(*) OVER()
위와 같이 분석함수의 OVER 절에 아무 내용도 적지 않아면, 레코드별로 분석대상은 분석대상 전체가 된다. 그러므로 레코드별로 분석함수의 결과는 분석대상의 전체 건수인 7이 된다. OVER 절에 다양한 옵션을 정의하면 레코드별로 분석대상을 다르게 지정할 수 있다. 이 방법은 뒤에서 설명한다.
SUM() OVER() 분석함수를 살펴보자.
-- [SQL-11-1-2-c] SUM 분석함수
SELECT T1.OrdNo ,T1.OrdDtm ,T1.OrdAmt
,SUM(T1.OrdAmt) OVER() SumOverAmt
FROM startdb.Ord T1
WHERE T1.OrdDtm >= STR_TO_DATE('20230101','%Y%m%d')
AND T1.OrdDtm < STR_TO_DATE('20230102','%Y%m%d');
OrdNo OrdDtm OrdAmt SumOverAmt
----- ------------------- -------- ----------
67277 2023-01-01 08:00:00 4500.000 49000.000
67278 2023-01-01 08:00:00 4500.000 49000.000
67279 2023-01-01 08:00:00 4500.000 49000.000
67280 2023-01-01 08:00:00 8000.000 49000.000
67281 2023-01-01 08:00:00 5000.000 49000.000
67282 2023-01-01 19:30:00 5000.000 49000.000
67283 2023-01-01 19:30:00 4000.000 49000.000
67284 2023-01-01 20:30:00 4500.000 49000.000
67285 2023-01-01 20:30:00 9000.000 49000.000
SQL
복사
레코드별로 SUM(T1.OrdAmt) OVER()의 결과가 모두 49000으로 동일하게 출력되었다. 49000은 분석대상(분석함수를 적용 전의 SQL 결과)의 OrdAmt를 모두 합한 값이다. OVER 절에 아무 내용이 없으므로 레코드별 분석대상은 분석대상 전체다. 그러므로 모든 레코드에 동일한 49000이란 값이 출력된다.
아래 SQL은 COUNT, SUM, AVG, MAX, MIN 분석함수를 사용하는 예제다. 분석대상 의미를 떠올리며 살펴보기 바란다.
-- [SQL-11-1-2-d] 다양한 분석함수
SELECT T1.OrdNo ,T1.OrdDtm ,T1.OrdAmt ,T1.OrdNo
,COUNT(*) OVER() CntOver
,SUM(T1.OrdAmt) OVER() SumOverAmt
,ROUND(AVG(T1.OrdAmt) OVER(),2) AvgOverAmt
,MAX(T1.OrdDtm) OVER() MaxOverDtm
,MIN(T1.OrdDtm) OVER() MinOverDtm
FROM startdb.Ord T1
WHERE T1.OrdDtm >= STR_TO_DATE('20221101','%Y%m%d')
AND T1.OrdDtm < STR_TO_DATE('20221104','%Y%m%d')
AND T1.ShopId = 'S003';
OrdNo OrdDtm OrdAmt OrdNo CntOver SumOverAmt AvgOverAmt MaxOverDtm MinOverDtm
----- ------------------- --------- ----- ------- ---------- ---------- ------------------- -------------------
45904 2022-11-01 08:00:00 4500.000 45904 7 40000.000 5714.29 2022-11-03 08:00:00 2022-11-01 08:00:00
45913 2022-11-02 08:00:00 4000.000 45913 7 40000.000 5714.29 2022-11-03 08:00:00 2022-11-01 08:00:00
45939 2022-11-02 10:00:00 4500.000 45939 7 40000.000 5714.29 2022-11-03 08:00:00 2022-11-01 08:00:00
45955 2022-11-02 10:00:00 6500.000 45955 7 40000.000 5714.29 2022-11-03 08:00:00 2022-11-01 08:00:00
45970 2022-11-02 10:00:00 13000.000 45970 7 40000.000 5714.29 2022-11-03 08:00:00 2022-11-01 08:00:00
46046 2022-11-02 11:00:00 4000.000 46046 7 40000.000 5714.29 2022-11-03 08:00:00 2022-11-01 08:00:00
46213 2022-11-03 08:00:00 3500.000 46213 7 40000.000 5714.29 2022-11-03 08:00:00 2022-11-01 08:00:00
SQL
복사
분석함수의 분석대상을 이해했는지 살펴보기 위해 아래의 빈칸 채우기를 해보자. 아직은 레코드별로 분석대상이 전체로 모두 같아 크게 어려움이 없을 것이다.
-- [SQL-11-1-2-e] 분석함수 빈칸 채워보기
SELECT T1.ItemId ,T1.ItemNm ,T1.LaunchDt
,COUNT(*) OVER() AllCnt
,MIN(T1.LaunchDt) OVER() FirstLaunch
,MAX(T1.LaunchDt) OVER() LastLaunch
FROM startdb.Item T1
WHERE T1.ItemCat = 'BEV'
ORDER BY T1.ItemId;
ItemId ItemNm LaunchDt AllCnt FirstLaunch LastLaunch
------ ----------------- ---------- ------ ----------- ----------
CITR Yuzu Ade(R) 2023-04-01 ( ) ( ) ( )
HCHB Hot Chocolate(B) 2019-01-01 ( ) ( ) ( )
HCHR Hot Chocolate(R) 2019-01-01 ( ) ( ) ( )
LEMR Lemonade(R) 2019-01-01 ( ) ( ) ( )
ZAMB Grapefruit Ade(R) 2023-04-01 ( ) ( ) ( )
SQL
복사
11-1-3. 분석함수 맛보기
간단하게 분석함수를 사용해보자. 분석함수를 이용해 전체 대비 비율 값을 구할 수 있다. 아래는 S003 매장의 22년 11월 1일부터 22년 11월 3일까지의 주문 데이터를 조회하고 있다. 분석함수를 이용해 조회된 데이터의 주문금액합계(SumOverAmt)와 최대주문금액(MaxOverAmt)을 구하고 있다.
-- [SQL-11-1-3-a]
SELECT T1.OrdNo ,T1.OrdDtm ,T1.OrdAmt
,SUM(T1.OrdAmt) OVER() SumOverAmt
,MAX(T1.OrdAmt) OVER() MaxOverAmt
FROM startdb.Ord T1
WHERE T1.OrdDtm >= STR_TO_DATE('20221101','%Y%m%d')
AND T1.OrdDtm < STR_TO_DATE('20221104','%Y%m%d')
AND T1.ShopId = 'S003';
OrdNo OrdDtm OrdAmt SumOverAmt MaxOverAmt
----- ------------------- --------- ---------- ----------
45904 2022-11-01 08:00:00 4500.000 40000.000 13000.000
45913 2022-11-02 08:00:00 4000.000 40000.000 13000.000
45939 2022-11-02 10:00:00 4500.000 40000.000 13000.000
45955 2022-11-02 10:00:00 6500.000 40000.000 13000.000
45970 2022-11-02 10:00:00 13000.000 40000.000 13000.000
46046 2022-11-02 11:00:00 4000.000 40000.000 13000.000
46213 2022-11-03 08:00:00 3500.000 40000.000 13000.000
SQL
복사
위와 같은 결과를 이용해 각 레코드별 전체대비주문금액비율(OrdAmtRatioToTotal)과 최대대비주문금액비율(OrdAmtRatioToMax)을 쉽게 구해낼 수 있다. 아래와 같이 SQL을 작성해보자.
-- [SQL-11-1-3-b]
SELECT T2.OrdNo ,T2.OrdDtm ,T2.OrdAmt
,ROUND(T2.OrdAmt / T2.SumOverAmt,2) OrdAmtRatioToTotal
,ROUND(T2.OrdAmt / T2.MaxOverAmt,2) OrdAmtRatioToMax
FROM (
SELECT T1.OrdNo ,T1.OrdDtm ,T1.OrdAmt
,SUM(T1.OrdAmt) OVER() SumOverAmt
,MAX(T1.OrdAmt) OVER() MaxOverAmt
FROM startdb.Ord T1
WHERE T1.OrdDtm >= STR_TO_DATE('20221101','%Y%m%d')
AND T1.OrdDtm < STR_TO_DATE('20221104','%Y%m%d')
AND T1.ShopId = 'S003'
) T2;
OrdNo OrdDtm OrdAmt OrdAmtRatioToTotal OrdAmtRatioToMax
----- ------------------- --------- ------------------ ----------------
45904 2022-11-01 08:00:00 4500.000 0.11 0.35
45913 2022-11-02 08:00:00 4000.000 0.10 0.31
45939 2022-11-02 10:00:00 4500.000 0.11 0.35
45955 2022-11-02 10:00:00 6500.000 0.16 0.50
45970 2022-11-02 10:00:00 13000.000 0.33 1.00
46046 2022-11-02 11:00:00 4000.000 0.10 0.31
46213 2022-11-03 08:00:00 3500.000 0.09 0.27
SQL
복사
위 SQL은 아래와 같이 인라인 뷰를 제거 할 수도 있다. 분석함수 사용이 아직 익숙하지 않다면 아래와 같이 작성하는 것보다 위와 같이 인라인 뷰 안에서 분석함수를 사용하는 것을 권장한다.
-- [SQL-11-1-3-c]
SELECT T1.OrdNo ,T1.OrdDtm ,T1.OrdAmt
,ROUND(T1.OrdAmt / SUM(T1.OrdAmt) OVER(),2) OrdAmtRatioToTotal
,ROUND(T1.OrdAmt / MAX(T1.OrdAmt) OVER(),2) OrdAmtRatioToMax
FROM startdb.Ord T1
WHERE T1.OrdDtm >= STR_TO_DATE('20221101','%Y%m%d')
AND T1.OrdDtm < STR_TO_DATE('20221104','%Y%m%d')
AND T1.ShopId = 'S003';
SQL
복사
11-1. BOOSTER QUIZ
BOOSTER QUIZ ERD
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
복사
