SQL BOOSTER] 4.3 WITH

📚 SQL BOOSTER (유일환, DBian)

📌오라클 → PostgreSql 15로 변환

 

CHAP4. 유용한 SQL 문법

 

4.3.1 WITH

인라인ㅡ뷰와 비슷하다.

SQL의 가장 윗부분에서 사용한다.

WITH 절에서 정의된 SQL 블록들은 같은 SQL 내에서 테이블처럼 사용할 수 있다.

-- ************************************************
-- PART I - 4.3.1 SQL1
-- ************************************************

-- 고객, 아이템유형별 주문금액 구하기 – 인라인-뷰 이용
select	T0.CUS_ID,
		T1.CUS_NM,
		T0.ITM_TP,
		(select	A.BAS_CD_NM
		 from	C_BAS_CD A
		 where 	A.LNG_CD = 'KO'
		 and	A.BAS_CD_DV = 'ITM_TP'
		 and	A.BAS_CD = T0.ITM_TP) as ITM_TP_NM,
		T0.ORD_AMT
from	(
			select	A.CUS_ID,
					C.ITM_TP,
					SUM(B.ORD_QTY * B.UNT_PRC) as ORD_AMT
			from	T_ORD A
			join	T_ORD_DET B on A.ORD_SEQ = B.ord_seq
			join	M_ITM C on B.ITM_ID = C.itm_id
			where	A.ORD_DT >= '2017-02-01'::DATE
			and 	A.ORD_DT < '2017-03-01'::DATE
			group by A.CUS_ID, C.ITM_TP
		) T0
join	M_CUS T1 on T1.CUS_ID = T0.cus_id
order by T0.CUS_ID, T0.ITM_TP;


-- ************************************************
-- PART I - 4.3.1 SQL2
-- ************************************************

-- 고객, 아이템유형별 주문금액 구하기 – WITH~AS 이용
-- 반복되는 인라인ㅡ뷰를 제거해 성능을 개선하거나, 가독성을 좋게 할 수 있음 (무조건 X)
with T_CUS_ITM_AMT as (
	select	A.CUS_ID,
			C.ITM_TP,
			SUM(B.ORD_QTY * B.UNT_PRC) as ORD_AMT
	from	T_ORD A
	join	T_ORD_DET B on A.ORD_SEQ = B.ORD_SEQ
	join 	M_ITM C on B.ITM_ID = C.itm_id
	where	A.ORD_DT >= '2017-02-01'::DATE
	and 	A.ORD_DT < '2017-03-01'::DATE
	group by A.CUS_ID, C.ITM_TP
)
select	T0.CUS_ID,
		T1.CUS_NM,
		T0.ITM_TP,
		(select	A.BAS_CD_NM
		 from	C_BAS_CD A
		 where	A.LNG_CD = 'KO'
		 and	A.BAS_CD_DV = 'ITM_TP'
		 and	A.BAS_CD = T0.ITM_TP) as ITM_TP_NM,
		T0.ORD_AMT
from	T_CUS_ITM_AMT T0
join	M_CUS T1 on T1.CUS_ID = T0.CUS_ID
order by T0.CUS_ID, T0.ITM_TP;


-- ************************************************
-- PART I - 4.3.1 SQL3
-- ************************************************

-- 고객, 아이템유형별 주문금액 구하기, 전체주문 대비 주문금액비율 추가 – WITH~AS 이용
-- 같은 테이블을 WITH 절마다 반복 사용하는 것은 주의
with T_CUS_ITM_AMT as (
	select	A.CUS_ID,
			C.ITM_TP,
			SUM(B.ORD_QTY * B.UNT_PRC) as ORD_AMT
	from	T_ORD A
	join	T_ORD_DET B on A.ORD_SEQ = B.ord_seq
	join	M_ITM C on B.ITM_ID = C.ITM_ID
	where	A.ORD_DT >= '2017-02-01'::DATE
	and 	A.ORD_DT < '2017-03-01'::DATE
	group by A.CUS_ID, C.ITM_TP
),
T_TTL_AMT as ( -- T_CUS_ITM_AMT 사용 가능
	select	SUM(ORD_AMT) as ORD_AMT
	from 	T_CUS_ITM_AMT
)
select	T0.CUS_ID,
		T1.CUS_NM,
		T0.ITM_TP,
		(select	A.BAS_CD_NM
		 from	C_BAS_CD A
		 where	A.LNG_CD = 'KO'
		 and	A.BAS_CD_DV = 'ITM_TP'
		 and	A.BAS_CD = T0.ITM_TP) as ITM_TP_NM,
		T0.ORD_AMT,
		(ROUND(T0.ORD_AMT / T2.ORD_AMT * 100, 2))::text || '%' as ORD_AMT_RT
from	T_CUS_ITM_AMT T0
join	M_CUS T1 on T1.CUS_ID = T0.CUS_ID
join	T_TTL_AMT T2 on true
order by ROUND(T0.ORD_AMT / T2.ORD_AMT * 100, 2) desc;

4.3.2 WITH 절을 사용한 INSERT

-- ************************************************
-- PART I - 4.3.2 SQL1
-- ************************************************

-- 주문금액 비율 컬럼 추가
alter table S_CUS_YM add ORD_AMT_RT NUMERIC(18,3);


-- ************************************************
-- PART I - 4.3.2 SQL2
-- ************************************************

-- WITH~AS 절을 사용한 INSERT문
insert into S_CUS_YM (BAS_YM, CUS_ID, ITM_TP, ORD_QTY, ORD_AMT, ORD_AMT_RT)
with T_CUS_ITM_AMT as (
	select	TO_CHAR(A.ORD_DT, 'YYYYMM') as BAS_YM,
			A.CUS_ID,
			C.ITM_TP,
			SUM(B.ORD_QTY) as ORD_QTY,
			SUM(B.ORD_QTY * B.UNT_PRC) as ORD_AMT
	from	T_ORD A
			join T_ORD_DET B on A.ORD_SEQ = B.ORD_SEQ
			join M_ITM C on B.ITM_ID = C.ITM_ID
	where	A.ORD_DT >= '2017-04-01'::DATE
	and		A.ORD_DT < '2017-05-01'::DATE
	group by TO_CHAR(A.ORD_DT, 'YYYYMM'), A.CUS_ID, C.ITM_TP
)
, T_TTL_AMT as (
	select	SUM(A.ORD_AMT) as ORD_AMT
	from	T_CUS_ITM_AMT A
)
select	T0.BAS_YM,
		T0.CUS_ID,
		T0.ITM_TP,
		T0.ORD_QTY,
		T0.ORD_AMT,
		ROUND(T0.ORD_AMT / T2.ORD_AMT * 100, 2) as ORD_AMT_RT
from	T_CUS_ITM_AMT T0
		cross join T_TTL_AMT T2
		join M_CUS T1 on T1.CUS_ID = T0.CUS_ID;