[ 쿼리 ] Advanced SQL for Data Professionals (6) - LAG/LEAD

이 포스트는 Medium에 올라온 다음 아티클을 참고하여 정리한 글입니다.

Advanced SQL for Data Professionals

---

이번 포스트에서 정리할 Advanced SQL 케이스는 Compare Row-to-Row 이다.

 

LEAD와 LAG 함수를 사용하며 정렬된 상태에 특정 데이터를 이전 몇번째 또는 이후 몇번째 데이터와 비교하고자 할 때 많이 쓰이는 방법이다.

 

이미지로 보면 다음과 같다.

왼쪽이 LAG, 오른쪽이 LEAD를 활용한 경우

---

[ Compare Row-to-Row: LAG Function ]

WITH currency (date, price, currency) AS (
SELECT cast('2006-01-02' AS DATE) ,3.2582, 'USD'
UNION SELECT cast('2006-01-03' AS DATE) ,3.2488  , 'USD'
UNION SELECT cast('2006-01-04' AS DATE) ,3.1858  , 'USD'
UNION SELECT cast('2006-01-05' AS DATE) ,3.1416  , 'USD'
UNION SELECT cast('2006-01-06' AS DATE) ,3.1507  , 'USD'
UNION SELECT cast('2006-01-09' AS DATE) ,3.1228  , 'USD'
UNION SELECT cast('2006-01-10' AS DATE) ,3.128   , 'USD'
UNION SELECT cast('2006-01-11' AS DATE) ,3.1353  , 'USD'
UNION SELECT cast('2006-01-12' AS DATE) ,3.1229  , 'USD'
UNION SELECT cast('2006-01-13' AS DATE) ,3.1542  , 'USD'
)

SELECT date, currency, price
  , LAG(price) OVER (ORDER BY date) previose_day_price
  , (price - LAG(price) OVER (ORDER BY date)) / LAG(price) OVER (ORDER BY date) diff
FROM currency;

/*
SELECT date, currency, price
  , LAG(price,1,NULL) OVER (ORDER BY date) previose_day_price
  , (price - LAG(price,1,NULL) OVER (ORDER BY date)) / LAG(price,1,NULL) OVER (ORDER BY date) diff
FROM currency;
*/

 

쿼리의 결과는 오른쪽과 같다.

price를 날짜 기준으로 한 행씩 아래로 옮긴 값을 활용해 전일 대비 변화량을 diff에 계산했다.

 

첫번째 행에 대응되는 이전 행 값이 없기 때문에 NULL로 조회된다.

위에서 실행된 쿼리는 주석처리 해둔 영역 안의 쿼리와 동일한 결과를 나타낸다.

 

만약 전일 대비 비교를 하고싶은 것이 아닌 이틀 전의 데이터와 비교하고 싶다면, 그리고 대응되는 값이 없을 때 NULL이 아닌 0의 값으로 조회되도록 하려면 다음과 같이 쿼리하면 된다.

WITH currency (date, price, currency) AS (
SELECT cast('2006-01-02' AS DATE) ,3.2582, 'USD'
UNION SELECT cast('2006-01-03' AS DATE) ,3.2488  , 'USD'
UNION SELECT cast('2006-01-04' AS DATE) ,3.1858  , 'USD'
UNION SELECT cast('2006-01-05' AS DATE) ,3.1416  , 'USD'
UNION SELECT cast('2006-01-06' AS DATE) ,3.1507  , 'USD'
UNION SELECT cast('2006-01-09' AS DATE) ,3.1228  , 'USD'
UNION SELECT cast('2006-01-10' AS DATE) ,3.128   , 'USD'
UNION SELECT cast('2006-01-11' AS DATE) ,3.1353  , 'USD'
UNION SELECT cast('2006-01-12' AS DATE) ,3.1229  , 'USD'
UNION SELECT cast('2006-01-13' AS DATE) ,3.1542  , 'USD'
)

SELECT date, currency, price
  , LAG(price,2,0) OVER (ORDER BY date) previose_day_price
  , (price - LAG(price,2,0) OVER (ORDER BY date)) / LAG(price,2,0) OVER (ORDER BY date) diff
FROM currency;

쿼리 결과

---

[ Compare Row-to-Row: LEAD Function ]

WITH currency (date, price, currency) AS (
SELECT cast('2006-01-02' AS DATE) ,3.2582, 'USD'
UNION SELECT cast('2006-01-03' AS DATE) ,3.2488  , 'USD'
UNION SELECT cast('2006-01-04' AS DATE) ,3.1858  , 'USD'
UNION SELECT cast('2006-01-05' AS DATE) ,3.1416  , 'USD'
UNION SELECT cast('2006-01-06' AS DATE) ,3.1507  , 'USD'
UNION SELECT cast('2006-01-09' AS DATE) ,3.1228  , 'USD'
UNION SELECT cast('2006-01-10' AS DATE) ,3.128   , 'USD'
UNION SELECT cast('2006-01-11' AS DATE) ,3.1353  , 'USD'
UNION SELECT cast('2006-01-12' AS DATE) ,3.1229  , 'USD'
UNION SELECT cast('2006-01-13' AS DATE) ,3.1542  , 'USD'
)

SELECT date, currency, price
  , LEAD(price,2,0) OVER (ORDER BY date) previose_day_price
FROM currency;

 

LEAD의 경우 LAG와 방향만 다를 뿐 문법이 동일하기 때문에 별다른 설명 없이 쿼리와 결과만 남겨 두겠다.

 

 

 

 

 

---

전일 대비 또는 전월 대비 등의 결과는 지표에서 많이 사용하는 기능이다.

그것이 더 효율적이라서 그런진 모르겠지만 지금까지 LAG, LEAD 함수를 사용하지 않고 Subquery를 사용하는 경우가 많았다.

다음에 같은 작업을 할 때는 두 방법을 비교해봐야겠다.

 

다음 포스트에서는 Transforming an Array to Rows 내용을 정리한다.