Redshift - LAG(), LEAD() : 이전 행 값 반환, 이후 행 값 반환

 

 

LAG(), LEAD()

Syntax

LAG(column_name_1, offset, default_value) OVER (PARTITION BY column_name_2 ORDER BY column_name_3 ASC/DESC)
LEAD(column_name_1, offset, default_value) OVER (PARTITION BY column_name_2 ORDER BY column_name_3 ASC/DESC)

 

LAG 함수
LAG 함수는 LAG 함수에 명시된 column_name_1 기준으로 이전 행의 값을 반환합니다.

column_name_1
이전 행의 값을 표시할 때 그 값을 가져올 column_name입니다. necessary parameter 입니다.

offset
이전 행의 값을 표시할 때 얼마만큼 이전 행의 값을 가져올지에 대한 내용입니다.
optional parameter이며 생략 시 default 값은 1입니다.
offset 값이 1로 명시되어있으면 1행 이전의 값을, offset이 2라면 2행 이전의 값을 불러오게됩니다.

default_value
이전 행 값이 없을 경우 원래는 NULL로 표시되는데 default_value값을 명시해주면 NULL대신 이 default_value값으로 표시됩니다.
이 값은 optional parameter입니다.

column_name_2
PARTITION BY 부분을 생략하면 내가 참조하려는 테이블에 있는 column_name_1에서 테이블에 있는 전체 row를 대상으로 이전값을 불러옵니다.
그러나 PARTITION BY를 명시하면 column_name_2 값이 동일한 row끼리 한 세트로 가정하여 이 한 세트에 대해서 LAG 함수를 적용합니다.
optional paramter입니다.

column_name_3
보통 이전 행이라고 하면 테이블에 적재된 순서일테지만 LAG 함수에선 어떤 컬럼 기준으로 내림차순 OR 오름차순을 해준 후에 이전행, 다음행을 판단합니다.
necessary parameter입니다.

LEAD 함수는 모든 것이 LAG 함수와 동일하지만 단 한 가지 다른 점은 LAG 함수가 이전 row의 값을 불러온다면 LEAD 함수는 다음 row의 값을 가져온다는 것입니다.

 

 

 

 

 

 

offset, default_value 인자가 없는 예시

Table_name: sales_forecast_info

product_group	sale_start_date	product_name	sales_forecast
10	2019-10-18	Pizza	1200
10	2019-01-02	Potatochip	1100
30	2018-12-01	Cookie	1500
20	2017-05-29	Milk	2300
30	2018-07-15	Apple	800
30	2019-10-07	Grape	950
10	2018-03-23	Watermelon	700
20	2020-02-10	Yellowmelon	1000
20	2020-02-11	Hamburger	1150
10	2020-01-10	Macbook	200
20	2019-08-27	GalaxyWatch	300
30	2019-03-15	Ipad	500

 

 

 

 

SELECT  product_group, product_name, sale_start_date, sales_forecast
        LAG(sales_forecast) OVER (ORDER BY sale_start_date ASC) AS lag_ex,
        LEAD(sales_forecast) OVER (ORDER BY sale_start_date ASC) AS lead_ex
FROM sales_forecast_info

 

product_group	sale_start_date	sales_forecast	lag_ex	lead_ex
20	2017-05-29	2300	NULL	700
10	2018-03-23	700	2300	800
30	2018-07-15	800	700	1500
30	2018-12-01	1500	800	1100
10	2019-01-02	1100	1500	500
30	2019-03-15	500	1100	300
20	2019-08-27	300	500	950
30	2019-10-07	950	300	1200
10	2019-10-18	1200	950	200
10	2020-01-10	200	1200	1000
20	2020-02-10	1000	200	1150
20	2020-02-11	1150	1000	NULL

 

sales_forecast 컬럼은 원래의 sales_forecast 정보를 담고 있습니다.

