[7] 데이터 가공을 위한 SQL - 다면적인 축을 사용해 데이터 집약하기 : ABC분석으로 구성비 누계로 등급 매기기, 팬 차트로 성장/쇠퇴 판단하기, 도수분포표로 분포 확인/비교하기

---

목차

3. 데이터 가공을 위한 SQL

 3-1. 하나의 값 조작하기 (5강)
 3-2. 여러 개의 값에 대한 조작 (6강)
  3-3. 하나의 테이블에 대한 조작 (7강)
3-4. 여러 개의 테이블 조작하기 (8강)
4. 매출을 파악하기 위한 데이터 추출 (9강)

  4-1. 시계열 기반으로 데이터 집계하기 (10강)

4-2. 다면적인 축을 사용해 데이터 집약하기
5. 사용자를 파악하기 위한 데이터 추출
  5-1. 사용자 전체의 특징과 경향 찾기
  5-2. 시계열에 따른 사용자 전체의 상태 변화 찾기
  5-3. 시계열에 따른 사용자의 개별적인 행동 분석하기
6. 웹사이트에서 행동을 파악하는 데이터 추출하기
  6-1. 사이트 전체의 특징/경향 찾기
  6-2. 사이트 내의 사용자 행동 파악하기
  6-3. 입력 양식 최적화하기
7. 데이터 활용의 정밀도를 높이는 분석 기술
  7-1. 데이터를 조합해서 새로운 데이터 만들기
  7-2. 이상값 검출하기
  7-3. 데이터 중복 검출하기
  7-4. 여러 개의 데이터셋 비교하기
8. 데이터를 무기로 삼기 위한 분석 기술
  8-1. 검색 기능 평가하기
  8-2. 데이터 마이닝
  8-3. 추천
  8-4. 점수 계산하기
9. 지식을 행동으로 옮기기
  9-1. 데이터 활용의 현장

---

※ Mysql 기준

 

4장. 매출을 파악하기 위한 데이터 추출 (9강)

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

 

매출의 시계열 뿐만 아니라 상품의 카테고리, 가격 등을 조합해서 데이터의 특징을 추출해 리포팅하는 방법

 

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

Union All 구문, Roll Up 구문, 소계

table purchase_detail_log

카테고리 별 매출과 소계를 동시에 구하기

방법1. Union all 사용

1. 카테고리의 소계와 총계를 한 번에 출력
   • 소 카테고리의 매출 집계 
   • 대 카테고리의 매출 집계
   • 전체 매출 집계
2. 계층 별로 집계한 결과를 같은 컬럼이 되게 변환하고, 하나의 테이블로 합치기(union all)

with sub_cat_amt as (
    select 
        category,
        sub_category,
        sum(price) as amount
    from purchase_detail_log
    group by category, sub_category
),
cat_amt as (
    select 
        category,
        'all' as sub_category,
        sum(price) as amount
    from purchase_detail_log
    group by category
),
total_amt as (
    select 
        'all' as category,
        'all' as sub_category,
        sum(price) as amount
    from purchase_detail_log
)
select category, sub_category, amount from sub_cat_amt
union all 
select category, sub_category, amount from cat_amt
union all 
select category, sub_category, amount from total_amt;

원본 데이터가 조금 다른 듯 (데이터 부족해서 집계값 다름)

방법2. ROLLUP 사용 (With Rollup)

: 항목 별 합계(group by)에 대한 전체 합계를 구할 때 사용

 

<PostgreSQL> ROLLUP(컬럼1,컬럼2)

<My SQL> 컬럼1, 컬럼2 WITH ROLLUP

 

※ 소계를 계산할 때 레코드 집계 키(전체 합계 컬럼명)가 NULL이 되므로, COALESCE로 NULL을 ALL로 변환해주는 작업 필요

select
	coalesce(category, 'all') cat,
    coalesce(sub_category, 'all') sub_cat,
    sum(price) amount
from purchase_detail_log
group by category, sub_category with rollup;

- 대부분의 리포트 작성 도구에는 소계를 계산해주는 기능이 있기에, 최소 단위를 집계해두면 후에 계산 가능하다.

---

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

