Analytic SQL 5. 이동 평균(Moving Average)과 유의 사항

다른 집계 함수와 데이터 처리 관련 개념을 따로 빼놓기는 했다. 이는 window절의 심화 과정보다는 응용에 가까운 개념이라 판단되어 새로운 장으로 설명해보도록 한다.

 

이동 평균(Moving Average)이란?

평균을 구하는 범위 구간을 이동시키면서 구하는 평균값을 의미함.

 

지금껏 자주 사용되는 누적합과 더불어 누적 평균도 존재는 하다만, 누적평균보다는 이동 평균이 더 보편적으로 쓰이는 편.

여기서의 범위 구간 = 이미 배웠던 window의 정의와 연결시켜 생각한다면 쉽게 이해할 수 있을 것 같다.

 

이를 구하는 이유는 뭘까?

주식과 같은 항목처럼 단기간 변동성이 심한 항목들에 대해서 이동 평균을 접목시키면 효과적인 시각화가 가능하다.

전반적인 추세를 확인하기 어려운 항목에 대해 효과적인 집계/시각화가 가능한 이동 평균(Moving Average)

 

이동 평균을 의미하는 SQL 구문의 예시는 아래와 같다.

select *,
avg(money) over(order by buy_date rows between 2 preceding and current row)
from a_table;

 

직전의 열 2개의 값을 불러와 최대 3개의 열에 대한 이동 평균을 구하는 코드임을 알 수 있다.

이때, 첫 번째 열과 두 번째 열은 3개의 열을 만들 수 없기 때문에 평균을 구함에 있어서 각각이 1,2로 나누어짐을 알고 가면 더 좋을 것이다.

 

이를 응용한 중앙 평균이라는 값도 가끔 사용이 된다. 이는 이동 평균과 다르게 직전/직후의 값을 참조하여 평균을 구한다는 차이가 존재함

select *,
avg(money) over(order by buy_date rows between 1 preceding and 1 following)
from a_table;

---

집계 함수 시 유의 사항 - Null의 처리

다른 장에서 설명한 바가 있었지만, null의 처리에 대해서는 특히 자주하는 실수가 많기 때문에 한 번 더 짚고 넘어가도록 한다.

 

직전 장에서 다음과 같은 a_table을 만들었었다.

학번	날짜	소유돈
101	2023-01-01	100
101	2023-01-02	150
101	2023-01-03	130
101	2023-01-05	180
101	2023-01-08	200

상기 테이블은 1월 4일/6일/7일이 존재하지 않는 데이터라 하여도 무방하다. 하지만 날짜별로 outer join을 시켜 다음과 같은 처리를 할 수도 있을 것 같다.

 

날짜	소유돈
2023-01-01	100
2023-01-02	150
2023-01-03	130
2023-01-04	null
2023-01-05	180
2023-01-06	null
2023-01-07	null
2023-07-08	200

상기 테이블처럼 명시적으로 null을 만들어준 뒤 이동 평균을 취해준다면 어떻게 될까. 아마 예상하다시피 물리적인 열만을 취급하는 rows의 특성으로 해당 열은 계산하지 않고 처리(평균을 위한 나눗셈 포함)가 되는 것을 확인할 수 있다.

 

select *,
	avg(소유돈) over(order by 날짜 rows between 2 preceding and current row) as 3일_이동평균
from join_table;

날짜	소유돈	3일_이동평균
2023-01-01	100	100
2023-01-02	150	125
2023-01-03	130	126.6667
2023-01-04	null	140
2023-01-05	180	155
2023-01-06	null	180
2023-01-07	null	180
2023-07-08	200	200

 

해당 열을 포함시켜 평균을 구하고 싶다면 null을 전부 0으로 바꿔주는 등의 처리를 통하여야지만 우리가 생각하는 정상적인 이동 평균을 도출할 수 있을 것이다. 

select *,
	avg(coalesce(소유돈, 0)) over(order by 날짜 rows between 2 preceding and current row) as 3일_이동평균
