[DB/SQL] 윈도우 함수 LEAD/LAG (Feat. Partition by )

SQL에서 윈도우 함수 중에서 LEAD() 와 LAG() 에 대해서 알아봅시다. 내용 중 틀린 부분이 있거나 추가 하면 좋은 정보가 있을 경우 댓글로 남겨주시면 감사하겠습니다.

 

 

목차

 

---

LAG와 LEAD 함수

LAG와 LEAD 함수는 하나의 쿼리에서 동시에 다른 로우의 값에 접근할 수 있는 함수입니다. 

LAG 함수 문법
LAG ( expr, offset, default ) OVER ( PARTITION BY 절 ) 
LEAD 함수 문법
LEAD ( expr, offset, default ) OVER ( PARTITION BY 절 )

LAG 함수는 계산 대상 데이터들을 PARTITION BY 절로 구분하여, expr에 명시된 값을 기준으로 이전 로우의 값을 반환합니다. offset은 생략이 가능한데, 생략할 경우 디폴트 값인 1이 적용됩니다. 즉 생략하게 되면 바로 이전 로우의 값을 반환하게 됩니다. 세번쨰 파라미터인 default 또한 생략이 가능하며 이 값은 offset에 지정된 로우가 존재하지 않을 경우 LAG 함수가 리턴하는 값을 말합니다.

 

LEAD 함수는 LAG 함수와 파라미터 및 용법이 같습니다. 단지 이전 로우가 아닌 이후의 로우 값을 리턴하는 함수입니다.

 

---

사용법 및 예제

예제를 위한 성적 테이블을 생성했습니다. 학생ID, 점수 그리고 시험을 치른 날짜가 있습니다. 현재 테이블을 살펴보면 학생이 2명이 있으며 날짜별로 다른 점수를 받았습니다. 

성적
학생ID	점수	날짜
1	70	2023-01-01
2	50	2023-01-02
1	80	2023-01-03
2	60	2023-01-03
2	90	2023-01-04

다음은 학생별로 다음 시험을 치른 날짜에 대해서 확인해보도록 하겠습니다.

SELECT *, LEAD(날짜) OVER (PARTITION BY 학생ID ORDER BY 날짜 )AS "다음 날짜"
FROM 성적;

결과
학생ID	점수	날짜	다음날짜
1	70	2023-01-01	2023-01-03
2	50	2023-01-02	2023-01-03
1	80	2023-01-03
2	60	2023-01-03	2023-01-04
2	90	2023-01-04

위의 결과를 보면 1번 학생이 시험을 치른날짜 옆에 새로운 칼럼인 다음날짜가 출력되는 것을 확인할 수 있습니다. 마지막 시험은 2023-01-03 이므로 다음 시험이 없기 때문에 다음 날짜에 대한 값이 없습니다. 2번 학생은 2023-01-04일까지 총 3번의 시험을 치뤘기 때문에 다음날짜에는 2023-01-04 까지가 출력되었습니다. 

 

다음으로 LAG()함수를 사용하여 학생별로 이전에 치른 시험 성적을 출력하는 SQL문장을 만들어보겠습니다.

SELECT *, LAG(성적) OVER ( PARTITION BY 학생ID ORDER BY 날짜 ) AS "이전 성적"
FROM 성적;

결과
학생ID	점수	날짜	이전 성적
1	70	2023-01-01
2	50	2023-01-02
1	80	2023-01-03	70
2	60	2023-01-03	50
2	90	2023-01-04	60

 

위 결과에서 학생 1을 보면 23년 1월 1일에 치른 시험이 첫번째 시험이므로 이전 성적에 대한 값이 없습니다. 23년 1월 3일에 치른 시험이 두번쨰 시험이므로 이전 성적인 70이 함께 출력되었습니다. 

 

partition by 절은 group by처럼 의미는 비슷합니다. partition by 절에 오는 칼럼을 기준으로 범위를 정하여 결과를 출력해냅니다. order by 절을 사용하여 정렬기준을 정해 앞 또는 뒤에 오는 값이 정해집니다. partition by절을 활용하는 방법을 추가적으로 알아보겠습니다.

 

일자별요금내역
순번	요금	일자
1	10,000	2023-01-01
2	20,000	2023-01-02
3	10,000	2023-01-03
4	15,000	2023-01-04
5	25,000	2023-01-05

일자별요금내역 테이블을 이용하여 다음 결과를 나타낼 수 있는 일자별 누적요금을 구하는 SQL문을 작성해보겠습니다.

 

일짜별요금내역
일자	누적요금
2023-01-01	10,000
2023-01-02	30,000
2023-01-03	40,000
2023-01-04	55,000
2023-01-05	80,000

SELECT A.일자,SUM(B.요금) AS 누적요금
FROM 일자별요금내역 A JOIN 일자별요금내역 B
ON A.일자 >= B.일자
GROUP BY A. 일자
ORDER BY A. 일자;

위 결과를 나타내기 위해  SELF 조인을 사용하였씁니다 PARTITION 절 이용하면 JOIN을 이용하지 않고 같은 결과를 도출 해낼 수 있습니다.

SELECT 일자,SUM(요금) OVER (PARTITION BY 일자 ORDER BY 일자) AS 누적요금
FROM 일자별요금내역
ORDER BY A. 일자;

위와 같이 JOIN을 이용하여 결과를 도출 한 것과 결과 테이블은 같지만 PARTITION 절을 이용하여 더 간단하게 SQL 문을 작성할 수도 있습니다.