lag_ex 컬럼을 보면 두번째 행이 첫 번째 행의 값인 2300을 가지고 있으며, 세 번째 행은 두 번째 행의 값인 700을 가지고 있는걸 알 수 있습니다.
그리고 lag_ex의 첫 번째 행은 sales_forecast 컬럼에서 더 이전 행이 없으므로 NULL값을 가지게 됩니다.
이렇게 LAG 함수는 이전 행의 값을 가져옵니다.

lead_ex 컬럼을 보면 첫 번째 행이 두 번째 행의 값인 700을 가지고 있으며, 두 번째 행은 세 번째 행의 값인 800을 가지고 있습니다.
그리고 가장 마지막 행은 sales_forecast 컬럼에서 더 이후의 행이 없으므로 NULL값을 가지게 됩니다.

 

 

 

 

 

SELECT  product_group, product_name, sale_start_date, sales_forecast
        LAG(sales_forecast) OVER (PARTITION BY product_group ORDER BY sale_start_date ASC) AS lag_ex,
        LEAD(sales_forecast) OVER ( PARTITION BY product_group ORDER BY sale_start_date ASC) AS lead_ex
FROM sales_forecast_info

 

 

product_group	sale_start_date	sales_forecast	lag_ex	lead_ex
10	2018-03-23	700	NULL	1100
10	2019-01-02	1100	700	1200
10	2019-10-18	1200	1100	200
10	2020-01-10	200	1200	NULL
20	2017-05-29	2300	NULL	300
20	2019-08-27	300	2300	1000
20	2020-02-10	1000	300	1150
20	2020-02-11	1150	1000	NULL
30	2018-07-15	800	NULL	1500
30	2018-12-01	1500	800	500
30	2019-03-15	500	1500	950
30	2019-10-07	950	500	NULL

 

이전 예시와는 다르게 PARTITION BY 부분이 생겼습니다.
LAG 함수에서 PARTITION BY의 조건으로 product_group이 제시되었습니다. 즉, product_group이 동일한 행들을 한 세트로하여 LAG를 적용시킵니다.

LEAD 함수에서 PARTITION BY의 조건으로 product_group이 제시되었습니다. 즉, product_group이 동일한 행들을 한 세트로하여 LEAD를 적용시킵니다.

또한 ORDER BY product_group ORDER BY sale_start_date 이므로 동일한 product_group에 대해 sale_statrt_date를 기준으로 하여 각 product_group당 sales_start_date로 오름차순 정렬하여 함수를 적용시킵니다.

LAG 함수는 이전 값을 끌어오므로 각 prouct_name에서 이전 값이 없는 첫 행의 값은 모두 NULL입니다.

LEAD 함수도 LAG 함수와 사용법은 완전히 똑같지만 다음 행의 값을 가져오는거라서 product_group에 다음 행이 없는 네 번째 행이 모두 NULL값으로 표시됨을 알 수 있습니다.

 

 

 

 

 

 

 

offset, default_value 인자가 있는 예시

 

 

SELECT  product_group, product_name, sale_start_date, sales_forecast
        LAG(sales_forecast, 2, 0) OVER (ORDER BY sale_start_date ASC) AS lag_ex,
        LEAD(sales_forecast, 2, 0) OVER (ORDER BY sale_start_date ASC) AS lead_ex
FROM sales_forecast_info

 

product_group	sale_start_date	sales_forecast	lag_ex	lead_ex
20	2017-05-29	2300	0	800
10	2018-03-23	700	0	1500
30	2018-07-15	800	2300	1100
30	2018-12-01	1500	700	500
10	2019-01-02	1100	800	300
30	2019-03-15	500	1500	950
20	2019-08-27	300	1100	1200
30	2019-10-07	950	300	1200
10	2019-10-18	1200	950	200
10	2020-01-10	200	1200	1000
20	2020-02-10	1000	200	0
20	2020-02-11	1150	1000	0

 