from join_table;

 

💡 coalesce 함수를 이용하여 비어있는 값을 적당히 처리해주는 센스가 필요하다.
Pandas에서의 fillna를 보는 것 같다...

---

배우면서 추가된 개념들 - rows와 range의 차이

 

직전 장에서 rows는 물리적인 위치, range는 논리적인 위치라는 언급을 한 바 있다. 하지만 이것은 공부의 이해를 돕기 위해 그렇게 설명한 것이지, 이 둘의 차이를 설명하라고 하면 사람마다 의견이 분분하다고 한다.

 

unbounded preceding/following 를 위의 개념과 결부시켜 생각해보면 해당 키워드 역시 물리적인 키워드가 아닌 논리적인 키워드임을 유추해낼 수 있다. 따라서 예시로 사용된 이 키워드를 range에도 사용이 가능하다는 이야기.

 

range 키워드는 rows와 동일한 window 절 크기 문법도 사용이 가능하다.(ex : unbounded preceding/following)

 

range의 다른 유의 사항 - default range

해당 유의사항은 왜 기본값이 range between unbounded preceding and current row인지에 대한 내용이다.

이번에도 a_table을 사용하되, 행을 일부 추가하여 테이블을 생성해 본다.

학번	날짜	소유돈
101	2023-01-01	100
101	2023-01-02	150
101	2023-01-03	130
101	2023-01-05	180
101	2023-01-05	120
101	2023-01-08	200

극적인 차이를 위해 같은 학번에 같은 날짜인데 소유돈만 다른 행을 추가하였다. 이를 rows와 range를 사용하여 그 결과를 비교해 보자.

 

select *,
    sum(소유돈) over(order by 날짜) as range_sum,
    sum(소유돈) over(order by 날짜 rows between unbounded preceding and current row) as rows_sum
from a_table;

order by 이후 키워드를 생략하면 암묵적으로 range between unbounded preceding and current row를 생성한다는 것을 이용하여 축약된 코드를 작성하였고, 이를 테이블로 표현하면 다음과 같다.

 

학번	날짜	소유돈	range_sum	rows_sum
101	2023-01-01	100	100	100
101	2023-01-02	150	250	250
101	2023-01-03	130	380	380
101	2023-01-05	180	680	560
101	2023-01-05	120	680	680
101	2023-01-08	200	880	880

 

같은 날짜(order by 된 열을 기준) 기준으로 같은 값에 대한 처리를 각기 다르게 하는 것을 알 수 있다.

range 절을 이용하는 경우에는 같은 1월 5일에 대해 아예 전부 합한 300으로 취급하여 누적합이 계산된 것을 알 수 있고,

rows 절을 이용하는 경우에는 같은 1월 5일이더라도 하나의 행을 독립적인 행으로 간주하여 누적합이 계산된 것을 알 수 있다.

 

해당 합 모두 뭐가 틀리다고 이야기는 할 수 없지만, 해당 차이를 알면서 default 값이 range between unbounded preceding and current row임은 꼭 알고 넘어가야 할 것이다.

 

지금까지의 관련된 rows와 range와 관련된 유의사항을 모두 나열해보면 아래와 같다.

 

1. rows는 물리적인 행의 위치, range는 논리적인 행(대체적으로 interval)의 위치를 의미한다.
2. order by 절 이후를 생략한다면 기본 키워드는 range between unbounded preceding and current row를 반환한다.
3. range 키워드는 존재하지 않는 날짜의 논리적인 순서와 더불어 같은 날짜에 대한 동시 취급을 진행한 후 집계를 낸다.
4. range 키워드는 rows와 동일한 window 절 크기 문법도 사용이 가능하다.(ex : unbounded preceding/following) 

 

rows와 range에 대한 헷갈리는 정의가 많다. 상기 4개의 문장을 읽고 이해할 수 있도록 해당 내용을 충분히 복습해보도록 하자.