[SQL로 시작하는 데이터 분석] (3) - 시계열 분석 3

이전 글에서는 시계열 분석의 기초 개념과 함께, 날짜·시간 데이터 처리 및 주요 SQL 함수를 살펴보았다.

이번 글에서는 Census.gov에서 제공하는 미국 월간 소매업·외식업 매출 데이터를 활용하여,
실제로 트렌드 분석과 시계열 집계를 수행하는 방법을 예제와 함께 다뤄보겠다.

 

1. 간단한 트렌드 분석

1) 월별 매출 추세 확인하기

시계열 분석의 첫걸음은 시간 순으로 데이터를 나열하고 변화 패턴을 파악하는 것이다.

다음 예시는 ‘Retail and food services sales, total’(전체 소매업 및 외식업 매출) 데이터를 월별로 조회하는 쿼리이다.

 

SELECT sales_month,
       sales
FROM retail_sales
WHERE kind_of_business = 'Retail and food services sales, total'
ORDER BY sales_month;

💡 해석

• ORDER BY sales_month로 시간 순서대로 정렬
• 매출 변화의 계절성(holiday season, 여름 비수기 등)을 쉽게 파악 가능

2) 연도별 매출로 장기 트렌드 보기

월별 데이터는 세부 변동이 많아 큰 흐름을 보기 어렵다.
연도 단위로 집계하면 장기 추세가 훨씬 명확해진다.

SELECT DATE_PART('year', sales_month) AS sales_year,
       SUM(sales) AS total_sales
FROM retail_sales
WHERE kind_of_business = 'Retail and food services sales, total'
GROUP BY sales_year
ORDER BY sales_year;

💡 포인트

• DATE_PART('year', sales_month)으로 연도 추출
• SUM(sales)로 해당 연도 매출 합계 계산
• 경기침체, 팬데믹 등 거시경제 이벤트의 영향을 장기 추세에서 파악 가능

 

3) 트렌드 변화 해석하기

연도별 집계 결과를 그래프로 표현하면 다음과 같은 특징을 확인할 수 있다.

1. 1990년대~2007년: 꾸준한 우상향 성장세
2. 2008~2009년: 금융위기 여파로 매출 급락
3. 2010년 이후: 경기 회복과 함께 지속적인 성장
4. 2020년: 코로나19로 인한 성장 정체
   📌 Tip: 연도별 데이터와 월별 데이터를 함께 보면, 경기 충격이 언제부터 회복되었는지 더 명확히 알 수 있다.

 

4) 전체 대비 업종별 비율 분석 (확장 예시)

트렌드 분석을 한 단계 더 확장하면, 특정 업종 매출이 전체 매출에서 차지하는 비중을 계산할 수 있다.

SELECT sales_month,
       kind_of_business,
       sales,
       sales / SUM(sales) OVER (PARTITION BY sales_month) * 100 AS pct_of_total
FROM retail_sales
WHERE sales_month BETWEEN '2020-01-01' AND '2020-12-31';

💡 활용 예시

• 팬데믹 기간에 식료품점(Grocery stores) 매출 비중이 얼마나 증가했는지 확인 가능
• 소비 패턴 변화 분석 가능

 

2. 요소 비교 

위에서는 주로 시간에 따라 변하는 데이터를 확인했다. 근데,시계열 분석에서는 단순히 하나의 데이터만 보는 것이 아니라, 동일한 기간 동안 여러 요소의 트렌드를 함께 비교하는 경우가 많다. 아래는 여러 상점의 매출 데이터를 연도별로 비교하여 어떤 상점이 성장하고 있는지 파악하기 위한 쿼리들이다. 

 

1) kind_of_business 필드를 활용해 'Book stores', 'Hobby, toy, and game stores', 'Sporting goods stores'의 연간 매출을 집계

SELECT
    date_part('year', sales_month) as sales_year,
    kind_of_business,
    sum(sales) as sales
FROM  retail_sales
WHERE kind_of_business in ('Book stores', 'Hobby, toy, and game stores', 'Sporting goods stores')
GROUP BY  1, 2
ORDER BY  1, 2;

☞ 위에 쿼리를 통해 각 상점 유형별 연간매출 합산을 확인할 수 있다..!
( 나온 데이터로 그래프도 만들 수 있긴한데, 시간이 너무 오래걸려서 아래서 부터는 시각화한 그래프를 생략하고 쿼리 중심적으로 다뤄보갰다.)

 

