[PostgreSQL] 다면적인 축을 사용해 데이터 집약하기

1. 카테고리별 매출과 소계를 동시에 출력

 

with sub_category_amount as(
	select category, 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_category_amount as(
	select 'all' as category, 'all' as sub_category, sum(price) as amount
        from purchase_detail_log)

select * from sub_category_amount
union all
select * from category_amount
union all
select * from total_category_amount
order by amount desc;

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)
    order by amount desc;

 

• 계층별로 집계한 결과를 같은 column으로 변환한 뒤, union all 구문으로 병합
• 하지만, 테이블을 여러 번 불러오고 속도도 느리기 때문에 비효율적임
• rollup 함수를 사용하면 효율적으로 추출 가능
• rollup(column 1, column 2) : 집계 함수 중 하나로, 지정된 열의 부분합 및 전체합을 생성
• rollup에서 소계를 계산할 때 레코드 값이 NULL이 되므로, coalesce 함수를 사용해 문자열 all로 변환

 

2. 매출 구성비 누계와 ABC 등급을 계산하는 쿼리

 

ABC 분석은 매출의 중요도에 따라 상품을 나누고, 그에 따라 전략을 만들 때 사용한다. 순서는 다음과 같다.

1. 매출이 높은 순서로 데이터를 정렬한다.
2. 매출 합계를 집계한다.
3. 매출 합계를 기반으로 각 데이터가 차지하는 비율을 계산하고, 구성비를 구한다.
4. 계산한 카테고리의 구성비를 기반으로 구성비 누계를 구한다.
   
   

with sum_price as(
	select category, sum(price) as amount
	from purchase_detail_log
-- 기간 조건이 필요한 경우, where절과 between 함수 사용
	group by category),
category_rate as(
	select category, amount, 
	100.0 * amount / sum(amount) over() as composition_rate
	from sum_price),
cul_rate as(
	select category, amount, composition_rate,
	sum(composition_rate) over(order by composition_rate desc rows between unbounded 
    preceding and current row) as cumulative_ratio
	from category_rate
)

select category, amount, composition_rate, cumulative_ratio, 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 cul_rate
	order by amount desc;

 

3. 팬 차트 작성에 필요한 데이터를 구하는 쿼리

 

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

 

with extract_dt as(
	select substring(dt,1,4) as year, substring(dt,6,2) as month, category, price
	from purchase_detail_log),
concat_table as(
	select concat(year,'-',month) as year_month, category, sum(price) as amount
	from extract_dt
	group by year_month, category)

select *, first_value(amount) over(partition by category rows unbounded preceding) as base_amount,
	100.0 * amount / first_value(amount) over(partition by category rows unbounded preceding) as rate
	from concat_table;

 

해당 쿼리에서는 기준 시점이 가장 첫 월의 매출이므로, first_value window function을 사용해 값을 산출했다.

 

4. 히스토그램

 

히스토그램 : 가로 축에 단계(데이터의 범위), 세로 축에 도수(데이터의 개수)를 나타내는 그래프이다. 데이터가 어떻게 분산되어 있는지를 한 눈에 확인할 수 있으며, 가장 높은 부분을 '최빈값' 이라고 부른다. 데이터의 분포에 따라 최빈값은 평균값과 비슷하지 않을 수도 있기 때문에, 히스토그램을 통해 데이터의 분포를 정확히 이해해야 한다. 히스토그램을 활용하여 매출 상승/하락의 원인을 파악할 수도 있다. 최근 매출과 과거 매출을 기반으로 2개의 히스토그램을 작성하고, 두 기간 사이의 어떤 부분에 차이가 있는지를 확인하면 된다.

히스토그램을 만들기 위해서는 다음과 같은 요소가 필요하다.

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

 

4-1. 최댓값, 최솟값, 범위를 구하는 쿼리

 

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;

 

4-2. 데이터의 계층을 구하는 쿼리

 

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,
	width_bucket(price, min_price, max_price, bucket_num),
	floor(1.0 * (price-min_price) / (1.0 * range_price / bucket_num)) + 1 as bucket
	from purchase_detail_log, stats)

select * from purchase_log_with_bucket
order by width_bucket;

 

• 계층 판정 : floor(<정규화 금액> / <계층 범위>) +1, 1을 더하지 않으면 0부터 집계되어서 실무에서 혼란이 발생할 수 있다.
• postgreSQL은 width_bucket(가격, 최소 가격, 최대 가격, 계층 수) 함수를 사용하면 간편하게 결과 출력가능
• 해당 쿼리는 최댓값을 기준으로 끝나기 때문에, 최댓값으로 인해 11개의 계층이 생기는 문제가 있음

 

4-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 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 bucket_num
	from stats, purchase_detail_log
)

select * from purchase_log_with_bucket
order by price;

 

4-4. 히스토그램 제작

 

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 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 bucket_num
	from stats, purchase_detail_log
)

-- 1번
select bucket, min_price, bucket_range, min_price + bucket_range*(bucket-1) as lower_limit,
    min_price + bucket_range * bucket as upper_limit,
    count(price), sum(price)
    from purchase_log_with_bucket
    group by bucket, min_price, bucket_range
    order by bucket;

-- 2번
select bucket, min(price), max(price), count(price), sum(price)
    from purchase_log_with_bucket
    group by bucket
    order by bucket;

 

• 차이점 : 1번 쿼리는 bucket마다 최소 가격에서 bucket_range를 더한 값이다. 즉 이전 bucket의 upper_limit과 다음 bucket의 lower_limit은 동일하다. 2번 쿼리는 bucket을 기준으로 그룹화 했으나, 범위가 아닌 해당 bucket 내부의 가격에서 최소, 최댓값을 추출하기 때문에 결과가 달라진다. 1번 쿼리를 사용해야 정확한 결과가 출력된다.

 

4-5. 히스토그램의 상한과 하한을 수동으로 조절하는 쿼리

 

with stats as(
	select 50001 as max_price, 0 as min_price, 50000 as range_price, 10 as bucket_num),
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)
	from 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), sum(price)
    from purchase_log_with_bucket
    group by bucket, min_price, bucket_range
    order by bucket;

 

• 공식적인 문서 혹은 상급자에게 보고할 때, 범위의 기준이 소수점이면 직감적이지 않을 수 있다.
• 따라서, 임의로 범위를 나눠서 깔끔하게 레포트를 정리할 수 있다.