LAG(sales_forecast, 2, 0)에서 2라는 숫자는 보면 다음 행의 값을 가져올 때 한 칸이아닌 두 칸 이전 행에 있는 정보를 가져오라는 인자입니다.
lag_ex 컬럼을 보면 세번째 행이 첫 번째 행의 값인 2300을 가지고 있으며, 네 번째 행은 두 번째 행의 값인 700을 가지고 있는걸 알 수 있습니다.
그리고 lag_ex의 첫 번째, 두 번째 행은 sales_forecast 컬럼에서 더 이전의 행이 없으므로 NULL값을 가지게 됩니다.
다만 LAG(sales_forecast, 2, 0)에서처럼 0이라는 숫자는 NULL값일 때 어떤 값으로 대체할지를 나타내므로 NULL이 아닌 0으로 표시되게 되죠.

LEAD(sales_forecast, 2, 0)에서 2라는 숫자는 보면 다음 행의 값을 가져올 때 한 칸이아닌 두 칸 이후 행에 있는 정보를 가져오라는 인자입니다.
lead_ex 컬럼을 보면 첫 번째 행이 세 번째 행의 값인 800을 가지고 있으며, 두 번째 행은 네 번째 행의 값인 1500을 가지고 있는걸 알 수 있습니다.
그리고 lead_ex의 마지막, 그리고 그 위의 행은 sales_forecast 컬럼에서 더 이후의 행이 없으므로 NULL값을 가지게 됩니다.
다만 LEAD(sales_forecast, 2, 0)에서처럼 0이라는 숫자는 NULL값일 때 어떤 값으로 대체할지를 나타내므로 NULL이 아닌 0으로 표시되게 되죠.

 

 

 

 

 

 

SELECT  product_group, product_name, sale_start_date, sales_forecast
        LAG(sales_forecast, 2, 0) OVER (PARTITION BY product_group ORDER BY sale_start_date ASC) AS lag_ex,
        LEAD(sales_forecast, 2, 0) OVER ( PARTITION BY product_group ORDER BY sale_start_date ASC) AS lead_ex
FROM sales_forecast_info

 

product_group	sale_start_date	sales_forecast	lag_ex	lead_ex
10	2018-03-23	700	0	1200
10	2019-01-02	1100	0	200
10	2019-10-18	1200	700	0
10	2020-01-10	200	1100	0
20	2017-05-29	2300	0	1000
20	2019-08-27	300	0	1150
20	2020-02-10	1000	2300	0
20	2020-02-11	1150	300	0
30	2018-07-15	800	0	500
30	2018-12-01	1500	0	950
30	2019-03-15	500	800	0
30	2019-10-07	950	1500	0

 

마찬가지로 PARTITION BY가 주어진 경우입니다.

product_group == 10인 부분을 보면 첫 번째, 두 번째 행은 값을 가져올 이전 2개의 행이 없으므로 NULL값을 가집니다.
다만 NULL값일 때 0이라는 값을 출력하라는 내용이 LAG 함수의 인자로 존재하기 때문에 NULL이 아닌 0이라는 값을 가지게 됩니다.
또한 세 번째 행은 두 개의 행 전인 700이라는 값을 가집니다. 네 번째 행은 두 개의 행 전인 1100이라는 값을 가집니다.

product_group == 10인 부분을 다시 보면 첫 번째 행은 두 행 다음에 있는 1200이라는 값을 가져옵니다. 두 번째 행은 두 행 다음에 있는 200이라는 값을 가지게 됩니다.
세 번째, 네 번째 행은 더 이상 그 이후의 행이 존재하지 않아 이후의 행에서 가져올 값이 없으므로 NULL로 출력됩니다.
다만 NULL값일 때 NULL 대신 0이라는 값을 표시하라고 LEAD 함수의 인자로 제시되었기 때문에 NULL이 아닌 0이라는 값을 가지게 됩니다.