IT/SQL

[데이터 분석을 위한 SQL 레시피] 4장 9강, 10강

728x90

5.16_4장 9강, 10강

📌 4장의 목표 : 웹사이트, 각종 서비스의 상태를 파악할 때 굉장히 중요한 매출 파악하기 위한 SQL을 알아보자.

9. 시계열 기반으로 데이터 집계하기

목표 : 웹사이트 또는 서비스의 상태를 파악할 때 굉장히 중요한 작업인 시계열 데이터를 알아보자!

💡 시계열 데이터란?

일정한 시간 간격으로 측정되었거나 특정 시간 간격으로 수집된 주기적인 시간 간격을 따른다.

즉, 시계열은 단순히 시간에 따라 정렬된 일련의 데이터 지점이며 시계열 분석은 이 데이터를 이해하는 프로세스이다.

💡 시계열 데이터의 장점

현재 데이터와 과거 데이터를 비교하고, 변칙 현상을 검색하고, 실시간 경고를 생성 가능
과거 추세를 시각화할 수 있음.
시계열 분석은 예측 모델링 및 결과 예측에도 매우 적합.
많은 예측 모델에 기록 변경 레코드를 적용할 수 있음.

9-0. 9장 개요 / 샘플 데이터 설명

● 실습 코드
  Postgresql과 DBeaver을 기준으로 사용함.
● 샘플 데이터
  2014년부터 2015년까지 2년에 걸친 매출 데이터인 purchase_log테이블을 생성하는 쿼리  

9강:시계열 기반으로 데이터 집계하기
    1. 날짜별 매출 집계하기
       코드 9-1
    2. 이동 평균을 사용한 날짜별 추이 보기
       코드 9-2
       원포인트
    3. 당월 매출 누계 구하기
       코드 9-3
       코드 9-4
       코드 9-5
       원포인트
    4. 월별 매출의 작대비 구하기
           코드 9-6
             원포인트
      5. Z 차트로 업적의 추이 확인하기

9-1. 날짜별 매출 집계하기

SQL GROUP BY구문, SUM함수, AVG 함수 / 분석 합계,평균

가로 축 : 날짜

세로 축 : 금액

코드 9-1 날짜별 매출과 평균 구매액을 집계하는 쿼리

SELECT dt
    , count(*) AS purchase_count
    , sum(purchase_amount) AS total_amount -- 일일 총 매출
    , avg(purchase_amount) AS avg_amount -- 전체 총 매출
FROM purchase_log
GROUP BY dt
ORDER BY dt;

9-2. 이동평균을 사용한 날짜별 추이 보기

9-3. 당월 매출 누계 구하기

9-4. 월별 매출의 작대비 구하기

일차 월차 연차 매출 추이를 리포트로 만들어서 확인 가능

💡 매출의 추이와 전년 대비 매출 성장을 한 눈에 파악하는데 용이하다.

코드 9-6| 월별 매출과 작대비를 계산하는 쿼리

WITH daily_purchase AS (
    SELECT 
          dt
        , substring(dt,1 ,4) AS YEAR
        , substring(dt, 6, 2) AS MONTH
        , substring(dt, 9, 2) AS date
        , SUM(purchase_amount) AS purchase_amount
        , COUNT(order_id) AS orders
    FROM purchase_log
    GROUP BY dt
)
SELECT
      month
    , SUM(CASE year WHEN '2014' THEN purchase_amount END) AS amount_2014
    , SUM(CASE year WHEN '2015' THEN purchase_amount END) AS amount_2015
    , 100.0
      * SUM(CASE year WHEN '2015' THEN purchase_amount END)
      / SUM(CASE year WHEN '2014' THEN purchase_amount END)
      AS rate
FROM
    daily_purchase
GROUP BY MONTH 
ORDER BY month
;

9-5. Z차트로 업적의 추이 확인하기

Z 차트

💡 월단위 매출, 매출 누계, 이동 합계 3개의 지표로 구성된 차트로 계절 변동의 영향을 배제하고 트렌드를 분석하는 방법

지표

월차매출 : 매출 합계를 월별로 집계
매출누계 : 해당 월의 매출에 이전월까지의 매출 누계를 합한 값
이동년계 : 해당 월의 매출에 과거 11개월의 매출을 합한 값