SUM() OVER~, 구성비 누계, ABC 분석

ABC 분석

1. 재고 관리에서 사용하는 분석 방법
2. 매출 중요도에 따라 상품을 나누고, 그에 맞게 전략을 만들 때 사용
3. 분석 목적에 따라 다르지만, 일반적으로는 다음과 같이 등급을 나눈다.
   • A 등급: 상위 0~70%
   • B 등급: 상위 70~90%
   • C 등급: 상위 90~100%

- 위 테이블을 작성하는 순서는

1. 매출이 높은 순서로 데이터를 정렬하고,
2. 매출 합계를 집계하고,
3. 매출 합계를 기반으로 각 데이터가 차지하는 비율을 계산하고, 구성비를 구함
4. 구성비를 기반으로 구성비 누계를 구함 (해당 시점까지의 누계를 총 매출로 나눈 값)
5. 등급은 분류 방법이 변경될 수 있기 때문에, 리포트를 만드는 쪽에서 나누는 것이 좋다.

같은 테이블 사용

purchase_detail_log

카테고리 별 매출 구성비 누계와 ABC 등급 계산하기

1. 월 별 항목 별 매출 계산하기 monthly_sales

with monthly_sales as(
	select category,
    sum(price) amount
    from purchase_detail_log
    group by category)
select * from monthly_sales;

monthly_sales

2. 구성비/ 구성비 누계 계산하기 sales_composition_ratio

with monthly_sales as(
	select category,
    sum(price) amount
    from purchase_detail_log
    group by category),
 /*2* 구성비 계산*/
    sales_composition_ratio as(
	select category,
    amount,
    amount / sum(amount) over() * 100 composition_ratio, --over 필수~
    sum(amount) over(
    order by amount desc rows between unbounded preceding and current row)
    / sum(amount) over() * 100 cumulative_ratio
    from monthly_sales)
select * from sales_composition_ratio;

(X) 구성비 구할 때 sum에 over 붙이지 않아서 결과가 다 안나왔었음

구성비, 구성비누계가 모두 나온 모습

3. 최종 쿼리, 구성비 누계 범위에 따라 순위 붙이기

with monthly_sales as(
	select customerNumber,
    sum(amount) amount
    from payments
    group by customerNumber),
sales_composition_ratio as(
	select customerNumber,
    amount,
    amount / sum(amount) * 100 composition_ratio,
    sum(amount) over(
    order by amount desc rows between unbounded preceding and current row)
    / sum(amount) over() * 100 cumulative_ratio
    from monthly_sales)
/*3. 순위 부여*/
    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;

- 여기에서는 분모가 무조건 0보다 큰 숫자이기에(전체 매출, 항복 별 누계 매출) 0으로 나누는 것을 따로 확인하지 않았지만, 구성비와 구성비누계를 구할 때에는 0으로 나누는 것 확인하기

---

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

first_value 윈도 함수, 팬 차트

팬차트

: 어떤 기준 시점을 100%로 두고, 이후 숫자 변동을 확인할 수 있게 해주는 그래프

• 변화를 백분율로 표시되기 때문에, 작은 변화도 쉽게 인지할 수 있다.

팬차트 예시 (기준 시점이 100%), 변화 비율이 표시되어 성장과 쇠퇴를 쉽게 파악할 수 있다.

날짜	카테고리	매출	Rate
2015.01	ladys_fashion	5,278,900	100.0%
2015.01	mens_fashion	3,490,120	100.0%
2015.02	ladys_fashion	8,096,980	153.4%

(▲) 팬 차트 작성 때 필요한 데이터 예시

위와 같은 데이터를 만드는 쿼리 작성

Steps

1. 날짜에서 연,월을 추출하고 연월 단위로 매출 구하기
2. 매출을 시계열 순서로 정렬하고, 기준이 되는 월 매출을 기준으로 비율 구하기
3. 여기에서는 기준이 되는 매출이 시계열로 정렬했을 때 가장 첫 월의 매출이기 때문에, first_value 사용
   • base_amount 컬럼에 First_value() 윈도 함수를 사용해 매출 입력
   • base_amount에 대한 비율을 rate 컬럼에 계산

