SQL 실습 - Agrregate_analytic

1. order_items 테이블에서 order_id별 line_prod_seq순으로 누적 amount 합 - partition 또는 order by 절이 없을 경우 windows.

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;

 

 

2. order_items 테이블에서 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;

 

 

3. 직원 정보 및 부서별로 직원 급여의 hiredate순으로 누적 급여합.

select empno, ename, deptno, sal, hiredate, sum(sal) over (partition by deptno order by hiredate) cum_sal from hr.emp;

 

 

4. 직원 정보 및 부서별 평균 급여와 개인 급여와의 차이 출력

select empno, ename, deptno, sal, avg(sal) over (partition by deptno) dept_avg_sal
	, sal - avg(sal) over (partition by deptno) dept_avg_sal_diff
from hr.emp;

with 
temp_01 as (
	select deptno, avg(sal) as dept_avg_sal 
	from hr.emp group by deptno
)
select a.empno, a.ename, a.deptno, b.dept_avg_sal,
	a.sal - b.dept_avg_sal as dept_avg_sal_diff
from hr.emp a 
	join temp_01 b
		on a.deptno = b.deptno
order by a.deptno

 

 

5. product_id 총 매출액을 구하고, 전체 매출 대비 개별 상품의 총 매출액 비율을 소수점2자리로 구한 뒤 매출액 비율 내림차순으로 정렬

with 
temp_01 as (
	select product_id, sum(amount) as sum_by_prod
	from order_items
	group by product_id
)
select product_id, sum_by_prod
	, sum(sum_by_prod) over () total_sum
	, round(1.0 * sum_by_prod/sum(sum_by_prod) over (), 2) as sum_ratio
from temp_01
order by 4 desc;

 

 

6. 직원별 개별 상품 매출액, 직원별 가장 높은 상품 매출액을 구하고, 직원별로 가장 높은 매출을 올리는 상품의 매출 금액 대비 개별 상품 매출 비율 구하기

with 
temp_01 as (
	select b.employee_id, a.product_id, sum(amount) as sum_by_emp_prod
	from order_items a
		join orders b on a.order_id = b.order_id
	group by b.employee_id, a.product_id
)
select employee_id, product_id, sum_by_emp_prod
	, max(sum_by_emp_prod) over (partition by employee_id) as sum_by_emp
	, sum_by_emp_prod/max(sum_by_emp_prod) over (partition by employee_id) as sum_ratio
from temp_01
order by 1, 5 desc;

 

 

7. 상품별 매출합을 구하되, 상품 카테고리별 매출합의 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;