Z차트 읽는 법

매출누계 (월별 매출 확인 가능)
- 매출 일정 : 직선
- 매출 상승 : 오른쪽으로 갈수록 기울기가 급해지는 곡선
- 매출 하락 : 오른쪽으로 갈수록 기울기가 완만해지는 곡선
이동년계 (과거 1년 매출 확인 가능)
- 매출 일정 : 직선
- 매출 상승 : 오른쪽 위로 올라감
- 매출 하락 : 오른쪽 아래로 내려감

코드 9-7| 2015년 매출에 대한 Z 차트를 작성하는 쿼리

WITH
daily_purchase AS (
    SELECT 
          dt
        , substring(dt,1 ,4) AS year
        , substring(dt, 6, 2) AS month
        , substring(dt, 9, 2) AS date
        , SUM(purchase_amount) AS purchase_amount
        , COUNT(order_id) AS orders
    FROM purchase_log
    GROUP BY dt
-- 위에까지는 9-4 코드
)
, monthly_amount AS(
    -- 월별 매출 집계하기
    SELECT
          year
        , MONTH
        , sum(purchase_amount) AS amount
    FROM
        daily_purchase
    GROUP BY YEAR, MONTH
)
, calc_index AS (
    SELECT
          year
        , month
        , amount
        -- 2015년의 누계 매출 집계
        , SUM(CASE WHEN year='2015' THEN amount END)
              OVER(ORDER BY year, month ROWS UNBOUNDED PRECEDING)
            AS agg_amount
            -- 당월부터 11개월 이전까지의 매출 합계(이동년계) 집계
        , SUM(amount)
              OVER(ORDER BY year, month ROWS BETWEEN 11 PRECEDING AND CURRENT ROW)
            AS year_avg_amount
    FROM
      monthly_amount
    ORDER BY
      year, month
)
-- 마지막으로 2015년의 데이터만 압축하기
SELECT
  concat(year, '-', month) AS year_month
  , amount
  , agg_amount
  , year_avg_amount
FROM calc_index
WHERE year = '2015'
ORDER BY year_month
;

9-6. 매출을 파악할 때 중요 포인트

z 차트의 한계 & 활용

매출의 집계만으로는 본질적인 이유를 알 수 없음.
매출의 원인과 결과인 주변 데이터를 고려해야 원인을 알 수 있음.

코드 9-7| 매출과 관련된 지표를 집계하는 쿼리

WITH
daily_purchase AS (
    SELECT 
          dt
        , substring(dt,1 ,4) AS year
        , substring(dt, 6, 2) AS month
        , substring(dt, 9, 2) AS date
        , SUM(purchase_amount) AS purchase_amount
        , COUNT(order_id) AS orders
    FROM purchase_log
    GROUP BY dt
)
, monthly_purchase AS(
    SELECT
          year
        , MONTH
        , sum(purchase_amount) AS orders
        , avg(purchase_amount) AS avg_amount
        , sum(purchase_amount) AS monthly
    FROM daily_purchase
    GROUP BY YEAR, MONTH
)
SELECT
  concat(year, '-', month) AS year_month
  , orders
  , avg_amount
  , monthly
  , sum(monthly)
      over(PARTITION BY YEAR ORDER BY MONTH ROWS UNBOUNDED PRECEDING)
      AS agg_amount
      -- 12개월 전의 매출 구하기
      , LAG(monthly, 12)
          over(ORDER BY YEAR, month)
      AS last_year
      -- 12개월 전의 매출과 비교해서 비율 구하기
      , 100.0
      * monthly
      / LAG(monthly, 12)
          over(ORDER BY YEAR, MONTH)
      AS rate
  FROM MONTHly_purchase
  ORDER BY year_month
  ;

LAG 함수(윈도우 함수) : CASE 보다 일반적으로 사용

이전 12개월 전의 매출을 추출
12개월 전의 매출을 확인할 수 없다면 null 로 표시

💡 윈도우 함수를 작성할 수 있지만, SELECT를 여러번 사용하면 데이터를 여러 번 읽어 들이므로 성능이 떨어진다. 임시 테이블을 활용하는 것도 가독성을 높이는 좋은 방법

10. 다면적인 축을 사용해 데이터 집약하기

