[STARTERS 4기 TIL] #47일차(23.04.12)

---

📚 오늘의  학습

1. SQL에서 복잡한 데이터셋을 다룰 경우

복잡한 데이터셋의 문제점
가독성↓  재사용성↓  유지보수성↓  실수↑

 

 

2. 임시테이블 temporary table

📌 임시 테이블 tmp_order_details 생성 📌

✅ 주문번호, 고객번호, 주문일, 연, 월, 일, 제품번호, 제품단가, 수량, 할인율, 매출액

create temporary table tmp_order_details as
select o.order_id , customer_id , order_date 
	 , to_char(order_date, 'YYYY') as year 
	 , to_char(order_date, 'mm') as month
	 , to_char(order_date, 'dd') as day
	 , to_char(order_date, 'q') as quarter
	 , od.product_id , od.unit_price , od.quantity , od.discount 
	 , od.unit_price * od.quantity * (1-od.discount) as amount
from orders o , order_details od 
where o.order_id = od.order_id ;

🖍 세션이 유지되는 동안 일반 테이블처럼 사용(alter, drop 가능)

📌 임시 테이블 tmp_order_details 이용 📌

✅ 월별 매출액, 주문건수 추출, 주문자 수, 건당 평균 주문액, 고객당 평균 주문액

select year , month , sum(amount) as 매출액, count(distinct order_id) as 주문건수
	 , count(distinct customer_id) as 주문자수
	 , sum(amount)/count(distinct order_id) as 건당평균주문액
	 , sum(amount)/count(distinct customer_id) as 고객당평균주문액
from tmp_order_details
group by 1, 2;

 

 

3. 공통 테이블 표현식 CTE(Common Table Expression)

📌 공통 테이블 cte_order_details 생성 📌

✅ 주문번호, 고객번호, 주문일, 연, 월, 일, 분기, 제품번호, 제품단가, 수량, 할인율, 매출액

with 
cte_order_details as(
select o.order_id , customer_id , order_date 
	 , to_char(order_date, 'YYYY') as year 
	 , to_char(order_date, 'mm') as month
	 , to_char(order_date, 'dd') as day
	 , to_char(order_date, 'q') as quarter
	 , od.product_id , od.unit_price , od.quantity , od.discount 
	 , od.unit_price * od.quantity * (1-od.discount) as amount
from orders o , order_details od 
where o.order_id = od.order_id
)
select * from cte_order_details;

📌 공통 테이블 cte_order_details을 이용하여 cte_order_info 테이블 생성 📌

with cte_order_details as (
select o.order_id , o.customer_id , o.order_date
	, to_char(o.order_date,'YYYY') as year
	, to_char(o.order_date,'q') as Quarter
	, to_char(o.order_date,'MM') as month
	, to_char(o.order_date,'dd') as day
	, od.product_id , od.unit_price , od.quantity , od.discount
	, od.unit_price*od.quantity*(1-od.discount) as total
from orders o , order_details od 
where o.order_id = od.order_id
)
-- 월별 매출액, 주문건수, 주문자수, 건당평균주문액, 고객당평균주문액
, cte_order_info as (
select year, month
	, sum(total) as 매출액
	, count(distinct order_id) as 주문건수
	, count(distinct customer_id) as 주문자수
	, sum(total)/count(distinct order_id) as 건당평균주문액
	, sum(total)/count(distinct customer_id) as 고객당평균주문액  
from cte_order_details
group by year, month
)
select sum(매출액) as 매출액
	 , sum(주문건수) as 주문건수
from cte_order_info;

 

 

4. 윈도우 함수