※ 기존에 purchase_detail_log에는 한 일자의 데이터밖에 없어서, payments 테이블 사용

payments 테이블, customerNumber을 카테고리로 사용

1. 일 별 카테고리 별 매출액 집계

-- 일시 테이블 daily_cat_amt 생성
with daily_cat_amt as (
select dt,
	mid(dt, 1, 4) year,
    mid(dt, 6, 2) month,
    mid(dt, 9, 2) date,
    category,
    sum(price) amount
    from purchase_detail_log
    group by dt, category)
select * from daily_cat_amt;

일 별 카테고리 별 매출액

2. 월 별 카테고리 별 매출액 집계

WITH daily_cat_amt AS (
    SELECT 
        paymentDate,
        DATE_FORMAT(paymentDate, '%Y') AS year, --gpt한테 쿼리 syntax error 질문했는데 
        DATE_FORMAT(paymentDate, '%m') AS month, --가독성 좋게 mid대신 date_format쓰라고 추천해줌
        DATE_FORMAT(paymentDate, '%d') AS date,
        customerNumber AS category,
        SUM(amount) AS amount
    FROM payments
    GROUP BY paymentDate, category
),
/*2. 월 별로 집계*/
monthly_cat_amt AS (
    SELECT 
        CONCAT(year, '-', month) AS date,
        category,
        SUM(amount) AS amount
    FROM daily_cat_amt
    GROUP BY year, month, category
)
SELECT * FROM monthly_cat_amt;

 

월 별 카테고리 별 매출액

3. base_amount와 (amount / base_amount ) * 100 구하기

WITH daily_cat_amt AS (
    SELECT 
        paymentDate,
        DATE_FORMAT(paymentDate, '%Y') AS year,
        DATE_FORMAT(paymentDate, '%m') AS month,
        DATE_FORMAT(paymentDate, '%d') AS date,
        customerNumber AS category,
        SUM(amount) AS amount
    FROM payments
    GROUP BY paymentDate, category
),
monthly_cat_amt AS (
    SELECT 
        CONCAT(year, '-', month) AS date,
        category,
        SUM(amount) AS amount
    FROM daily_cat_amt
    GROUP BY year, month, category
)
/* 3. base_amount 계산 */
SELECT 
    date,
    category,
    amount,
    FIRST_VALUE(amount) OVER (
        PARTITION BY category
        ORDER BY date, category
        ROWS UNBOUNDED PRECEDING --없어도 됨(default)
    ) AS base_amount,
    100 * amount / FIRST_VALUE(amount) OVER (
        PARTITION BY category
        ORDER BY date, category
        ROWS UNBOUNDED PRECEDING --없어도 됨(default)
    ) AS rate
FROM monthly_cat_amt;

base_amount를 기준으로 얼마나 증가/감소했는지 쉽게 알 수 있다.

 

- 팬 차트에서는 base_amount를 어떤 시점에서의 매출 금액으로 채택하는지가 가장 중요하다.

- 이에 따라 성장/쇠퇴 경향에 대한 판단이 크게 달라지기 때문

- ex. 8월을 기준으로 매출이 늘어나는 상품이 있다면, 8월을 기준을 잡았을 경우 해당 시점 이후로는 계속 감소하는 그래프가 나올 것이고, 8월 이후 매출 증가여부를 제대로 판단할 수 없음

- 따라서 **계절 변동이 적인 평균적인 달을 기준으로 선택하기**

- 목적이 7-8월의 매출 변화라면, 직전인 6월을 기준점으로 선택해야 할 것

---

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

widget_bucket 함수, 도수분포표, 히스토그램

히스토그램을 만들기 위한 도수 분포표 작성

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

가격대 하한	가격대 상한	도수
0	5,000	52
5000	10,000	156

 

임의의 계층 수로 히스토그램 만들기

테이블 purchase_detail_log 사용

 

1. 일시테이블 stats 생성 (max, min, range, 계층 수)

도수 분포표 작성하기, max-min=range_price, bucket_name은 나눌 계층 수 (n등분)

