[sql] 가중 이동평균

https://indistract.tistory.com/60

[sql] 이동평균

 

우리는 데이터를 바라볼때, 모든 기간의 데이터에 대하여 균등한 무게를 두고 분석하지는 않는다. 예를들어, 장염에 걸린 환자가 병원에 찾아간다면 의사는 환자가 '어제' 어떤 음식을 먹었는지 궁금해하며, '1년전'에 어떤 음식을 먹었는지 물어보지는 않을 것이다. 이처럼 최신의 데이터일수록 좀더 중요성을 부여해야할 경우가 종종있다. 그리고 이를 적용한 개념중 하나가 '가중 이동평균' 이다.

 

이전 게시글에서는 '이동평균' 에 대해서 알아봤다. 이동평균은 최근 N일의 수치 데이터의 평균을 매 일자마다 구하는 것이었으며, 각 N일마다 가중치는 부여되지 않았다. 반면, '가중 이동평균'은 데이터의 최신성에 따라 가중치를 부여한다.

---

우리는 sql을 이용해 가중이동 평균매출을 구해보고자 한다. sql에서 가중이동 평균을 구하기위해서는 아래 과정을 거쳐야한다. 

 

① 일별 매출값 계산

② self join을 이용해 각 일별로 당일 ~ 4일전 일자의 매출값 붙이기

③ case when으로 일자별로 가중치 부여

④ 가중 이동평균을 구할수 없는 일자 필터

 

① 먼저, 아래와 같이 일별 매출액을 구해준다. row_number()를 이용해서 날짜 순서대로 넘버링을 부여하는데, 이는 나중에 가중치를 계산할때 쓰기 위함이다.

with
tmp1 as (
	select date_trunc('day', o.order_date)::date as d_day,
		sum(amount) as sum_amount,
		row_number() over(order by date_trunc('day', o.order_date)::date) as rnum
	from nw.orders o 
		left join nw.order_items oi on o.order_id = oi.order_id
	group by 1
	order by 1
)

 

 

 

② 다음으로는 위에서 구한 tmp1을 이용해, self join (between join)을 해줘야한다.

tmp2 as (
	select a.d_day, a.sum_amount, a.rnum, b.d_day as d_day_back, b.sum_amount as sum_amount_back, b.rnum as rnum_back 
	from tmp1 a
		join tmp1 b on a.rnum between b.rnum and b.rnum+4 
)

 

self join을 할때는 on 조건절에 between 함수를 사용할수있다.우리는 위에서 적용했던 rnum을 between 조건절에 이용하고자 한다.

 

"on a.rnum between b.rnum and b.rnum+4" 조건을 걸게되면 rnum은 아래 표와같이 매칭된다. 

a.rnum	b.rnum
1	1
2	1, 2
3	1, 2, 3
4	1, 2, 3, 4
5	2, 3, 4, 5

 

이처럼 매칭하게되면 결론적으로 아래처럼 각 일자별로 당일 + 최대 3일전까지의 매출정보를 붙여서 가져올수있게된다.

 

③ 다음으로는 위 테이블을 이용해서 일자(d_day 칼럼)를 기준으로 그룹핑 하여, 가중 이동평균을 구하려한다.

 

1. 당일 일자는 가중치 1.5 (rnum과 rum_back 의 차이가 0일때)

2. 1일전 ~ 3일전 일자는 가중치 1 (rnum과 rum_back 의 차이가 1, 2, 3일때)

3. 5일전 일자는 가중치 0.5 (rnum과 rum_back 의 차이가 4일때)

 

가중치는 위와같이 부여하며, 이는 sum 함수내에 case when 구문을 아래와 같이 작성해주면 가중치 계산이 가능하다. case when 함수내에 rnum과 rnum_back의 차이에 따라 매출에 가중치를 각각 계산해주면 된다. 

 

select d_day,
		sum(
			case 
			when rnum - rnum_back = 0 then sum_amount_back * 1.5
			when rnum - rnum_back in (1, 2, 3) then sum_amount_back * 1
			when rnum - rnum_back = 4 then sum_amount_back * 0.5
			end
			) / 5 as weighted_avg_amount
	from tmp2
	group by d_day
	order by d_day

 

④ 그리고 마지막으로 각 일자별로 1일전, 2일전, 3일전, 그리고 4일전 데이터가 하나라도 누락된다면 데이터에서 제외해야한다. 일자 데이터가 하나라도 누락되면 '5일 가중평균' 을 구할수 없기 때문이다. 이는 간단하게 having절로 데이터를 필터링해주면 된다.

select d_day,
		sum(
			case 
			when rnum - rnum_back = 0 then sum_amount_back * 1.5
			when rnum - rnum_back in (1, 2, 3) then sum_amount_back * 1
			when rnum - rnum_back = 4 then sum_amount_back * 0.5
			end
			) / 5 as weighted_avg_amount,
		count(*) as cnt
	from tmp2
	group by d_day
	having count(*) = 5
	order by d_day
;

 

 

완성된 쿼리와 테이블은 아래와 같다.

-- 1. 일별매출 구하기
with
tmp1 as (
	select date_trunc('day', o.order_date)::date as d_day,
		sum(amount) as sum_amount,
		row_number() over(order by date_trunc('day',o.order_date)::date) as rnum
	from nw.orders o 
		left join nw.order_items oi on o.order_id = oi.order_id
	group by 1
	order by 1
),
-- 2. between join (각 일자별로 0일전 ~ 4일전 데이터 join)
tmp2 as (
	select a.d_day, a.sum_amount, a.rnum, b.d_day as d_day_back, b.sum_amount as sum_amount_back, b.rnum as rnum_back 
	from tmp1 a
		join tmp1 b on a.rnum between b.rnum and b.rnum+4 
)
-- 3. 가중 이동평균 구하기
select d_day,
		sum(
			case 
			when rnum - rnum_back = 0 then sum_amount_back * 1.5
			when rnum - rnum_back in (1, 2, 3) then sum_amount_back * 1
			when rnum - rnum_back = 4 then sum_amount_back * 0.5
			end
			) / 5 as weighted_avg_amount,
		count(*) as cnt
	from tmp2
	group by d_day
	having count(*) = 5
	order by d_day
;