[SQL] Analytic SQL_집계 Analytic

Analytic SQL_집계 Analytic

집계(Aggregate) Analytic SQL

sum(), max(),min(),avg(),count() 와 같은 집계 함수를 window를 이용하여 로우 레벨로 자유 자재로 집계할 수 있는 기능 제공

OVER (

[ Partition 절 ] → 그룹화 컬럼명

[ Sorting 절 ] → 정렬 컬럼명(Window 이동 방향 기준 컬럼 명)

[ Window 절 ] → Window 범위(Rows, Range)

)

 

- SUM() Analytic SQL 활용

select order_id, line_prod_seq, product_id, amount
	, sum(amount) over (partition by order_id) as total_sum_by_ord 
	, sum(amount) over (partition by order_id order by line_prod_seq) as cum_sum_by_ord_01
	, sum(amount) over (partition by order_id order by line_prod_seq rows between unbounded preceding and current row) as cum_sum_by_ord_02
	, sum(amount) over ( ) as total_sum
from nw.order_items where order_id between 10248 and 10250;

total_sum_by_ord : order_id 별 amount 총합

cum_sum_by_ord_01 : order_id 별 line_prod_seq 누적 amount 총합

cum_sum_by_ord_02 : cum_sum_by_ord_01 와 동일

 

집계(Aggregate)계열 analytic함수는 order by 절이 있을 경우

window 절은 range between unbounded preceding and currenr row가 기본값

만약, order by 절이 없다면 window는 해당 partition의 모든 row 대상

만약, partition 절도 없다면 window는 전체 데이터의 row를 대상

 

- MAX() Analytic SQL 활용

order_id 별 상품 최대 구매 금액, order_id 별 상품 누적 최대 구매 금액

select order_id, line_prod_seq, product_id, amount
	, max(amount) over (partition by order_id) as max_by_ord 
	, max(amount) over (partition by order_id order by line_prod_seq) as cum_max_by_ord
from nw.order_items;

 

- MIN() Analytic SQL 활용

order_id 별 상품 최소 구매 금액, order_id 별 상품 누적 최소 구매 금액

select order_id, line_prod_seq, product_id, amount
	, min(amount) over (partition by order_id) as min_by_ord 
	, min(amount) over (partition by order_id order by line_prod_seq) as cum_min_by_ord
from nw.order_items;

 

- AVG() Analytic SQL 활용

order_id 별 상품 평균 구매 금액, order_id 별 상품 누적 평균 구매 금액

select order_id, line_prod_seq, product_id, amount
	, avg(amount) over (partition by order_id) as avg_by_ord 
	, avg(amount) over (partition by order_id order by line_prod_seq) as cum_avg_by_ord
from nw.order_items;

 

- Aggregate Analytic SQL 실습

상품별 매출합을 구하되, 상품 카테고리별 매출합의 5% 이상이고, 동일 카테고리에서 상위 3개 매출의 상품 정보 추출. 
1. 상품별 + 상품 카테고리별 총 매출 계산. (상품별 + 상품 카테고리별 총 매출은 결국 상품별 총 매출임)
2. 상품 카테고리별 총 매출 계산 및 동일 카테고리에서 상품별 랭킹 구함
3. 상품 카테고리 매출의 5% 이상인 상품 매출과 매출 기준 top 3 상품 추출. 

with
temp_01 as (
	select a.product_id, max(b.category_id) as category_id , sum(amount) sum_by_prod
	from  order_items a
		join products b 
			on a.product_id = b.product_id 
	group by  a.product_id
), 
temp_02 as (
select product_id, category_id, sum_by_prod
	, sum(sum_by_prod) over (partition by category_id) as sum_by_cat
	, row_number() over (partition by category_id order by sum_by_prod desc) as top_prod_ranking
from temp_01
)
select * from temp_02 where sum_by_prod >= 0.05 * sum_by_cat and top_prod_ranking <=3;