WITH stats AS(
SELECT
	MAX(price) max_price,
    MIN(price) min_price,
    MAX(price) - MIN(price) range_price,
    10 bucket_num
FROM purchase_detail_log)
SELECT * FROM stats;
-- 가독성을 위해 이제 대문자로 써야짓

2. 데이터의 계층 구하기

diff, bucket_range, bucket 구하기

1. diff ( 정규화 금액 ) : 대상 금액 - 최소 금액
2. bucket_range ( 계층 범위 ) : 금액 범위를 계층 수로 나눈 것
3. bucket ( 계층 판정) : floor( <정규화 금액> / <계층 범위> )

<PostgreSQL> width_bucket 함수 사용 가능

WITH stats AS(
SELECT
	MAX(price) max_price,
    MIN(price) min_price,
    MAX(price) - MIN(price) range_price,
    10 bucket_num
FROM purchase_detail_log),
/* 2. 정규화 금액, bucket 나눌 기준 계산, 계층 생성*/
purchase_log_with_bucket AS(
	SELECT
    price,
    min_price,
    price - min_price AS diff, /*정규화 금액*/
    1.0 * range_price / bucket_num AS bucket_range, /*금액범위를 계층 수로 나눈 것, 1.0 곱해주기*/
    FLOOR( 1.0 * (price - min_price) / (1.0 * range_price / bucket_num ))+1 AS bucket /*계층(1~10) 판정하는 부분*/
FROM
	purchase_detail_log, stats)
SELECT * 
FROM purchase_log_with_bucket
ORDER BY price;

(price - min_price) 값인 diff가 bucket_range 범위인 0~9380미만인 데이터가 모두 1로 판정, 그 다음 9380이상~18760미만은 2로 판정

그다음 diff가 9380 이상 ~ 18760 미만은 2로 판정

 

위 결과의 경우 문제가 있음

계급 범위를 10으로 지정했고, 계급 판정 로직이 "최댓 값 미만"이라서 최댓값은 11으로 판정됨

 

이번에는 모든 레코드가 지정한 범위 내부에 들어갈 수 있게 쿼리 개선하기

stats 테이블을 정의할 때 계급 상한에 최댓값+1을 해서 모든 레코드가 계급 상한 미만이 되도록

계급 상한에 +1을 해서 bucket_range에 소수점이 들어가긴 했지만, 최댓값까지 제대로 1~10으로 구분됨

3. 구한 계층으로 도수 계산하기

bucket(1~10) 별로 lower, upper limit과 합계 금액 구하기

• lower_limit 계산 : 최솟값 + 버킷 범위 * 버킷 # - 1
• upper_limit 계산 : 최솟값 + 버킷 범위 * 버킷 
• num_purchase 도수 세기 
• 합계 금액 계산

WITH stats AS(
SELECT
	MAX(price) + 1 max_price,
    MIN(price) min_price,
    MAX(price) + 1 - MIN(price) range_price,
    10 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, /*금액범위를 계층 수로 나눈 것, 1.0 곱해주기*/
    FLOOR( 1.0 * (price - min_price) / (1.0 * range_price / bucket_num ))+1 AS bucket /*계층(1~10) 판정하는 부분*/
FROM
	purchase_detail_log, stats)
/* 3. bucket 별로 lower, upper 금액과 sum구하기*/
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, bucket_range
ORDER BY bucket;

 

※ 이 과정의 목적을 생각해보면, 너무 많은 데이터를 보여줄 때 눈에 들어오지 않기에, 전체적인 상황을 판단하기 쉽도록 위와 같이 구간을 나누어 bucket 별 lower, upper 값과 분포를 보여주는 1차 가공을 할 수 있음

 

히스토그램이 나누어진 경우

서로 다른 모집단에서 하나의 데이터를 도출한 경우, 여러 조건을 걸어 필터링해서 확인하기

이런 경우에는 따로 나누어서 보아야 왜곡이 없을 것

 

※ 매출의 상승/하락 원인을 조사할 때, '최근 매출'과 '과거 매출'을 기반으로 두 개의 히스토그램을 각각 작성하고, 어떤 차이가 있는지 보다 쉽게 확인할 수 있다. 특정 bucket에서만 하락했는지와 같은 정보를 보다 세세하게 확인할 수 있음

---