[SQL 문법] LAG()과 LEAD()의 차이 및 활용 방법(예제 코드)

목차
1. SUMMARY
2. LAG
     2-1. LAG 의미
     2-2. offset
     2-3. 예제
3. LEAD
     3-1. LEAD() 의미
     3-2. LEAD() 함수 구문
     3-3. offset
     3-4. 예제

1. summary

현재 행과 이전 행 간의 차이를 계산하는데 유용한 함수

LAG() : 현재 행에서 이전 행을 볼 수 있도록 함

LEAD() : 현재 행에서 다음 행을 볼 수 있도록 함

 

2. LAG

2-1. LAG 의미

- 해당 파티션 또는 결과 집합 내의 행 수 만큼 현재 행 앞에 있는 생에서 값을 반환한다
- [SQL 문법] PARTITION BY(파티션 분할)과 ROW_NUMBER() OVER() 정리

- 여러 행을 되돌아보고 연재 행에서 해당 행의 데이터에 액세스 할 수 있는 윈도우 함수

 

2-2. offset

- offset 값을 가져올 연재 행의 이전 행 수

- 0 혹은 literal 양의 정수

- default = 1

 

- 오프셋 정수를 2로 지정할 경우, 1번째 행과 2번째 행은 해당 값이 없기에 NULL로 반환됨

lag(<expression>[, offset[, default_value]]) over (
	partition by expr, ...
    order by expr [asc|desc], ...
)

 

 

 

 

2-3. 예제

특정 연도 및 전년도의 모든 제품 라인의 주문값

- CTE로 매년 모든 제품의 주문 금액을 가져옴 (common table expression : with [new_table_name] as (select ~~) 

- 제품을 제품군(productline)으로 파티셔닝함(나눔, 그룹화)

- 파티션을 주문 연도별로 정렬(order by)

- '정렬된 파티션'에 LAG(column name,1)함수를 적용해 >> 각 제품의 전년도 총 주문 금액을 가져옴

WITH productline_sales AS (
	SELECT productline,
    		YEAR(orderDate) order_year,
                ROUND(SUM(quantityOrdered * priceEach), 0) order_value
	FROM orders
    INNER JOIN orderdetails USING (orderNumber)
    INNER JOIN products USING (productCode)
    GROUP BY productline, order_year
)

SELECT
    productline,
    order_year,
    order_value,
    LAG(order_value, 1) OVER (
    	PARTITION BY productLine
        ORDER BY order_year
    ) prev_year_order_value
FROM 
	productline_sales

- year() 

- round( , ): 소수점 이하 자릿수를 0으로 반올림/ (숫자 혹은 열 이름, 반올림할 자리값) 0은 소수점 첫째 자리 / defaut == 0

- inner join

- using

 

 

 

 

 

3. LEAD()

3-1. LEAD() 의미

현재 행에서 N번째 이후/아래의 행의 데이터에 액세스 할 수 있는(보여줄 수 있는/가져오로 수 있는) WINDOW FUNCTION(윈도우함수)

LAG() 함수와 유사하지만,접근하는 데이터의 위치/방향이 반대다

 

 

3-2. LEAD() 함수 구문

LEAD(<expression>[,offset[, default_value]]) over (
	PARTITION BY (expr)
    ORDER BY (expr)
)

 

 

3-3. offset

- offset의 값은 == 현재 행에서 정보를 참조할 행의수까지의 간격? 이동거리 혹은 정보를 당겨오는 구간의 길이...

- 양의 정수

- 0이면 현재 행에 대해 평가?

- default == 1

 

- offset == 1, 마지막 행의 반환 값은 == default_value

- default_value를 지정하지 않으면, NULL 반환

 

 

3-4. 예제

각 고객의 다음 주문 날짜 표기

- 고객 번호(customerNumber)로 결과 집합을 파티션으로 나눔(partition by)

- 파티션을 주문 날짜별로 정렬(order by)

- 각 파티션에 lead()함수 적용해, 다음 주문 날짜를 가져옴

 

- 후속 행(다음으로 실행되는 행이) 파티션 경계를 넘음 == 한 파티션의 마지막 행 == 그 행의 nextOrderDate는 NULL

SELECT customerName
	, orderDate
    	, LEAD(orderDate, 1) OVER (
    		PARTITION BY customerNumber 
        	ORDER BY orderDate) nextOrderDate
FROM orders
INNER JOIN customers USING (customerNumber)

 

 

 

 

 

---

자료 출처

https://www.mysqltutorial.org/mysql-window-functions/mysql-lag-function/

MySQL LAG() Function Explained By Practical Examples

 

https://le-meiren.tistory.com/20

[SQL 문법] PARTITION BY(파티션 분할)과 ROW_NUMBER() OVER() 정리

https://www.mysqltutorial.org/mysql-window-functions/mysql-lead-function/

MySQL LEAD Function By Practical Examples