04-3 공통 테이블 식

 

공통 테이블 식은 주로 데이터베이스에 없는 테이블이 필요할 때 사용하며, 바로 다음에 실행할 SELECT 문에만 사용해야 한다는 특징이 있다. 공통 테이블 식은 일반 공통 테이블 식 과 재귀 공통 테이블식으로 나뉜다.

공통테이블 식 은 줄여서 CTE 라고 부른다.

 

일반 CTE

CTE 안에서 UNION문 , UNION ALL문 , INTERSECT문, EXCEPT문을 사용해 여러개의 일반 CTE 쿼리를 결합할 수 있다. 일반 CTE는 복잡한 쿼리를 단순하게 만들때 사용하기 좋다 .

CTE 기본형식 
WITH [CTE_테이블 이름] (열 이름1, 열이름2, ...)
AS ( <SELECT 문>) SELECT [열 이름] FROM [CTE_테이블 이름];

WITH문을 입력한 다음에 CTE_테이블 이름과 같이 사용할 테이블 이름을 지정하고 그 다음(열이름1,열이름2..)
와 같이 열 목록을 정의 한다. 

 

CTE 테이블을 참조해 데이터 검색

WITH cte_stock_price (date, symbol, price) 
AS
( SELECT date, symbol, [close] FROM stock
  WHERE date >= '2021-01-01' AND date <= '2021-01-10'
)
SELECT * FROM cte_stock_price WHERE symbol = 'MSFT'

 

---

UNION문과 UNION ALL 문으로 CTE 결합하기 (둘의 차이 : 중복을 제거한 행 포함 여부 )

-- UNION ALL 문을 CTE 결합
WITH cte_stock_price (date, symbol, price)
AS 
(
 SELECT date, symbol, [close] FROM stock
WHERE date >= '2021-01-01' AND date <= '2021-01-10'
UNION ALL
SELECT date, symbol, [close] FROM stock
WHERE date >= '2021-02-01' AND date <= '2021-02-07'
)
SELECT * FROM cte_stock_price WHERE symbol = 'MSFT'

 

중복을 제거한 결과를 보고 싶다면 UNION문을 사용한다. 하지만 중복 데이터를 제거하는 연산을 포함하는

UNION문 대신 UNION ALL문을 사용하는것이 좋다. UNION은 성능문제를 일으키기 쉬우므로

---

 

 

INTERSECT 문으로 CTE 결합하기

 

사용하는 방법은 내부조인과 비슷하지만, 내부 조인의 경우 테이블 사이의 조인 조건에 맞는 데이터를 반환하고

INTERSECT 문은 각 쿼리에서 반환한 결과에서 중복결과를 걸러 내 반환한다는 차이점이 있다.

-- CTE 에서 INTERSECT 문으로 중복 결과 검색
WITH cte_stock_price (date, symbol, price)
AS 
(
 SELECT date, symbol, [close] FROM stock
WHERE date >= '2021-01-01' AND date <= '2021-01-10'
INTERSECT
SELECT date, symbol, [close] FROM stock
WHERE date >= '2021-01-07' AND date <= '2021-01-20'
)
SELECT * FROM cte_stock_price WHERE symbol = 'MSFT'

 

즉, 1월 7일부터 1월 10일까지의 데이터가 중복하는 구간이 있으며 CTE는 이렇게 중복된 결과를 반환한다.

그리고 그 다음 SELECT 에서 CTE 결과 중에 symbol 열의 값이 'MSFT' 인 결과만 검색한다.

INTERSECT 문과 CTE를 사용하면 각 SELECT 문에서 지역 데이터를 검색한 다음 결과에서 중복된 데이터만

검색할 수 있다 .

---

EXCEPT 문으로 CTE 결합하기

NOT IN 문과 비슷하지만 , EXCEPT 문은 결괏값에서 중복을 제거한 유일한 행을 반환하고 NOT IN 문은 중복을 제거하지 않고 반환하는 점이 다르다. 또한 CTE 에서 먼저 작성한 쿼리 기준으로 , 그 다음 작성한 SELECT문 쿼리와 중복되지 않는 데이터를 반환한다.

CTE에서 EXCEPT문으로 중복 제거한 결과 검색
WITH cte_stock_price (date, symbol, price)
AS 
(
 SELECT date, symbol, [close] FROM stock
WHERE date >= '2021-01-01' AND date <= '2021-01-10'
EXCEPT
SELECT date, symbol, [close] FROM stock
WHERE date >= '2021-01-07' AND date <= '2021-01-20'
)
SELECT * FROM cte_stock_price WHERE symbol = 'MSFT'

