[SQL] 오라클 LAG, LEAD 함수 사용법(이전 행값, 다음 행값 가져오기),전일대비 구하기

대시보드 개발 중인데 전일대비 값을 한 쿼리로 가져오는 방법에 대해 엄청 고민이 됐다,,,

맨처음에는 아래처럼 DECODE 처리한 쿼리를 또 한번 감싸서 SUM하는 식으로 했는데 뭔가 결과값이 틀린것같다,,

다른 방법이 있을 것 같아서 찾아봤더니 LAG와 LEAD라는 함수가 있었다.

~~

DECODE(ORD_DTM, 기준날짜, WHOUT_QTY, 0) AS TOTAL  --기준일 합계에 사용할 수량

DECODE(ORD_DTM, 전일자, WHOUT_QTY, 0) AS BEF_TOTAL  --전일 합계에 사용할 수량

 

SELECT 	NVL(SUM(TOTAL), 0) AS TOTAL
              	,NVL(SUM(BTOTAL), 0) AS BTOTAL
              	, CUST_CD
              	, CUST_NM
        FROM (         
       			SELECT  DECODE(A.ORD_DT,#{TOT_DT},A.WHOUT_QTY, 0) AS TOTAL
		              , DECODE(A.ORD_DT,#{BTOT_DT},A.WHOUT_QTY, 0) AS BTOTAL
		              , A.ORD_DT
		              , A.CUST_CD
		              , A.CUST_NM
       			FROM (
	               SELECT A.CUST_CD    AS CUST_CD
	                    , D.CUST_NM        AS CUST_NM
	                    , B.WHOUT_QTY
	                    , TO_CHAR(A.ORD_DTM, 'YYYYMMDD') AS ORD_DT
		            FROM     VW_DS_ORDERS A
		                    , VW_DS_ORDERDETAIL B
		                    , DASADM.TB_DS_ORG_CUST D
		            WHERE 1=1
		            AND     A.ORD_NO = B.ORD_NO
		            AND     D.BRCH_CD = #{BRCH_CD}
		            AND     D.CUST_CD = A.CUST_CD(+)
		            AND     A.CENT_CD IN (SELECT  C.CENT_CD FROM DASADM.TB_DS_ORG_CENT C WHERE C.BRCH_CD = #{BRCH_CD})
	           ) A
        )
       GROUP BY CUST_CD, CUST_NM

 

1. 함수

LAG : 이전 행의 값 리턴

LEAD : 다음 행의 값 리턴

 

2. 문법

1) LAG

LAG(대상컬럼, [행의 위치(기본값 1은 생략가능)], [값이 없을경우 기본값설정]) OVER ([PARTITION BY] ORDER BY )

아래 테이블로 예시를 들어보면

SELECT 	CUST_CD		--고객코드
	, CUST_NM	--고객명
        , QYT		--수량
        , ORD_DTM   	--날짜
FROM 	ORDERS		--주문테이블


CUST_CD   |  CUST_NM  | QTY  | ORD_DTM
PSJ       |  테스트1   | 1    | 2023-01-05
SJT	  |  테스트2   | 3    | 2023-01-05

 

SELECT CUST_CD
	, CUST_NM
        , QTY
        , LAB(QTY) OVER(ORDER BY CUST_CD) AS PREV_QTY
        , ORD_DTM
FROM 	ORDERS

CUST_CD   |  CUST_NM  | QTY  | PREV_QTY   |  ORD_DTM
PSJ       |  테스트1   | 1    | 0         | 2023-01-05
SJT	  |  테스트2   | 3    |1          | 2023-01-05

결과는 위 처럼 

PSJ 한테는 이전행이 없으니 PREV_QTY가 0

SJT 에게는 3을 조회할 수 있다.

 

다른 옵션을 써보면

SELECT 	CUST_CD
	, CUST_NM
        , QTY
        , LAG(QTY, 2, 999) OVER(ORDER BY CUST_CD) AS PREV_QTY
        , ORD_DTM
FROM ORDERS

이 쿼리는 현재행 기준으로 두번째 이전행의 값을 표시하고 없다면, 999 값으로 표시한다는 의미이다.

 

 

2) LEAD 함수

LEAD(대상컬럼, [행의 위치(기본값 1은 생략가능)], [값이 없을경우 기본값설정]) OVER ([PARTITION BY] ORDER BY )

LAG와 반대로 다음행의 값을 가져오겠다는 의미~~

 

 

 

3. 내가 개발에서 사용한 케이스

상황 : 대시보드에서 보고싶은 고객사의 금일과 전일자 물동량 차이를 계산하려고 할 때

오늘자 주문데이터가 없어도 합계와, 전일대비 수량이 0으로 나와야한다.

 

 

주문(VW_DS_ORDERS), 주문상세(VW_DS_ORDERDETAIL)

대시보드_고객(TB_DS_ORG_CUST)

SELECT CUST_CD
		, CUST_NM
		, SUM(TOTAL) AS TOTAL
		, SUM(BTOTAL) AS BTOTAL
		, ORD_DT
		FROM (
			SELECT * 
			FROM (
					SELECT CUST_CD 	
							, CUST_NM
							, TOTAL
							, NVL(LAG(TOTAL) OVER (PARTITION BY CUST_CD, CUST_NM ORDER BY ORD_DT), 0) AS BTOTAL
							, ORD_DT
							, ORD_SEQ
					FROM (
							SELECT A.CUST_CD 
									, D.CUST_NM
									, NVL(SUM(B.WHOUT_QTY), 0) AS TOTAL
									, TO_CHAR(A.ORD_DTM, 'YYYYMMDD') AS ORD_DT
									, ORD_SEQ
							FROM     VW_DS_ORDERS A
				        			, VW_DS_ORDERDETAIL B
				        			, DASADM.TB_DS_ORG_CUST D
							WHERE 	A.ORD_NO = B.ORD_NO
							AND     D.BRCH_CD = #{BRCH_CD}
							AND     D.CUST_CD = A.CUST_CD(+)
							AND     A.CENT_CD IN (SELECT  C.CENT_CD FROM DASADM.TB_DS_ORG_CENT C WHERE C.BRCH_CD = #{BRCH_CD})
							GROUP BY 	A.CUST_CD, D.CUST_NM, ORD_DTM, ORD_SEQ
							HAVING		TO_CHAR(A.ORD_DTM, 'YYYYMMDD') IN (TO_CHAR(SYSDATE-1, 'YYYYMMDD'), TO_CHAR(SYSDATE, 'YYYYMMDD'))
						) 
					)
		UNION 
			SELECT 	CUST_CD
					, CUST_NM
					, 0 AS TOTAL
					, 0 AS BTOTAL
					, TO_CHAR(SYSDATE, 'YYYYMMDD') AS ORD_DT 
					, ORD_SEQ
			FROM DASADM.TB_DS_ORG_CUST 
			WHERE BRCH_CD = #{BRCH_CD}
		)
		GROUP BY CUST_CD, CUST_NM, ORD_DT, ORD_SEQ
		HAVING ORD_DT = TO_CHAR(SYSDATE, 'YYYYMMDD')
		ORDER BY ORD_SEQ

ORDERS 테이블에 오늘날짜 데이터가 없으면 아예 행이 없는데,,

대시보드에서는 0건인 것도 봐야하므로 

UNION으로 DUMMY 데이터를 UNION 해주고 어차피 값이 0 이므로 고객 코드와 날짜 기준으로 SUM 해주면 0건도 조회할 수 있다.