2) 데이터 간 차이와 비율 계산 

단순히 그래프로 트렌드를 확인하는 것을 넘어, 두 데이터 간의 수치적인 차이나 비율을 계산하면 더욱 정밀한 분석도 가능하다. 여기서는 '여성 의류'와 '남성 의류'의 매출 데이터를 활용하여 분석해보겠다. 

 

(1) 두 매출 간의 차이 계산

두 업종의 연간 매출 차이를 계산하려면 CASE 문과 집계 함수를 활용하여 각 업종의 매출을 피벗(pivot)한 후, 뺄셈 연산을 수행하면 된다.

 

기본기 탄탄을 위한 추가)) CASE

기본문법 

CASE
    WHEN 조건1 THEN 결과1
    WHEN 조건2 THEN 결과2
    ...
    ELSE 기본값
END

• WHEN: 조건을 지정
• THEN: 조건이 참일 때 반환할 값
• ELSE: 모든 조건이 거짓일 때 반환할 값 (생략 가능)
• END: CASE 문 끝 표시

이제 아래는 두 매출 간 차이를 계산한 쿼리이다

SELECT
    sales_year,
    (womens_sales - mens_sales) AS womens_minus_mens
FROM
    (
        SELECT
            date_part('year', sales_month) AS sales_year,
            sum(CASE when kind_of_business = 'Women''s clothing stores' then sales end) AS womens_sales,
            sum(CASE when kind_of_business = 'Men''s clothing stores' then sales end) AS mens_sales
        FROM
            retail_sales
        WHERE
            kind_of_business IN ('Women''s clothing stores', 'Men''s clothing stores')
            AND sales_month <= '2019-12-01'
        GROUP BY 1
    ) a
ORDER BY 1;

 

 

 

(2) 비율 계산

두 매출 간의 비율을 계산하는 것은 상대적인 규모를 파악하는데 유용하다. 이번에는 남성 의류 매출 대비 여성 의류 매출의 비율 계산을 해보겠다.

SELECT
    sales_year,
    (womens_sales / mens_sales) AS womens_times_of_mens
FROM
    (
        SELECT
            date_part('year', sales_month) AS sales_year,
            sum(CASE when kind_of_business = 'Women''s clothing stores' then sales end) AS womens_sales,
            sum(CASE when kind_of_business = 'Men''s clothing stores' then sales end) AS mens_sales
        FROM
            retail_sales
        WHERE
            kind_of_business IN ('Women''s clothing stores', 'Men''s clothing stores')
            AND sales_month <= '2019-12-01'
        GROUP BY 1
    ) a
ORDER BY 1;

위 쿼리를 실행하면 결과로 각 연도별 여성 의류 매출이 남성의류 매출의 몇배인지 알 수 있다. 

쓴 함수는 간단한데,, 이게 쿼리가 길어지니까 맨 바탕에 처음부터 쓰려고하니까 어렵다.. 계속계속 눈에 익히고 손,머리로 쿼리를 써내는 과정의 연습이 많이 필요할 듯하다 (약간 영어로 문장 만드는 맥락이랑 비슷하게..)

 

 

---

책에는 예제 쿼리들이 훨씬 많은데 다 포스팅에 넣기에는 제한적이라 아래에 예시 쿼리는 어떤 내용을 담고 있었는지 적어두겠다! 
한번 연습해보도록하자...! 

 

1. 성 의류 매출에서 남성 의류 매출을 뺀 차이를 구하는 쿼리: sales_year와 (womens_sales - mens_sales)를 선택하여 각 연도별 여성 의류와 남성 의류 매출의 차이를 계산

2. 남성 의류 매출 대비 여성 의류 매출 비율을 계산하는 쿼리: (womens_sales / mens_sales)를 선택하여 각 연도별 남성 의류 매출에 대한 여성 의류 매출의 상대적 비율

3. 
남성 의류 매출 대비 여성 의류 매출 비율의 변화율을 계산하는 쿼리:
 ((womens_sales / mens_sales) - 1) * 100을 선택하여 남성 의류 매출을 기준으로 여성 의류 매출이 몇 퍼센트 변화했는지 계산