-- CTE 내부의 SELECT 순서 변경 
WITH cte_stock_price (date, symbol, price)
AS 
(
 SELECT date, symbol, [close] FROM stock
WHERE date >= '2021-01-07' AND date <= '2021-01-20'
EXCEPT
SELECT date, symbol, [close] FROM stock
WHERE date >= '2021-01-01' AND date <= '2021-01-10'
)
SELECT * FROM cte_stock_price WHERE symbol = 'MSFT'

 

---

재귀 CTE

재귀 CTE는 CTE결과를 CTE 내부의 쿼리에서 재사용함으로써 반복 실행하는 쿼리 구조를 갖는다.

재귀 CTE는 주로 계층 데이터를 검색 할 때 많이 사용한다.

재귀 CTE 기본 형식
WITH [CTE_테이블 이름] (열 이름1, 열이름2, ...)
AS ( 
<SELECT * FROM 테이블 A> ----> 쿼리 1(앵커멤버)
UNION ALL
<SELECT * FROM 테이블 B JOIN CTE_테이블 이름> ---> 쿼리 2 (재귀멤버)
) 
SELECT * FROM [CTE_테이블 이름];

 

재귀 CTE는 적어도 2개의 CTE쿼리가 필요하다. 이때 각 쿼리는 앵커멤버와 재귀 멤버를 포함해야 한다.

위의 기본 형식이 쿼리 1이 앵커멤버 , 쿼리 2가 재귀 멤버 이다.

• 주의할점 ! ---> 자기 자신 CTE를 참조하지 않는 멤버를 의미 , 앵커멤버는 1번째 재귀멤버 앞에 있어야 한다는것, 재귀멤버의 열 자료형과 일치해야 한다는 것, 앵커멤버와 재귀멤버는 여러개 정의 할 수 있다.

 

재귀 CTE의 실행 순서
1. 최초에 쿼리 1을 실행한다. 이때 쿼리 2의 기본값은 0 으로 초기화 된다. 
2. 이어서 쿼리 2를 실행한다. 이때 쿼리 2의 기본값은 1 만큼 증가한다. 쿼리 1의 결과 행 수 만큼 쿼리 2
에서 CTE_테이블 이름을 재귀 호출하고, 쿼리 2의 기본값이 1씩 증가 하면서 쿼리 1의 결과 행 수 까지 
도달해 결과가 더 없다면 재귀 호출을 중단한다. 
3. 외부 SELECT 문에서 과정 1,2 를 통해 만든 CTE누적 결과를 검색한다. 

 

재귀 CTE 실습
생성한 doit_cte_recursive 테이블에서 EmployeeID, ManagerID 값을 확인해 
직원의 레벨(EmpLevel)을 구하는 쿼리

WITH 
cte_recursive (EmpID, FirstName, LastName, MgrID, EmpLevel)
AS
(
SELECT EmployeeID, FirstName, LastName, ManagerID, 1
FROM doit_cte_recursive WHERE ManagerID IS NULL

UNION ALL

SELECT e.EmployeeID, e.FirstName, e.LastName, e.ManagerID, r.EmpLevel + 1
FROM doit_cte_recursive AS e
  INNER JOIN cte_recursive AS r ON e.ManagerID = r.EmpID
)
SELECT
 FirstName + '' + LastName AS FullName, EmpLevel,
(SELECT FirstName + '' + LastName FROM doit_cte_recursive
WHERE EmployeeID = cte_recursive.MgrID) AS Manager
FROM cte_recursive
ORDER BY EmpLevel, MgrID

 

1번째 SELECT 문의 SELECT EmployeeID, FirstName, LastName, ManagerID, 1 을 보면 최상위 직원을 검색해 EmpLevel을 1로 지정한다. 2번째 SELECT 문은 1번째 SELECT문이 반환한 값을 doit_cte_recursive 테이블과 조인하면서 바로 앞 행의 EmpLevel에 1 을 더하며 재귀 호출한다. 이때 MangerID와 EmpID가 같은 값을 조인 조건으로 사용해 매니저가 같으면 같은 EmpLevel을 지정한다. 재귀 호출은 테이블 행 끝까지 진행한다.

마지막 SELECT 문은 cte_recursive 테이블을 검색한다. FullName과 EmpLevel 을 검색하고 각 행의 Manager 이름을 검색 하려고 스칼라 서브 쿼리를 사용했다.