[열정페이 - SQL] 3장 시계열 분석 실습

---

https://github.com/sql-for-data-analysis-kr/book

GitHub - sql-for-data-analysis-kr/book

여기에 있는 소매업 매출 데이터 셋을 가지고 실습을 진행

 

원래는 csv 파일 다운받고 테이블 생성 코드 실행해서 테이블 생성해야하는데 버전이 안맞는건지 실행이 안됨

 

 

그래서

이런식으로  Schemas > public > table에서 직접 테이블을 만들고 데이터 임폴트를 하였다. 

처음에 이 방식을 찾기까지 오랜시간이 걸렸지만 앞으로 실습 데이터 셋은 이렇게 생성하지 않을까 싶다.

---

- 데이터 살펴보기

테이블은 간단하게 5개의 칼럼과 22,620개의 로우로 구성되어 있다.

칼럼을 자세히 살펴보면 sales_month는 1992년부터 2020년까지 판매한 달을 의미한다 따라서 day는 변하지 않고 1로 고정 되어있고 month에 해당하는 값을 기준으로 분류했다.

 

두번째 칼럼은 naics_code는 (North American Industry Classification System code)**는 북미 산업 분류 시스템이다.

6자리 숫자로 이루어져 있으며 각자리의 숫자는

• 첫 번째와 두 번째 숫자: 경제 섹터 (예: 농업, 제조업 등).
• 세 번째 숫자: 하위 섹터.
• 네 번째 숫자: 산업 그룹.
• 다섯 번째 숫자: 산업.
• 여섯 번째 숫자: 특정 국가의 산업 특화.

이런 의미를 가지고 있다.

 

사진에 보이는 숫자들로 예시를 들어보면

 

• 4411: 자동차 딜러 (Automobile Dealers)
• 4412: 오토바이, 보트 및 기타 차량 딜러 (Other Motor Vehicle Dealers)
• 4413: 자동차 부품, 타이어 및 액세서리 딜러 (Automotive Parts, Accessories, and Tire Stores)

이런식인 것 같다.

 

그리고 세번째 kind_of_business는 문자열로 naics_code를 설명해주고 있다.

naics_code가 같으면 kind_of_business도 같은 종속의 관계를 가지고 있기에 이 칼럼을 산업의 분류 정도로 생각할 수 있겠다.

 

네번째 칼럼인 reason_for_null은  마지막 칼럼의 값이 null인 이유를 설명해준다.

이런식으로 3가지의 값이 존재한다.

 

마지막 칼럼인 sales는 시기와 산업별 판매 수량? 판매액? 을 나타낸다.

 

데이터를 간단하게 살펴본 결과 실습 데이터로 해볼 수 있는것은 특정 산업 분야별로 시간별 판매추이가 증가하는 혹은 감소하는지 판단이나 같은 시기에 산업 분야별 판매추이 비교? 정도가 있을 것 같다.

---

- 간단한 트렌드 분석

select sales_month,sales from retail_sales
where kind_of_business = 'Book stores';

이런식으로 특정 분야에 대한 조건을 주면 간단하게 시간에 따른 매출 트렌드를 확인할 수 있다.

서점 매출 트렌드

 

나는 서점의 매출 트렌드가 궁금해 검색을 해 본 결과 뭔가 증가하다가 감소하는 경향이 있는 것 같지만 노이즈가 심해 명확히 알아보기 힘들다. 

 

이 때 date_part 함수를 사용해 연도별로 검색한다면?

select date_part('year',sales_month),sum(sales) from retail_sales
where kind_of_business = 'Book stores'
group by 1
order by 1;

sales_month에서 연도만 가져온 다음에 그 값을 기준으로 그룹화 하여 노이즈를 제거하였다.

이 과정에서 연도별 정렬 및 연도별 매출 총계 표현을 위해 order_by 절과 sum 집계함수를 추가로 사용해야 한다.

노이즈를 제거한 모습

 

해당 데이터는 1992년도 부터 2020년까지 충분한 기간동안 수집된 데이터이기 때문에 연도별로 묶어서 분석을 해도 충분했지만 데이터나 목적에 따라 적절한 기간을 기준으로 비교해야 한다.

---

- 전달하는 목적에 따른 다양한 표현법

1. 여성 의류업 매출은 남겅 의류업 매출보다 287억만큼 크다. (여성 매출액 - 남성 매출액)

 

2. 여성 의류업 매출은 남성 의류업 매출의 4.9배이다. (여성 매출액/남성 매출액)

 

3. 여성 의류업 매출은 남성 의류업 매출의 390% 만큼 크다. (여성매출액/ 남성 매출액 - 1) * 100

 

4. 전체 대비 비율(self-join 또는 PARTITION BY 활용)

 

모두 동일한 분석 결과를 설명하지만 전달하려는 목적에 따라 적절한 문장을 사용해야한다....

 

설득력을 높이기 위해 중요한 부분이지만 어떤 상황에 어떤 문장이 잘어울리는지는 아직 잘 모르겠다. 

 

아마 데이터에 대한 이해도가 부족해서 그런듯 하다.

---

 

- 인덱싱으로 시계열 데이터 변환 이해하기

 

데이터 인덱싱이란?

시계열에서 베이스 구간(시작 지점)을 기준을 기주능로 데이터 변화량을 이해하는 방법

Ex) CPI 

 

예시 

