SQL(4) ➡️ with 절과 cte 개요

with 절과 cte 개요

가. with절
WITH절은 CTE를 표현하기 위한 구문으로 MySQL 8.0 이후부터 사용할 수 있다.
CTE는 기존 뷰, 파생 테이블, 임시 테이블 등으로 사용되던 것을 대신할 수 있으며, 더 간결한 식 으로 보여지는 장점이 있다.

💡 CTE(Common Table Expression)

• 비재귀적(Non-Recursive) CTE
• 재귀적(Recursive) CTE

나. 비재귀적 CTE
단순한 형태이며, 복잡한 쿼리 문장을 단순화 시켜준다.

WITH CTE_테이블명(열 이름)
AS
(
 쿼리문
)
SELECT 열 이름 FROM CTE_테이블명;

✅ 사용자별 총 구매액 (오름차순)

select userid as '사용자', sum(price*amount) as '총구매액'
from buyTBL
group by userid
order by 총구매액 desc;

✅ 뷰 생성하기

create view abc as
select userid, sum(price*amount) as total
from buyTBL
group by userid;

select * from abc order by total desc;

✅ 비재귀적 CTE를 사용하여 위의 코드 변경해보기 (order by 미사용)

-- CTE를 이용하여 아래의 코드처럼 사용하여 출력할 수 있다.
with abc(userid, total) as
(select userid, sum(price*amount)
from buyTBL
group by userid) -- 사용자별 총 구매액 
select * from abc order by total desc;

with maxAverage(addr, maxHeight) as (
select addr, max(height) from usertbl group by addr
) select avg(maxHeight) as '각 지역별 최고키의 평균' from maxAverage;
-- 테이블을 생성하지 않기 때문에 생성과 출력을 같이 진행해줘야 한다.

select addr, max(height)
from usertbl
group by addr
order by avg(height)

뷰는 계속 존재하여 다른 구문에서도 사용할 수 있지만, CTE와 파생 테이블은 구문이 끝나면 소멸된다.

다. 중복 CTE

WITH 
AAA(컬럼들)
AS ( AAA의 쿼리문 ),
 BBB (컬럼들)
 AS ( BBB의 쿼리문 ),
 CCC (컬럼들)
 AS ( CCC의 쿼리문)
SELECT * FROM [AAA 또는 BBB 또는 CCC]

CCC 쿼리문에서는 AAA나 BBB를 참조할 수 있지만,
AAA나 BBB 쿼리문에서는 CCC를 참조할 수 없 다. 아직 정의되지 않은 CTE를 미리 참조할 수 없다.

with cte_test as (
	with maxAverage(addr, maxHeight) as (
		select addr, max(height) from usertbl group by addr
	) select avg(maxHeight) as '각 지역별 최고키의 평균' from maxAverage
) select * from cte_test;

★VARCHAR(n) → 문자가 아닌 데이터를 받아야 하는 경우 사용한다.