10-0. 10장 개요 / 샘플 데이터 설명

다면적인 축?

카테고리 별, 매출 순위 별, 가격대 별 집계를 통해 나열된 데이터에서 인사이트 추출하기!

10장 샘플 데이터

EC사이트(electronic commerce site, 즉 온라인 쇼핑몰)의 매출 로그
판매번호(order_id)마다 판매 품목 번호, 가격, 품목 카테고리 등의 정보 有

EC 사이트 DA: 매출 로그를 각 col 기준으로 groupby하여 매출에 유의한 영향을 가지는 요소 찾기

경로: PC인가 SP(모바일) 인가?
카테고리: 상품 카테고리는 어떻게 되는가?
사용자 정보: 회원인가 비회원인가?

10-1. 카테고리별 매출과 소계 계산하기

10-1, 쿼리 1 실행 결과. 대분류, 소분류에 따른 모든 매출 소계와 총계를 확인할 수 있다.

모든 대분류(category) 별 / 모든 소분류(sub_category)별 매출 합계(amount) 테이블 생성
모든 대분류(category) 별 / 전체(sub_category는 all로 고정) 매출 합계(amount) 테이블 생성
전체 매출 합계(category, sub_category 모두 all로 하여 합친 amount) 테이블 생성
세 테이블을 UNION ALL하여 최종 테이블 생성

WITH
--소 카테고리의 매출 집계하기
sub_category_amount AS(
SELECT 
  category AS category
, sub_category AS sub_category
, SUM(price) AS amount
FROM
  purchase_detail_log 
GROUP BY
  category, sub_category
) 
-- 대 카테고리의 매출 집계하기
,category_amount AS(
SELECT 
  category
, 'all' AS sub_category
,  sum(price) AS amount
FROM
  purchase_detail_log 
GROUP BY
  category
)
-- 전체 매출 집계하기
, total_amount AS (
SELECT 
  'all' AS category
, 'all' AS sub_category
, sum(price) AS amount
FROM
  purchase_detail_log 
)

SELECT category, sub_category, amount FROM sub_category_amount
UNION ALL SELECT category, sub_category, amount FROM category_amount
UNION ALL SELECT category, sub_category, amount FROM total_amount;

ROLLUP 활용하기

SELECT 
COALESCE(category, 'all') AS category
, COALESCE(sub_category,'all') AS sub_category
, sum(price) AS amount

FROM 
purchase_detail_log 
GROUP BY
rollup(category, sub_category);

ROLLUP(a, b): 계층적 집계

COALESCE(a, b): a에 null이 있다면 b로, 없다면 a 그대로 반환

-- 예시: COALESCE를 활용하지 않는 경우
SELECT 
category,
sub_category
, sum(price) AS amount

FROM 
purchase_detail_log 
GROUP BY
rollup(category, sub_category);

10-2. ABC 분석으로 잘 팔리는 상품 판별하기

ABC분석: 매출로 등급 나누기

매출로 내림차순
누적 합계로 비율, 구성비 계산
등급 구분: [ A: ~ 70% | B: 70~90% | C: 90% ~ ]

WITH 
monthly_sales AS (
SELECT 
category
-- 항목별 매출 계산하기
, SUM(price) AS amount
FROM 
purchase_detail_log
-- 대상 1개월 동안의 로그를 조건으로 걸기
WHERE 
dt BETWEEN '2017-01-01' AND '2017-01-31'
GROUP BY
category 
)
, sales_composition_ratio AS ( 
SELECT 
category
, amount

-- 구성비: 100.0*<항목별 매출> / <전체 매출>
, 100.0*amount / SUM(amount) OVER() AS composition_ratio

-- 구성비 누계: 100.0*<항목별 구계 매출> / <전체 매출>
, 100.0*sum(amount) OVER(ORDER BY amount DESC
ROWS BETWEEN UNBOUNDED PRECEDING AND current ROW)
/ sum(amount) over() AS cumulative_ratio
FROM 
monthly_sales
)

SELECT
*
-- 구성비누계 범위에 따라 순위 붙이기
, CASE
    WHEN cumulative_ratio BETWEEN 0 AND 70 THEN 'A'
    WHEN cumulative_ratio BETWEEN 70 AND 90 THEN 'B'
    WHEN cumulative_ratio BETWEEN 90 AND 100 THEN 'C'
