[SQL 레시피] 다면적인 축을 사용해 데이터 집약하기

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

시계열로 매출 금액의 추이를 표현하는 것 이외에도, 상품 카테고리에 주목해서 매출 내역을 집계하거나 구성하는 비율을 집계하는 등 리포트 표현 방법은 다양하다.

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

리포트 업무는 전체적인 수치 개요를 전하면서, 해당 내역을 다양한 관점에서 설명해야한다.

• PC 사이트와 SP 사이트, 상품 카테고리별, 웹사이트의 총 페이지 뷰 수에 대한 회원들의 페이지 뷰와 비회원의 페이지 뷰 비율 등을 드릴다운하여 리포트함.

select coalesce(category, 'all') AS category
      , coalesce(sub_category, 'all') AS sub_category
      , sum(price) AS amount
from sqlrecipe.purchase_detail_log
group by rollup(category, sub_category)
limit 100;

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

ABC 분석은 재고 관리 등에서 사용하는 분석 방법. 매출 중요도에 따라 상품을 나누고, 그에 맞게 전략을 만들 때 사용한다.

[데이터를 작성하는 방법]

1. 매출이 높은 순서로 데이터를 정렬한다.
2. 매출 합계를 집계한다.
3. 매출 합계를 기반으로 각 데이터가 차지하는 비율을 계산하고, 구성비를 구한다.
4. 계산한 카테고리의 구성비를 기반으로 구성비누계를 구한다. (카테고리의 매출과 해당 시점까지의 누계를 따로 계산하고, 총 매출로 나누면 구성비누계를 구할 수 있다.)

• SQL이 아니라, 리포트를 만드는 쪽에서 등급을 나누어야한다.

with monthly_sales AS (
      select category
            , sum(price) AS amount
      from sqlrecipe.purchase_detail_log
      -- 대상 1개월 동안의 로그를 조건으로 걸기
      -- where dt between '2015-12-01' and '2015-12-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
limit 100;

<aside> 💡 구성비와 구성비누계 자체는 데이터 분석에서 굉장히 많이 사용된다.

</aside>

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

• 팬 차트 : 어떤 기준 시점을 100%로 두고, 이후의 숫자 변동을 확인할 수 있게 해주는 그래프
• 변화 비율이 그래프에 표시되므로, 성장과 쇠퇴를 쉽게 파악할 수 있다.

with
daily_category_amount AS (
      select dt
            , category
            , substr(dt,1,4) AS year
            , substr(dt,6,2) AS month
            , substr(dt,9,2) AS date
            , sum(price) AS amount
      from sqlrecipe.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
limit 100;

<aside> 💡 팬 차트를 만들 때 중요한 것은, 어떤 시점에서의 매출 금액을 기준점으로 채택할 것인지이다. 계절 변동이 적은 평균적인 달을 기준으로 선택하는 것이 좋고, 7~8월의 매출 변화를 확인한다면 명백한 목적이 있을 경우에는 직전인 6월을 기준점으로 선택하는 것이 좋다.

</aside>

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

가로 축에 단계(데이터의 범위), 세로 축에 도수(데이터의 개수)를 나타내는 그래프

• 히스토그램 만드는 방법
  1. 최댓값, 최솟값, 범위(최댓값-최솟값) 을 구한다.
  2. 범위를 기반으로 몇 개의 계급으로 나눌지 결정하고, 각 계급의 하한과 상한을 구한다.
  3. 각 계급에 들어가는 데이터 개수(도수)를 구하고, 이를 표로 정리한다.

  -- 데이터 계층 구하는 쿼리
  with
  stats as (
        select max(price)+1 as max_price
              , min(price) as min_price
              , max(price)+1 - min(price) as range_price
              , 10 as bucket_num
        from sqlrecipe.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(<정규화 금액>/<계층 범위>)
              , floor(1.0*(price - min_price) / (1.0*range_price/bucket_num)) + 1 as bucket
        from sqlrecipe.purchase_detail_log, stats
  )
  select *
  from purchase_log_with_bucket
  order by price;
  

  -- 히스토그램 구하는 쿼리
  with
  stats as (
        select max(price)+1 as max_price
              , min(price) as min_price
              , max(price)+1 - min(price) as range_price
              , 10 as bucket_num
        from sqlrecipe.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(<정규화 금액>/<계층 범위>)
              , floor(1.0*(price - min_price) / (1.0*range_price/bucket_num)) + 1 as bucket
        from sqlrecipe.purchase_detail_log, stats
  )
  select bucket
        , min_price + bucket_range * (bucket-1) AS lower_limit
        , min_price + bucket_range * bucket AS upper_limit
        , count(price) AS num_purchase
        , sum(price) AS total_amount
  from purchase_log_with_bucket
  group by bucket, min_price, bucket_range
  order by bucket;
  

  -- 상하한 임의 조정한 히스토그램 구하는 쿼리
  with
  stats as (
        select 200000 as max_price
              , 0    as min_price
              , 200000 as range_price
              , 10    as bucket_num
        from sqlrecipe.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(<정규화 금액>/<계층 범위>)
              , floor(1.0*(price - min_price) / (1.0*range_price/bucket_num)) + 1 as bucket
        from sqlrecipe.purchase_detail_log, stats
  )
  select bucket
        , min_price + bucket_range * (bucket-1) AS lower_limit
        , min_price + bucket_range * bucket AS upper_limit
        , count(price) AS num_purchase
        , sum(price) AS total_amount
  from purchase_log_with_bucket
  group by bucket, min_price, bucket_range
  order by bucket;