sql lag, lead 함수로 이전행과 다음행 조회하기

lag, lead라는 영어 단어의 뜻 그대로 이전 또는 이후 행의 데이터를 현재의 행과 함께 조회할 수 있는 기능을 제공하는 함수이다. 대부분 sql db에서 지원하는 기능이면 본문에서는 Postgresql을 기준으로 문법을 쓰고 설명하도록 하겠다.

함수로 해결할 수 있는 문제 (order by)

아주 자주 쓰이는 함수는 아니긴 하지만 그래도 lag, lead 함수를 쓰면 복잡하게 만들어야 하는 쿼리를 한줄로 간단하게 끝낼수 있다. 특히 이전 행의 값과 현재 값을 비교하거나 계산해야할 때 쓰면 유용하다. 예를들어 아래와 같은 테이블이 존재한다고 가정해보자.

 

lag 함수 예시

김정호라는 사람의 5일 전 소비 값과 현재 소비값을 비교해서 값이 오른 행만 추출하고 싶다면 어떻게 해야할까? 머리속이 복잡해지지만, lag함수를 사용하면 5일전 소비값을 불러오는 컬럼 하나를 만들어낼 수 있다.

select *, lag(spending,5) over(order by date) as lag_spending from table

lag 함수 예시

만약 5일 후의 값을 추출해서 행에 추가하고 싶다면 lag함수 대신 lead를 쓰면 된다, 형식은 같다.

select *, lead(spending,5) over(order by date) as lag_spending from table

 

반응형

그룹마다 다르게 함수를 적용하기 (partition by)

위 예시에서는 사람이 한명이었지만, 만약 테이블에 여러사람이 있다면 문제가 어떻게 될까? 단순히 5일 전 값을 불러오려고 한다면 아마 내가 원하는 결과를 조회할 수 없을 것이다. 아마도 우리가 원래 의도하려던 것은 사람마다 5일 전 값을 조회해서 보고싶었던 것일 테다. 이럴때는 간단하게 partition by를 적용해주면 된다. lead도 마찬가지이다.

lag함수 partition by

select *, lag(spending,5) over(partition by name order by date) as lag_spending from table

lag 함수 partition by

여기서 만약에 5일 전 값이 없다면 자동으로 Null으로 불러와지게 된다. 5월 6일 ~ 5월 9일에는 lag_spending 컬럼에 레코드가 없는 이유이다. 자 그럼 마지막으로 사람별로 5일전 spending이 있는 경우에만 현재의 spending과 함께 select하는 sql문으로 마무리 해보겠다.

 

select *, lag(spending,5) over(partition by name order by date) as lag_spending from table where lag_spending is not null;