[SQL] LAG 함수를 이용한 전일대비 증가분 계산

0. 목표

 - LAG 함수를 이용하여 전일대비 금일의 증가량 계산

1. 실습

 1) 테이블 생성

SELECT DATE, category, Price, 
	IFNULL(LAG(Price, 1) OVER (PARTITION BY Category ORDER BY DATE), 0) AS Price2
FROM lag_test
ORDER BY DATE

 2) 데이터 삽입

INSERT INTO lag_test(DATE, Category, Price) VALUES('2020-10-01', 'a', 100);
INSERT INTO lag_test(DATE, Category, Price) VALUES('2020-10-02', 'a', 200);
INSERT INTO lag_test(DATE, Category, Price) VALUES('2020-10-03', 'a', 400);
INSERT INTO lag_test(DATE, Category, Price) VALUES('2020-10-04', 'a', 800);
INSERT INTO lag_test(DATE, Category, Price) VALUES('2020-10-05', 'a', 1600);
INSERT INTO lag_test(DATE, Category, Price) VALUES('2020-10-06', 'a', 3200);
INSERT INTO lag_test(DATE, Category, Price) VALUES('2020-10-07', 'a', 6400);
INSERT INTO lag_test(DATE, Category, Price) VALUES('2020-10-08', 'a', 12800);
INSERT INTO lag_test(DATE, Category, Price) VALUES('2020-10-09', 'a', 25600);
INSERT INTO lag_test(DATE, Category, Price) VALUES('2020-10-10', 'a', 51200);

 3) 데이터 확인

SELECT * FROM lag_test order by DATE;

  - 결과

 4) lag 함수를 사용한 전일 정보 불러오기

SELECT 
	DATE, category, Price, 
	IFNULL(LAG(Price, 1) OVER (PARTITION BY Category ORDER BY DATE), 0) AS Price2
FROM lag_test;

  - 결과

 5) 증가분 구하기

SELECT 
	A.DATE, A.Price - A.Price2
FROM (
	SELECT 
		DATE, category, Price, 
		IFNULL(LAG(Price, 1) OVER (PARTITION BY Category ORDER BY DATE), 0) AS Price2
	FROM lag_test
) AS A;

  - 결과

2. 참고

 - MySQL | LEAD, LAG 윈도우 함수