[sql] 월 전체 매출액 대비 비율

아래 4가지 테이블을 이용해서 월별 상품 카테고리별 구매건수와 매출을 구한뒤, 월별로 각 카테고리가 얼만큼의 매출 비중을 차지하는지 구해보려한다.
 
① nw.order: 주문정보 테이블
② nw.order_items: 주문상품 테이블
③ nw.products: 상품정보 테이블
④ nw.category: 상품카테고리정보 테이블
 
먼저 아래와 같이 각 테이블에서 정보를 가져울수 있도록 키값에 맞춰 테이블을 연결해주고,
 
① 주문정보 테이블에서 일자를, ④ 카테고리 정보 테이블에서 카테고리 명을, 그리고 ② 주문상품 테이블에서 order_id의 유일값 개수와 총 매출액을 구해주면 월별/카테고리별 주문건수와 매출액을 구할수 있다.
 
* 하나의 주문에서 여러 상품을 구입할수 있으므로, 주문번호 고윳값 갯수를 주문건수로 취급한다.

select to_char(o.order_date, 'yyyy-mm') date_month, c.category_name ,
	count(distinct oi.order_id) cnt_ord,
	sum(oi.amount) as sum_amount
from nw.orders o 
	left join nw.order_items oi on o.order_id = oi.order_id
	left join nw.products p on oi.product_id = p.product_id 
	left join nw.categories c on p.category_id = c.category_id
group by 1, 2
order by 1, 2;

 
이렇게 생성된 테이블을 이용해서 ① 월별 총 매출값과, ② 각 월별로 카테고리별 매출액 비중 칼럼을 추가하고자한다.
 
우선 위쿼리를 with 함수를 이용해 임시 테이블로 받은후, sum() over() 함수를 적절히 이용하면 위 조건을 구현할 수 있다.

with tmp as (
	select to_char(o.order_date, 'yyyy-mm') date_month, c.category_name ,
		count(distinct oi.order_id) cnt_ord,
		sum(oi.amount) as sum_amount
	from nw.orders o 
		left join nw.order_items oi on o.order_id = oi.order_id
		left join nw.products p on oi.product_id = p.product_id 
		left join nw.categories c on p.category_id = c.category_id
	group by 1, 2
	order by 1, 2
)

select *,
	sum(sum_amount) over(partition by date_month),
	round(sum_amount / sum(sum_amount) over(partition by date_month), 2)
from tmp;

 
sum(sum_amount) over(partion by date_month) 는 각 date_month(월) 내에서 sum_amount의 합계를 구하는 부분이다. 쿼리 그대로 해석하면 date_month(월) 를 기준으로 파티션하여 매출액을 산출하겠다는 의미이다. 결과는 아래와 같이 각 월별로 총 매출액을 확인할수 있는 칼럼이 출력된다.

 
이렇게 구한 월별 총 매출액을 각 행의 매출액에 나눠주면 최종적으로 월별 카테고리 매출 비중까지 구할수 있게된다. 최종 출력 테이블은 아래와 같다.