11-5-1. LAG
LAG와 LEAD는 자신이 아닌 다른 레코드의 값을 가져와 보여줄 수 있는 분석함수다. LAG는 “이전” 레코드의 데이터를 가져온다. “이전”의 기준은 OVER 절의 ORDER BY에 따라 결정된다.아래는 M0201 회원의 2023년 1월 주문을 조회한 SQL이다.
-- [SQL-11-5-1-a] 기본 데이터 조회
SELECT T1.OrdNo ,T1.OrdDtm ,T1.MemberId
FROM startdb.Ord T1
WHERE T1.MemberId = 'M0201'
AND T1.OrdDtm >= STR_TO_DATE('20230101','%Y%m%d')
AND T1.OrdDtm < STR_TO_DATE('20230201','%Y%m%d')
ORDER BY T1.OrdDtm ASC;
OrdNo OrdDtm MemberId
----- ------------------- --------
67634 2023-01-05 10:00:00 M0201
68174 2023-01-11 10:00:00 M0201
68768 2023-01-23 10:00:00 M0201
69308 2023-01-29 10:00:00 M0201
SQL
복사
위 SQL 결과에 각 레코드별로 이전 주문일시를 같이 표시해주려고 한다. 아래와 같이 LAG 분석함수를 사용할 수 있다.
-- [SQL-11-5-1-b] LAG를 이용해 이전 데이터 가져오기
SELECT T1.OrdNo ,T1.OrdDtm ,T1.MemberId
,LAG(T1.OrdDtm) OVER(ORDER BY T1.OrdDtm ASC) BefoOrdDtm
FROM startdb.Ord T1
WHERE T1.MemberId = 'M0201'
AND T1.OrdDtm >= STR_TO_DATE('20230101','%Y%m%d')
AND T1.OrdDtm < STR_TO_DATE('20230201','%Y%m%d')
ORDER BY T1.OrdDtm ASC;
OrdNo OrdDtm MemberId BefoOrdDtm
----- ------------------- -------- -------------------
67634 2023-01-05 10:00:00 M0201 NULL
68174 2023-01-11 10:00:00 M0201 2023-01-05 10:00:00
68768 2023-01-23 10:00:00 M0201 2023-01-11 10:00:00
69308 2023-01-29 10:00:00 M0201 2023-01-23 10:00:00
SQL
복사
위 결과에서 마지막 레코드의 BeforeOrdDtm 값을 보면 바로 이전 주문의 주문일시인 2023-01-23 10:00:00이 출력된 것을 알 수 있다. LAG를 이용해 이전 주문일시 값을 가져온 것이다. 주의해야 할 것은 첫 번째 레코드다. 첫 번째 레코드는 이전 데이터가 없으므로 BeforeOrdDtm이 NULL로 채워져 있다. 분석함수는 항상, 조회된 결과에 대해서만 분석을 수행한다. 실제 테이블에는 그 이전 데이터가 있더라도 조회되지 않았다면 분석대상에 포함되지 않는다.
LAG를 사용할 때, 가져올 “이전”의 기준은 OVER 절의 ORDER BY에 의해 결정된다. 그러므로 ORDER BY 컬럼을 오름차순(ASC)으로 처리했는지, 내림차순(DESC)으로 처리했는지에 따라 LAG의 결과가 달라진다. 아래 그림을 살펴보기 바란다.
-- [SQL-11-5-1-c] LAG를 이용해 이후 데이터 가져오기
SELECT T1.OrdNo ,T1.OrdDtm ,T1.MemberId
,LAG(T1.OrdDtm) OVER(ORDER BY T1.OrdDtm DESC) AftOrdDtm
FROM startdb.Ord T1
WHERE T1.MemberId = 'M0201'
AND T1.OrdDtm >= STR_TO_DATE('20230101','%Y%m%d')
AND T1.OrdDtm < STR_TO_DATE('20230201','%Y%m%d')
ORDER BY T1.OrdDtm ASC;
OrdNo OrdDtm MemberId AftOrdDtm
----- ------------------- -------- -------------------
67634 2023-01-05 10:00:00 M0201 2023-01-11 10:00:00
68174 2023-01-11 10:00:00 M0201 2023-01-23 10:00:00
68768 2023-01-23 10:00:00 M0201 2023-01-29 10:00:00
69308 2023-01-29 10:00:00 M0201 NULL
SQL
복사
11-5-2. LEAD
LEAD는 LAG와 반대다. LEAD는 “이후” 레코드의 데이터를 가져온다. LAG와 마찬가지로 OVER 절의 ORDER BY에 따라 “이후”가 결정된다. 결국 앞에서 설명했던 LAG의 ORDER BY만 적절히 사용하면 LEAD처럼 사용할 수 있다. 아래 SQL을 보면 LAG를 사용해 이전값, LEAD를 사용해 이후값을 가져오고 있으나, 가져온 값이 같은 것을 알 수 있다. LAG와 LEAD의 ORDER BY 기준을 역으로 적용했기 때문이다.
-- [SQL-11-5-2-a] LAG와 LEAD
SELECT T1.OrdNo ,T1.OrdDtm ,T1.MemberId
,LAG(T1.OrdDtm) OVER(ORDER BY T1.OrdDtm ASC) BeforeOrdDtm_LAG
,LEAD(T1.OrdDtm) OVER(ORDER BY T1.OrdDtm DESC) BeforeOrdDtm_LEAD
FROM startdb.Ord T1
WHERE T1.MemberId = 'M0201'
AND T1.OrdDtm >= STR_TO_DATE('20230101','%Y%m%d')
AND T1.OrdDtm < STR_TO_DATE('20230201','%Y%m%d')
ORDER BY T1.OrdDtm ASC;
OrdNo OrdDtm MemberId BeforeOrdDtm_LAG BeforeOrdDtm_LEAD
----- ------------------- -------- ------------------- -------------------
67634 2023-01-05 10:00:00 M0201 NULL NULL
68174 2023-01-11 10:00:00 M0201 2023-01-05 10:00:00 2023-01-05 10:00:00
68768 2023-01-23 10:00:00 M0201 2023-01-11 10:00:00 2023-01-11 10:00:00
69308 2023-01-29 10:00:00 M0201 2023-01-23 10:00:00 2023-01-23 10:00:00
SQL
복사
11-5-3. LAG, LEAD와 PARTITION BY
LAG와 LEAD 역시 PARTITION BY를 추가해 사용할 수 있다. 파티션별로 이전, 이후의 레코드를 가져오도록 처리할 수 있다.
-- [SQL-11-5-3-a] PARTITION BY 별 LAG 활용
SELECT T1.OrdNo ,T1.OrdDtm ,T1.MemberId
,LAG(T1.OrdDtm) OVER(PARTITION BY T1.MemberId ORDER BY T1.OrdDtm ASC) BeforeOrdDtm
FROM startdb.Ord T1
WHERE T1.MemberId IN ('M0200','M0201','M0202')
AND T1.OrdDtm >= STR_TO_DATE('20230101','%Y%m%d')
AND T1.OrdDtm < STR_TO_DATE('20230201','%Y%m%d')
ORDER BY T1.MemberId ASC,T1.OrdDtm ASC;
OrdNo OrdDtm MemberId BeforeOrdDtm
----- ------------------- -------- -------------------
67305 2023-01-02 10:00:00 M0200 NULL
69792 2023-01-30 10:00:00 M0200 2023-01-02 10:00:00
67634 2023-01-05 10:00:00 M0201 NULL
68174 2023-01-11 10:00:00 M0201 2023-01-05 10:00:00
68768 2023-01-23 10:00:00 M0201 2023-01-11 10:00:00
69308 2023-01-29 10:00:00 M0201 2023-01-23 10:00:00
67406 2023-01-02 11:00:00 M0202 NULL
69893 2023-01-30 11:00:00 M0202 2023-01-02 11:00:00
SQL
복사
11-5-4. N건 이전, 이후 가져오기
LAG와 LEAD를 사용할 때, 컬럼명과 함께 이전이나 이후의 레코드 수를 지정할 수 있다.
-- [SQL-11-5-4-a] 한달 전 수치와 두달 전 수치 가져오기
SELECT T2.OrdYm
,T2.SumOrdAmt
,LAG(T2.OrdYm) OVER(ORDER BY T2.OrdYm ASC) Bf1Month
,LAG(T2.SumOrdAmt) OVER(ORDER BY T2.OrdYm ASC) Bf1Amt
,LAG(T2.OrdYm,2) OVER(ORDER BY T2.OrdYm ASC) Bf2Month
,LAG(T2.SumOrdAmt,2) OVER(ORDER BY T2.OrdYm ASC) Bf2Amt
FROM (
SELECT DATE_FORMAT(T1.OrdDtm,'%Y%m') OrdYm
,SUM(T1.OrdAmt) SumOrdAmt
FROM startdb.Ord T1
WHERE T1.OrdDtm >= STR_TO_DATE('20220101','%Y%m%d')
AND T1.OrdDtm < STR_TO_DATE('20230101','%Y%m%d')
GROUP BY DATE_FORMAT(T1.OrdDtm,'%Y%m')
) T2
ORDER BY T2.OrdYm;
OrdYm SumOrdAmt Bf1Month Bf1Amt Bf2Month Bf2Amt
------ ------------ -------- ------------ -------- ------------
202201 4488500.000 NULL NULL NULL NULL
202202 4702500.000 202201 4488500.000 NULL NULL
202203 5995000.000 202202 4702500.000 202201 4488500.000
202204 476500.000 202203 5995000.000 202202 4702500.000
202205 9270000.000 202204 476500.000 202203 5995000.000
202206 4300500.000 202205 9270000.000 202204 476500.000
202207 19053500.000 202206 4300500.000 202205 9270000.000
202208 19568000.000 202207 19053500.000 202206 4300500.000
202209 19510500.000 202208 19568000.000 202207 19053500.000
202210 19400500.000 202209 19510500.000 202208 19568000.000
202211 19280500.000 202210 19400500.000 202209 19510500.000
202212 91350000.000 202211 19280500.000 202210 19400500.000
SQL
복사
11-5-5. LAG 활용하기
LAG를 활용해 이전일자 대비 주문금액 증감 분석을 비교적 쉽게 처리할 수 있다.
-- [SQL-11-5-5-a] LAG를 이용해 이전일자 주문금액과 비교하기
SELECT T2.OrdYmd
,T2.SumAmt
,LAG(T2.SumAmt) OVER(ORDER BY T2.OrdYmd ASC) BeforeSumAmt
,LAG(T2.SumAmt) OVER(ORDER BY T2.OrdYmd ASC) - T2.SumAmt DayOverDayDifference
FROM (
SELECT DATE_FORMAT(T1.OrdDtm,'%Y%m%d') OrdYmd
,SUM(T1.OrdAmt) SumAmt
FROM startdb.Ord T1
WHERE T1.OrdDtm >= STR_TO_DATE('20220101','%Y%m%d')
AND T1.OrdDtm < STR_TO_DATE('20220106','%Y%m%d')
GROUP BY DATE_FORMAT(T1.OrdDtm,'%Y%m%d')
) T2
ORDER BY T2.OrdYmd;
OrdYmd SumAmt BeforeSumAmt DayOverDayDifference
-------- ---------- ------------ --------------------
20220101 15000.000 NULL NULL
20220102 472500.000 15000.000 -457500.000
20220103 18500.000 472500.000 454000.000
20220104 12000.000 18500.000 6500.000
20220105 785500.000 12000.000 -773500.000
SQL
복사
이번에는 일부 매장을 선택해, 매장별로 일별 주문금액 증감을 분석해보자. LAG와 함께 PARTITION BY를 활용해 원하는 결과를 얻을 수 있다.
-- [SQL-11-5-5-b] LAG를 이용해 매장별 주문금액 증감 비교하기
-- 매장에 따라 특정 일자에는 주문이 없다.
SELECT T2.ShopId ,T2.OrdYmd ,T2.SumAmt
,LAG(T2.SumAmt) OVER(PARTITION BY T2.ShopId ORDER BY T2.OrdYmd ASC) BeforeSumAmt
,T2.SumAmt - (LAG(T2.SumAmt) OVER(PARTITION BY T2.ShopId ORDER BY T2.OrdYmd ASC)) DayOverDayDifference
FROM (
SELECT T1.ShopId ,DATE_FORMAT(T1.OrdDtm,'%Y%m%d') OrdYmd
,SUM(T1.OrdAmt) SumAmt
FROM startdb.Ord T1
WHERE T1.OrdDtm >= STR_TO_DATE('20220101','%Y%m%d')
AND T1.OrdDtm < STR_TO_DATE('20220106','%Y%m%d')
AND T1.ShopId IN ('S010','S011')
GROUP BY T1.ShopId ,DATE_FORMAT(T1.OrdDtm,'%Y%m%d')
) T2
ORDER BY T2.ShopId ,T2.OrdYmd;
ShopId OrdYmd SumAmt BeforeSumAmt DayOverDayDifference
------ -------- --------- ------------ --------------------
S010 20220102 14000.000 NULL NULL
S010 20220105 3000.000 14000.000 -11000.000
S011 20220101 7000.000 NULL NULL
S011 20220102 3500.000 7000.000 -3500.000
S011 20220103 11000.000 3500.000 7500.000
S011 20220104 4000.000 11000.000 -7000.000
S011 20220105 7500.000 4000.000 3500.000
SQL
복사
비즈니스 로직에 따라 위 결과는 문제가 있을 수 있다. 매장에 따라 특정 일자에는 주문이 전혀 없을 수도 있다. S010 매장을 보면 1월 2일과 1월 5일 주문만 있다. 이 경우 주문이 발생하지 않은 일자의 데이터 0으로 보여주는 것이 비즈니스적으로 맞다면 SQL을 변경해야 한다. 다음과 같이 CROSS JOIN과 날짜 테이블을 활용해 볼 수 있다.
-- [SQL-11-5-5-c] 모든 매장의 조회 구간에 대한 데이터가 나올 수 있도록,
-- 날짜 테이블과 CROSS JOIN 조인을 활용
WITH W1 AS(
SELECT A.ShopId ,B.BaseYmd
FROM startdb.Shop A
CROSS JOIN startdb.BaseDt B
WHERE A.ShopId IN ('S010','S011')
AND B.BaseYmd BETWEEN '20220101' AND '20220105'
)
SELECT T2.ShopId ,T2.OrdYmd ,T2.SumAmt
,LAG(T2.SumAmt) OVER(PARTITION BY T2.ShopId ORDER BY T2.OrdYmd ASC) BeforeSumAmt
,T2.SumAmt - (LAG(T2.SumAmt) OVER(PARTITION BY T2.ShopId ORDER BY T2.OrdYmd ASC)) DayOverDayDifference
FROM (
SELECT TT1.ShopID
,TT1.BaseYmd OrdYmd
,IFNULL(TT2.SumAmt,0) SumAmt
FROM W1 TT1
LEFT OUTER JOIN (
SELECT T1.ShopId ,DATE_FORMAT(T1.OrdDtm,'%Y%m%d') OrdYmd ,SUM(T1.OrdAmt) SumAmt
FROM startdb.Ord T1
WHERE T1.OrdDtm >= STR_TO_DATE('20220101','%Y%m%d')
AND T1.OrdDtm < STR_TO_DATE('20220105','%Y%m%d')
AND T1.ShopId IN ('S010','S011')
GROUP BY T1.ShopId ,DATE_FORMAT(T1.OrdDtm,'%Y%m%d')
) TT2 ON (TT2.ShopId = TT1.ShopId AND TT2.OrdYmd = TT1.BaseYmd)
) T2
ORDER BY T2.ShopId ,T2.OrdYmd;
ShopId OrdYmd SumAmt BeforeSumAmt DayOverDayDifference
------ -------- --------- ------------ --------------------
S010 20220101 0.000 NULL NULL
S010 20220102 14000.000 0.000 14000.000
S010 20220103 0.000 14000.000 -14000.000
S010 20220104 0.000 0.000 0.000
S010 20220105 0.000 0.000 0.000
S011 20220101 7000.000 NULL NULL
S011 20220102 3500.000 7000.000 -3500.000
S011 20220103 11000.000 3500.000 7500.000
S011 20220104 4000.000 11000.000 -7000.000
S011 20220105 0.000 4000.000 -4000.000
SQL
복사
BOOSTER QUIZ
BOOSTER QUIZ ERD
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
복사