END AS abc_rank
FROM 
sales_composition_ratio
ORDER BY
amount DESC
;

구계 매출(구간 누계 매출) 구하기: sum의 윈도우 함수 기능 활용

윈도우 함수란

AVG, COUNT, FIRST_VALUE, LAST_VALUE, MAX, MIN, STDDEV, SUM 등,UNBOUNDED PRECEDING : 윈도우의 시작 위치가 첫 ROW
참조: https://widecheon.tistory.com/379
특정 구간의 통계치를 구하는 함수 중 OVER() 를 활용하여 구간을 세부 설정하는 기능

10-3. 팬 차트로 상품의 매출 증가율 확인하기

팬 차트
- 어느 기준 시점을 100%로 두고, 이후의 숫자 변동을 확인할 수 있게 해주는 그래프.
- 변화가 백분율로 표시되므로, 작은 변화도 쉽게 인지하고 상황을 판단할 수 있다.
- 주의) 어떤 시점에서의 매출 금액을 기준으로 채택할 것인가에 따라 성장 경향인지 또는 쇠퇴 경향인지 판단이 크게 달라짐.

코드 10-4

WITH
daily_category_amount AS (
  SELECT
  dt
  , category
  , substring(dt, 1, 4) AS year
  , substring(dt, 6, 2) AS month
  , substring(dt, 9, 2) AS date
  , SUM(price) AS amount
FROM purchase_detail_log
GROUP BY dt, category
)
, monthly_category_amount AS (
  SELECT
    concat(year, '-', month) AS year_month
    , category
    , SUM(amount) AS amount
  FROM
    daily_category_amount
  GROUP BY
    year, month, category
)
SELECT
  year_month
  , category
  , amount
  , FIRST_VALUE(amount)
    OVER(PARTITION BY category ORDER BY year_month, category ROWS UNBOUNDED PRECEDING) AS base_amount
  , 100.0 * amount / FIRST_VALUE(amount)
      OVER(PARTITION BY category ORDER BY year_month, category ROWS UNBOUNDED PRECEDING) AS rate
FROM
  monthly_category_amount
ORDER BY
  year_month, category
;

결과

10-4. 히스토그램으로 구매 가격대 집계하기

히스토그램
- 상품의 가격에 주목해서 데이터 분포를 확인할 수 있음.
- 히스토그램을 사용하면 데이터가 어떻게 분산되어 있는지를 한 눈에 확인 가능.
- 데이터의 산에서 가장 높은 부분을 ‘최빈값’이라고 부른다.
히스토그램 만드는 방법
- 히스토그램을 만들기 위해서는 다음과 같은 도수분포표를 만들어야 한다.
  - 최댓값, 최소값, 범위(min ~ max)를 구하고
  - 범위를 기반으로 몇개의 계급으로 나눌지 결정하고, 각 계급의 하한과 상한을 구함
  - 각 계급에 들어가는 데이터 개수(도수)를 구하고, 표로 정리 후 이를 그래프로 그리면 히스토그램이 된다.

코드 10-5(최댓값, 최솟값, 범위를 구하는 쿼리)

WITH
stats AS (
  SELECT
    -- 금액의 최댓값
    MAX(price) AS max_price
    , MIN(price) AS min_price
    , MAX(price) - MIN(price) AS range_price
    , 10 AS bucket_num
  FROM
    purchase_detail_log
)
SELECT * FROM stats

코드 10-6(데이터의 계층을 구하는 쿼리)

WITH
stats AS (
  SELECT
    MAX(price) AS max_price
    , MIN(price) AS min_price
    , MAX(price) - MIN(price) AS range_price
    , 10 AS bucket_num
  FROM
    purchase_detail_log
)
, purchase_log_with_bucket AS (
  SELECT
    price
    , min_price
    , price - min_price AS diff
    , 1.0 * range_price / bucket_num AS bucket_range
    , FLOOR(
        1.0 * (price - min_price) / (1.0 * range_price / bucket_num)
    ) + 1 AS bucket
    , width_bucket(price, min_price, max_price, bucket_num) AS width_bucket
  FROM
    purchase_detail_log, stats
)
SELECT *
FROM purchase_log_with_bucket
order by diff
;