[sql] 테이블 관계를 고려한 aggregation

https://indistract.tistory.com/58

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

 

앞선 게시글에서는 월내 카테고리별 매출액 대비 비율을 구해봤다. 이번에는 비슷하게 카테고리내 상품별 매출액 대비 비율을 구하며, 추가적으로 각 카테고리내 상품의 매출액 순위가지 구해보려한다.

 

각 행의 순위를 구하려면 row_number() 함수를 사용해주면된다. 그리고 어떤 기준으로 partiton하고, 어떤 기준으로 순위를 매길것인지에 대한 정보는 over(partition by ① 구분 기준 order by ② 순위 기준) 으로 작성해주면 되겠다.

 

우리는 ①각 카테고리마다 ②각 상품의 매출 순위를 구하고싶으므로, row_number() over(partitionby  ① 카테고리명 order by ② 매출액 desc) 라고 작성해주면된다. 끝단에 desc를 쓴 이유는 매출액이 큰 순으로 정렬하여 순위를 부여할 것이기 때문이다.

 

이를 이용해서 작성한 쿼리는 아래와 같다. 위 게시글과 쿼리 구조는 비슷하며, row_number() 함수로 순위구하는 부분만 추가된 정도이다.

with tmp as(
	select c.category_name, p.product_name, 
		count(distinct oi.order_id) as ord_cnt,
		sum(amount) as sum_amount
	from nw.order_items oi
		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
)

select *,
	sum(sum_amount) over(partition by category_name) as ctg_tot_amount,
	round(sum_amount / sum(sum_amount) over(partition by category_name), 3) as ratio,
	row_number() over(partition by category_name order by sum_amount desc) as rank_amount
from tmp
order by category_name, sum_amount desc
;

 

위 쿼리는 테이블 관계를 고려한다면 조금 다르게 작성할수 있다. 카테고리 정보와 상품 정보를 얻기 위해서 각각 nw.categories 테이블과 nw,products  테이블을 사용했는데, 이 두 테이블은 1:M 관계를 가진다. 즉, 하나의 카테고리가 여러 개의 상품명에 연결될 수 있다. 예를들어, '전자제품' 이란 카테고리는 '컴퓨터', '태블릿', 그리고 '키보드' 라는 상품에 연결될수 있다. 반대로 하나의 상품은 하나의 카테고리만 연결되는 구조이다.

 

위 쿼리에서는 임시 테이블(tmp)를 생성할때 group by 기준을 c.category_name과 p.product_name 칼럼 2가지로 설정했지만, 이 테이블 관계를 이용한다면 p.product_name만 group by 기준으로 잡고 c.category_name은 집계하여 max(c.category_name)으로 집계하여도 무방하다. 하나의 상품은 하나의 카테고리만을 가지기 때문이다. 

 

이런 테이블 관계 특징을 고려해서 수정한 쿼리는 아래와 같다.

with tmp as(
	select  
		p.product_name,
		max(c.category_name) as category_name,
		count(distinct oi.order_id) as ord_cnt,
		sum(amount) as sum_amount
	from nw.order_items oi
		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
)

select *,
	sum(sum_amount) over(partition by category_name) as ctg_tot_amount,
	round(sum_amount / sum(sum_amount) over(partition by category_name), 3) as ratio,
	row_number() over(partition by category_name order by sum_amount desc) as rn
from tmp
order by category_name, sum_amount desc
;