이빠진 데이터를 채워넣어보자 (재귀 테이블, 윈도우 함수)

문제는 

- 월별(년-월) 주문 건수를 계산하되, 주문이 없는 달도 0건으로 포함하고

- 지난달 대비 주문 건수 증감율도 계산해야 하는 문제다

 

우선 주문 시간을 월별로 GROUP BY 한 뒤 주문건수를 구해보자.

 

16년 10월, 11월 데이터가 빠져있는 것을 확인할 수 있다.

빠진 월을 포함할 수 있도록 재귀 테이블을 만들어준다.

 

먼저 재귀테이블의 구조를 알아보자

 

이 구조에서 날짜값을 하나씩 더해갈 건데 초기값 2016-10 부터 한달씩 더해야 한다.

이때 DATE_ADD 함수가 쓰이는데 이 함수에 대해서도 알아 둘 필요가 있다.

 

이제 테이블을 만들어보자. DATE_ADD의 데이터로는 완전한 날짜 형식 (예: '2022-10-01') 이 들어가야 한다

 

테이블이 만들어졌으니 먼저 확인한 테이블과 현재 테이블 기준으로 LEFT JOIN 해주자

 

 

잘 만들어진걸 확인할 수 있다. COUNT(*)은 NULL인 값도 세므로 order_id를 세어준다

다음으로 지난 달 대비 증감율을 구해야 하는데, order_cnt 이전 행의 값을 가져와줘야 한다.

LAG 윈도우 함수를 이용해보자

 

윈도우 함수의 구조는 다음과 같다

LAG() 함수에 대해서도 알아보자. LAG 함수의 구조는 다음과 같다.

 

앞서 만든 테이블을 CTE로 보내 윈도우 함수를 적용해보자

 

 

order_cnt의 이전 행을 가져오는 컬럼을 만들었다. 이제 증감률을 계산할 차례이다

증감률은 다음과 같이 구한다

(현재 cnt - 이전 cnt) / 이전 cnt * 100

 

앞선 테이블을 서브쿼리로 보내 증감률 구할건데 컬럼 만들기 전에 처리해야 할 것이 있다.

이전 cnt 중에 0인 값이 있는데 0으로 나누게 되면 Division by 0 에러가 발생한다.

이를 처리하기 위해 이전행으로 나눌 때 IF문을 활용하여 0이면 1로 바꿔주자

 

 

완성이다