메타코드의 SQL 부트캠프 강의 정리. 강의는 메타코드에서 구매 가능! https://mcode.co.kr/kor/
오늘은 window 함수 중에 어려운 편에 속하는 LEAD, LAG 함수에 대해 정리하려고 한다.
🔥 LEAD, LAG 함수
지난 글에서 살펴봤던 WINDOW 함수의 기본 형태를 적어보면, 아래와 같다. WINDOW_FUNCTION 부분에 LEAD, LAG 함수가 들어간다.
WINDOW_FUNCTION(컬럼1) OVER
(PARTITION BY ~
ORDER BY~)
AS 별칭;🔥 LEAD, LAG 함수
LEAD 함수는 다음 행 데이터를 가져오고, LAG 함수는 반대로 이전 행 데이터를 가져온다. LEAD 함수는 LEAD(컬럼, N) 과 같이 쓰면 N행 뒤의 데이터를 가지고 온다.
예시와 함께 살펴보는 것이 가장 이해하기 쉬울 것 같아서 강의 자료를 가지고 왔다.
위 예에서 LAG 함수 부분을 보면, customerNumber 기준으로 PARTITION을 나누었다. 103이라는 고객끼리, 112라는 고객끼리 묶인 걸 볼 수 있다. 고객번호 103 파티션 내에서 정렬 기준은 주문일(orderDate)이니까 2003-05-20, 2004-09-27, 2004-11-25 순으로 정렬이 되었다. LAG(orderDate) 함수로 만든 새로운 컬럼(prev_order_date)은 주문일 기준으로 이전 행을 불러온다. 즉, prev_order_date의 첫 번째 행은 orderDate의 -1행을 불러와야 한다. 파티션 내에 -1행은 존재하지 않으니까 NULL로 채워짐. prev_order_date의 2번째 행은 orderDate 컬럼의 1번째 행을 불러오므로 '2003-05-20'을 불러온다. 이런 식으로 하나씩 앞의 값을 불러오는 게 LAG 함수이다.
반대로 LEAD 함수는 동일한 파티션과 정렬 기준으로, 다음 행을 불러온다. 즉, LEAD(orderDate)로 만든 새로운 컬럼 'next_order_date'은 첫번째 행에 orderDate의 두번째 행인 '2004-09-27'을 불러오고, 2번째 행에 orderDate의 3번째 행인 '2004-11-25'를 불러온다. 그리고 마지막으로 3번째 행에서는 orderDate의 4번째 행을 불러와야 하는데, 파티션 내 행이 3개 뿐이니까 orderDate의 4행이 존재하지 않아 NULL값을 불러온다.
또한, 지난 글에서 본 누적합과 같이, LAED / LAG 함수도 뒤에 오는 PARTITION BY와 ORDER BY 구성에 따라 결과가 다르게 조회된다.
ORDER BY만 사용하면 정렬만 적용이 된다. PARTITION BY가 없으니까 데이터 전체를 하나의 파티션으로 인식하여 이전 행(LAG) 혹은 다음 행(LEAD)을 불러온다.
PARTITION만 나눈 경우에는 정렬없이, 원래 데이터가 쌓인 형태대로 LEAD와 LAG 함수가 적용된다. 위 예의 경우 orderDate(주문일)라는 날짜 데이터를 사용했기 때문에 기본적으로 오름차순으로 쌓여 있지만, 그렇지 않은 데이터의 경우 정렬되지 않은 상태 그대로 출력된다.
PARTITION BY와 ORDER BY 모두 사용하지 않으면 전체를 하나의 파티션으로 보고, 정렬을 따로 하지 않고 데이터가 쌓인 그대로 LEAD와 LAG함수가 적용된다.
문제 1. orderdetails 테이블에서 각 제품 코드별로 주문된 수량(orderNumber) 기준으로 정렬했을 때, 주문 수량의 증분을 계산하시오.
모든 어려운 문제는 원본 데이터 표가 어떻게 생겼는지 봐야 한다. 아래와 같이 필요한 컬럼만 가져와도 좋고, SELECT *을 써서 표의 모든 값을 조회해도 된다.
SELECT orderNumber, productCode, quantityOrdered
FROM orderdetails;
제품코드별로 주문수량 기준으로 정렬했을 때의 증분이니까, 먼저 orderdetails 테이블을 productCode와 orderNumber 순으로 정렬한 뒤 값을 살펴본다.
SELECT productCode, orderNumber, quantityOrdered
FROM orderdetails
ORDER BY productCode, orderNumber;
증분이란 뭘까? 주문수량이 30 -> 34 -> 41 -> 45 이런 식으로 있을 때 증분은, (34-30), (41-34), (45-41) 이런 식으로 계산하면 될 것 같다. 그러면 2번째 행(34)에서 1번째 행(30) 데이터를 가져와서 둘을 서로 빼면 되지 않을까? '이전 행 데이터를 현재 행 옆에 하나의 컬럼을 추가하여 표시'하는 WINDOW_FUNCTITION은? 'LAG'다.
SELECT orderNumber, productCode, quantityOrdered,
LAG(quantityOrdered) OVER (PARTITION BY productCode ORDER BY ORDERNUMBER) AS pre_quantitiy
FROM orderdetails;
LAG로 이전행 데이터를 가져왔으니, 이제 현재행 컬럼 - 이전 행 컬럼을 빼는 코드만 적어주면 완성!
SELECT orderNumber, productCode, quantityOrdered,
quantityOrdered - LAG(quantityOrdered) OVER (PARTITION BY productCode ORDER BY ORDERNUMBER) AS quantity_difference
FROM orderdetails;
어렵게 생각할 것 없이, 이렇게 바로 빼기 기호를 써주면 된다.
그러면 문제에서 요구하는 제품코드(productCode)별, 주문번호(orderNumber) 순 증분이 짜잔 나온다.
더 어려운 문제여도 원본 데이터를 살펴 보고, 수학 문제처럼 생각하고, 표가 어떻게 나와야 할지 고민하면 답이 풀릴... 것이다. (라고 말하는 나도 어려운 문제는 못 푸는 부트캠프 3일 배운 사람..ㅎ.....파이팅...🔥)