SELECT sales_year,sales, first_value(sales) over (order by sales_year) as index_sales
from 
(
	SELECT date_part('year',sales_month) as sales_year ,sum(sales) as sales
	from retail_sales
	Where kind_of_business = 'Women''s clothing stores'
	GROUP by 1
) a ;

실행결과

전체 데이터의 첫 행의 sales 값을 반환해주는 index_sales가 데이터 인덱싱의 예시이다.

이를 이용해 매년마다 판매수익이 늘어났는지 줄어들었는지 파악이 쉬워진다.

이 값을 이용해 비율 변화 또한 나타낼 수 있다.

---

- 시간 윈도우 롤링

이동계산이라고도 불리며 노이즈를 제거하고 여러 구간을 설정해 트렌드를 분석하는 방법

 

윈도우 사이즈 - 계산에 포함할 시간 구간의 개수

> 값이 크면 노이즈가 부드러워지고 민감도는 낮아진다. 

> 값이 작으면 짧은 주기의 변화에는 민감하지만 노이즈에 취약해진다

 

기술과 경영 시간에 배운 DAU, WAU, MAU가 윈도우 롤링 기법을 사용한 예시이다.

상황에 따라 어떠한 지표가 좋은지는 달라진다는 특징이 있다.

 

예시 코드

SELECT a.sales_month, 
	   a.sales, round(avg(b.sales),2) as moving_avg, 
	   count(b.sales) as records_count
From retail_sales a 
JOIN retail_sales b on a.kind_of_business = b.kind_of_business
	and b.sales_month between a.sales_month - interval '11 months'
	and a.sales_month 
	and b.kind_of_business = 'Women''s clothing stores'
Where a.kind_of_business = 'Women''s clothing stores'
and a.sales_month >= '1993-01-01'
group by 1,2
order by 1 ;

위 코드를 살펴보면 데이터 셋이 1992년부터 시작하므로 이전 레코드가 12개보다 많은 1993년 이후로 필터링을 하였고 

BETWEEN 절을 이용하여 이동평균을 계산할 범위를 지정하였다. 여기서 주의할 점은 BETWEEN 절은 명시된 날짜를 포함하기 때문에 interval '12 months' 로 작성하지 않도록 해야한다. 이를 확인하기 위해 records_count열을 추가해 값이 12인지 확인한다.

실행결과

시각화

앞서 봤던 단순 매출 그래프와 달리 노이즈가 많이 제거된 모습

 

---

- 원도우 함수를 이용한 윈도우 롤링

Frame 함수를 이용

{ RANGE or ROWS or GROUPS } BETWEEN frame_start AND frame_end     -  기본형식

 

중괄호 안에는 셋 중 상황에 맞는 하나를 선택

 

frame_start 와 frame_end 는 다음 값 중 하나로 지정

 - UNBOUNDED PRECEDING              :   현재 행 이전의 모든 행

 - offset(숫자가 들어감) PRECEDING   :    현재 행 이전의 (숫자) 행

 - CURRENT ROW                                :    현재 행

 - offset(숫자가 들어감) FOLLOWING   :    현재 행 이후의 (숫자) 행

 - UNBOUNDED FOLLOWING             :     현재 행 이후의 모든 행

 

예시코드

SELECT sales_month
	   ,avg(sales) over (order by sales_month
	   					 rows between 11 preceding and current row) as moving_avg
	   ,count(sales) over (order by sales_month
	   					 rows between 11 preceding and current row) as record_count
From retail_sales
Where kind_of_business = 'Women''s clothing stores';

이전에 살펴본 JOIN을 이용한 윈도우 롤링과 같은 결과를 반환하지만 더 짧고 가독성 있는 코드가 만들 수 있다.

Frame 함수는 복잡해 보이지만 활용도가 높기에 좀 더 연습을 해야할 것이다.

또한 이전 11행이 존재하지 않는 경우에는 제거하고 계산하기 때문에 1992년의 값도 구할 수 있다.

물론 어떤 방법이 더 좋은지는 상황에 따라 다르다.

---

- 누적값 계산하기

윈도우 롤링을 이용해서 지난 12개월동안의 합이나 평균을 구하였다면 이번에는 시작 포인트 부터 윈도우 크기를 점점 늘려가며 계산을 수행하는 누적값 계산이 있다.

 

간단한 개념이니 바로 예시 코드

SELECT sales_month, sales, sum(sales) over (partition by date_part('year',sales_month)
											 order by sales_month) as sales_ytd
FROM retail_sales
Where kind_of_business = 'Women''s clothing stores';

실행결과

이렇듯이 윈도우 함수를 이용해 1년간 누적값을 계산할 수 있다 윈도우의 사이즈를 1부터 12까지 늘어났다가 다시 1로 돌아간다고 생각 할 수 있다. 

---

- 느낀점

시계열 데이터는 데이터 그 자체로 의미가 없는 것 같다. 그 수치가 시간에 따라 어떻게 변했는지 혹은 특정 시점에 비해 얼마나 변했는지를 시각적으로 잘 표현하는 것이 시계열 데이터를 분석하는 방법이다.

특히 같은 데이터를 사용하더라도 분석 방법에 따라 다른 목적에 사용될 수 있다는 것을 명심하고 항상 상황에 맞는 방법을 적용해야한다. 

마지막으로 시계열 데이터는 시간순으로 정렬되어있겠지만 항상 명시적으로 order by 절을 이용해 혹시나 모를 상황에 대비하자.