LAG, LEAD는 앞뒤 행의 값을 읽는 함수이다. SELECT문은 한행씩 순서대로 읽는 것이 보편적인데 이 함수를 사용하면 결과셋의 이전, 이후행을 읽을 수 있다. 오라클도 지원하는 함수이며 MSSQL은 2012부터 지원하기 시작했다. 형식은 다음과 같다.
LAG(필드, offset, default) OVER (PARTITION BY ORDER BY)
LAG 함수는 이전 행을 읽고 LEAD 함수는 다음행을 읽는다. 괄호안에 읽을 필드를 지정하되 단일값을 리턴하는 수식도 가능하다. offset은 앞뒤로 몇 행 거리를 읽을 것인지를 지정하며 생략시 1이 적용되어 바로 앞 뒤의 행을 읽는다. offset에 음수를 지정할 수는 없어 앞뒤를 읽는 함수가 나누어져 있다. default는 행이 없을 시 대신 읽을 값이며 디폴트는 NULL이다.
앞 뒤의 행을 규정하려면 정렬은 필수적이어서 OVER 절에 ORDER BY가 반드시 있어야 한다. PARTITION BY는 그룹을 나눌 파티션을 지정하며 생략 가능하다. 테스트를 위해 다음 샘플 테이블을 생성하자.
CREATE TABLE tMonthSale
(
year INT,
month INT,
sales INT
)
INSERT INTO tMonthSale VALUES(2021, 1, 3800);
INSERT INTO tMonthSale VALUES(2021, 2, 4200);
INSERT INTO tMonthSale VALUES(2021, 3, 4150);
INSERT INTO tMonthSale VALUES(2021, 4, 4380);
INSERT INTO tMonthSale VALUES(2021, 5, 4600);
INSERT INTO tMonthSale VALUES(2021, 6, 3700);
INSERT INTO tMonthSale VALUES(2021, 7, 3330);
INSERT INTO tMonthSale VALUES(2021, 8, 2900);
INSERT INTO tMonthSale VALUES(2021, 9, 3650);
INSERT INTO tMonthSale VALUES(2021, 10, 4120);
INSERT INTO tMonthSale VALUES(2021, 11, 5000);
INSERT INTO tMonthSale VALUES(2021, 12, 4420);
월별 매출을 통계를 저장하는 테이블이다. SELECT문으로 평이하게 읽으면 월별 매출액이 출력된다. 여기에 이전월과 이후월의 매출까지 한행에 출력하려면 LAG, LEAD 함수를 사용한다.
SELECT year, month, sales,
LAG(sales, 1, NULL) OVER (ORDER by month) AS prior,
LEAD(sales, 1, NULL) OVER (ORDER by month) AS next
FROM tMonthSale;
LAG 함수는 1개월 전의 매출을 보여 주고 LEAD 함수는 1개월 후의 매출을 보여준다. offset을 2로 지정하면 2개월 전후의 매출을 볼 수도 있지만 보통은 인접행의 값을 보는 경우가 많다. 첫달은 이전이 없고 마지막달은 이후가 없는데 이때는 디폴트로 지정한 NULL이 출력된다. 1, NULL은 둘 다 디폴트여서 생략해도 상관 없다. 두 경우 모두 정렬은 반드시 필요해 월별로 정렬한 후 앞뒤행을 찾는다.
3월의 prior 열에는 2월의 매출이 표시되고 next열에는 4월의 매출이 표시된다. 바로 아래, 위에도 있지만 옆으로 나란히 보여주니 읽기 편하다.
1월의 이전달이나 12월의 이후달은 결과셋에 포함되지 않아 NULL로 표시된다. 이 함수를 사용하면 이전달과 비교하여 이번달 매출이 얼마나 늘어났는지 금방 조사할 수 있다.
SELECT year, month, sales,
sales - LAG(sales) OVER (ORDER by month) AS incsales
FROM tMonthSale;
LAG(sales) 구문으로 이전달 매출을 구하고 이번달 매출에서 이 값을 빼면 매출 증가액이다. 현재값뿐만 아니라 값을 증감분을 뽑아볼 때 유용하다.
이 기능이 도입되기 전에는 자체 조인이나 CTE 같은 고급 기법을 동원해야 했지만 지금은 간단한 수식으로 이전, 이후행을 조사할 수 있어 편리하다. 내부적인 검색 및 연산을 DBMS가 알아서 처리해 주니 속도도 훨씬 더 빠르다.
개발자의 천국 SoEn
|
|
