: 임시로 테이블을 불러와서 사용할 때 좋으며 데이터를 불러와 선작업을 할 때 이용할 수 있는 문법
--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