[SQL] ABC 분석 차트를 작성하는 SQL쿼리(with. Google BigQuery)

- 책 '데이터 분석을 위한 SQL 레시피' 를 참고하였습니다.(10강)
- SQL 쿼리 작성은 Google BigQuery를 활용해 이루어 졌습니다.

 

 

1. ABC 분석이란?

 

* 값이 비싸고 소량이어서 입수하기 어려운 것을 A 재고품,

  값이 싸고 입수하기 쉬운 것을 C 재고품,

  중간 것을 B 재고품으로 분류하여 관리의 중점을 바꾸는 방법이다.

출처 :  ABC 분석 (naver.com)

분석 목적에 따라 등급은 조금씩 달라질 수 있지만, 일반적으로 상위 70%를 A 등급이라고 부른다.

 

 

 

 

2. ABC 차트 예시 

 

ABC분석을 위한 데이터 형식은 다음과 같다.

* 매출은 매출 합계를 집계한 후,  매출 합계가 높은 순서대로 정렬한다.

 

* 구성비는 전체 매출 합계에 대한 행 레이블 매출합계의 비율이며

  구성비누계는 구성비를 누적합계 한 것이다.

 

* 등급은 구성비누계가 70% 이하일 경우 A, 90% 이하일 경우 B, 나머지는 C로 나누었다.

 

 

 

다음 그래프는 위 피벗 테이블을 이용해 만든 ABC 차트이다.

 

 

 

 

 

 

3. SQL 쿼리를 이용한 ABC차트 작성

 

테이블명 : sql-study-project-376507.sql_study_bigquery.purchase_detail_log

위 테이블을 사용해 카테고리 별 매출합계를 이용한 ABC차트를 만들 것이다.

 

쿼리는 다음과 같다.

with
  monthly_sales as(
    select category,sum(price) as amount -- 카테고리별 매출 합계
      from sql-study-project-376507.sql_study_bigquery.purchase_detail_log
        group by category
  )
, sales_composition_ratio as(
  select category , amount ,
      amount/sum(amount) over() * 100 as composition_ratio, -- 전체 매출에 대한 카테고리 별 비율
      sum(amount) over(order by amount desc rows between unbounded preceding and current row) 
      / sum(amount) over() * 100 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;

 

 

카테고리 별 그룹화 한뒤 매출 합계를 구한 후,

구한 카테고리 별 매출 합계와 전체 매출 합계를 나누어 전제 매출에 대한 카테고리 별 비율을 구했다.

또한 윈도 함수를 이용해 이전 전체 행(UNBOUNDED PRECEDING) 부터 현재 행(CURRENT ROW) 까지의 매출 합계와 

전체 매출 합계를 나누어 비율누계를 구했다.

 

하지만 등급까지 SQL을 이용해 구하게 된다면, 등급 방법이 변경되었을 때 SQL을 수정해야 함으로

결과를 출력하는 쿼리 문에서 유연하게 변경할 수 있게끔 작성해 주는 것이 좋다.

 

 

 

끝.