: 임시로 테이블을 불러와서 사용할 때 좋으며 데이터를 불러와 선작업을 할 때 이용할 수 있는 문법

--WITH절 기본 형태
WITH '임시 테이블 이름' AS (SUBQUERY)

--WITH절 완료 및 메인쿼리 시작
SELECT
	*
FROM
	'임시 테이블 이름' ;

--EXAMPLE. 시험점수별로 등급을 80점 이상 1등급. 60점 이상 2등급, 60점 미만 불합격으로
--나누고 등급이 2등급 이상인 수험생들의 이름과 등급을 구하시오.

WITH 등급분류 AS (
	SELECT
		이름, 점수, 
		(CASE 
				WHEN POINT >= 80 THEN '1등급'
				WHEN POINT < 80 AND POINT >= 60 THEN '2등급'								
				ELSE '불합격'
				END) AS 등급
)
SELECT
	이름, 등급
FROM
	등급분류
WHERE
	등급 in ('1등급', '2등급') ;

: WITH절은 여러번 사용 가능, 여러 곳에서 데이터를 모아와 가공 후 마지막으로 종합하고 싶을 때

WITH절을 여러번 사용해주면 된다.

--WITH 중복 사용 기본 형태

WITH 임시 테이블명1 AS (SUBQUERY),
		 임시 테이블명2 AS (SUBQUERY)
SELECT
	*
FROM
	임시 테이블명1, 임시 테이블명2 ;

--EXAMPLE. 시험점수 80점 이상은 1등급, 60점 이상은 2등급, 60점 미만은 불합격으로 분류
--수험생들 정보에서 컴퓨터 공학과 학생들과 관련직 종사자들은 관련여부 컬럼 TRUE로 
--그 외에는 FALSE로 표기한 후에 수험생 이름, 점수, 등급, 관련여부를 구하시오.

WITH 등급분류 As ( 
	SELECT 
		이름, 점수, 
		(CASE 
				WHEN POINT >= POINT >=80 THEN '1등급'
				WHEN POINT < 80 AND POINT >= 60 THEN '2등급'								
				ELSE '불합격'
				END) AS 등급
)
, 관계 AS (
	SELECT
		이름, 
		(CASE 
			WHEN 직업 = '컴푸터 공학과 학생' OR 직업 = '관련직 종사자' THEN TRUE
			ELSE FALSE
			END) AS 관련여부
)
SELECT
	이름, 등급, 관련여부
FROM
	등급분류, 관계 ;

: WITH절을 이용하여 테이블을 연속적으로 편집할 수 있다.

WITH t1 AS (
	SELECT
		컬럼명1,
		컬럼명2,
		컬럼명3
	FROM
		테이블명1
	WHERE
		조건1
		AND 조건2
	),
t2 AS (
	SELECT
	새로운 컬럼명A,
	새로운 컬럼명B
	FROM
		t1
	WHERE
		조건A
		AND 조건B
	)
SELECT
	*
FROM
	t2

예시 문제


with t1 as(
	SELECT
		DATE_FORMAT(MIN(ro.delivery_datetime),'%Y-%m') as yearmonth,
		ro.sid_id,
		rs.name,
		DATE_FORMAT(rs.create_date, '%Y-%m') as createdate,
		(
		select
			DATE_FORMAT(MIN(ro2.delivery_datetime), '%Y-%m-%d')
		from
			rest_order as ro2 
		where
			ro2.sid_id = rs.id and ro2.is_balance =1 and ro2.status = 'done'
		) as 1st_od,
		(
		select
			DATE_FORMAT(MAX(ro3.delivery_datetime), '%Y-%m-%d')
		from
			rest_order as ro3 
		where
			ro3.sid_id = rs.id and ro3.is_balance =1 and ro3.status = 'done'
		) as last_od 	
	FROM 
		rest_store as rs 
	LEFT JOIN 
		rest_order as ro ON rs.id = ro.sid_id  
	WHERE
-- 		rs.create_date BETWEEN '2019-01-01' AND NOW() AND
		rs.is_test = 0 
		and ro.is_balance = 1 and ro.status = 'done' -- 해당 조건이 없을 시 yearmonth하고 1st_od랑 안맞는다. 취소 등의 주문도 포함.
	GROUP BY 
		ro.sid_id 
)
,
t2 as 
(
	SELECT 
		t1.*,
		Timestampdiff(MONTH, t1.1st_od, t1.last_od) as cohort_index  # 잔존개월
	FROM
		t1
)
SELECT
	t2.yearmonth,
	COUNT(IF(t2.1st_od > '2000-01-01', 1, NULL)) as cnt_order, 
	COUNT(IF(t2.1st_od < t2.last_od, 1, NULL)) as cnt_reorder,
	COUNT(IF(t2.cohort_index >= 1, 1, NULL)) as cnt_stay 
FROM
	t2 
GROUP BY 
	yearmonth
with t1 as(
	SELECT
		DATE_FORMAT(MIN(ro.delivery_datetime),'%Y-%m') as yearmonth,
		ro.sid_id,
		rs.name,
		DATE_FORMAT(rs.create_date, '%Y-%m') as createdate,
		(
		select
			DATE_FORMAT(MIN(ro2.delivery_datetime), '%Y-%m-%d')
		from
			rest_order as ro2 
		where
			ro2.sid_id = rs.id and ro2.is_balance =1 and ro2.status = 'done'
		) as 1st_od,
		(
		select
			DATE_FORMAT(MAX(ro3.delivery_datetime), '%Y-%m-%d')
		from
			rest_order as ro3 
		where
			ro3.sid_id = rs.id and ro3.is_balance =1 and ro3.status = 'done'
		) as last_od 	
	FROM 
		rest_store as rs 
	LEFT JOIN 
		rest_order as ro ON rs.id = ro.sid_id  
	WHERE
		rs.is_test = 0 
		and ro.is_balance = 1 and ro.status = 'done'
	GROUP BY 
		ro.sid_id 
)
,
t2 as 
(
	SELECT 
		t1.*,
		Timestampdiff(MONTH, t1.1st_od, t1.last_od) as cohort_index  
	FROM
		t1
)
SELECT
	t2.yearmonth,
	COUNT(IF(t2.1st_od > '2000-01-01', 1, NULL)) as cnt_order, 
	COUNT(IF(t2.1st_od < t2.last_od, 1, NULL)) as cnt_reorder,
	COUNT(IF(t2.cohort_index >= 1, 1, NULL)) as cnt_stay 
FROM
	t2 
GROUP BY 
	yearmonth