[MSSQL][TSQL] CTE(Common Table Expression) 란? (계층쿼리, 재귀)

임시 테이블 처럼 사용하는 cte 성능에도 큰 도약이 됩니다.

단, 분산 cpu 성능을 많이 쓰는 경우도 있으니 분산 처리 환경에서 더 유용해요.

 

CTE(공통 테이블 식)는 SELECT, INSERT, UPDATE, DELETE 또는 CREATE VIEW 문 하나의 실행 범위 내에서 정의되는 임시 결과 집합이라고 볼 수 있습니다. CTE는 개체로 저장되지 않고 쿼리 지속 시간 동안만 존재한다는 점에서 파생 테이블과 비슷합니다. 그러나 CTE는 파생 테이블과 달리 자체 참조가 가능하며 동일 쿼리에서 여러 번 참조될 수 있습니다.

CTE를 사용하여 다음을 수행할 수 있습니다.

• 재귀 쿼리를 만들 수 있습니다. 자세한 내용은 공통 테이블 식을 사용하는 재귀 쿼리를 참조하십시오. (Oracle의 Start With, Connect by 와 유사하게 쓸수 있습니다. With 키워드를 같이 쓰네요)

• 일반적인 뷰 사용이 필요하지 않을 때, 즉 메타데이터에 정의를 저장할 필요가 없을 때 뷰를 대체할 수 있습니다.

• 스칼라 하위 SELECT에서 파생된 열 또는 비결정적이거나 외부 액세스가 없는 함수를 기준으로 그룹화할 수 있습니다.

• 동일 문에서 결과 테이블을 여러 번 참조할 수 있습니다.

 

https://docs.microsoft.com/ko-kr/previous-versions/sql/sql-server-2008-r2/ms190766(v=sql.105)

• 임시 테이블 생성과 유사하게 동작, 재귀적 쿼리 활용 가능
• 참조할 테이블 값 및 쿼리를 정의해서 하위 질의에서 재사용 가능
• 질의를 깔끔하게 정리 할 수 있음
  
  

1) 중복 cte 비재귀 사용예  

; – 이전 질의에 종료 표시 해주어야함

with

cte_1( 컬럼-생략가능 ) as ( cte_1 추출 쿼리 )

cte_2( 컬럼-생략가능 ) as ( cte_2 추출 쿼리 - cte_1 사용 가능 )

cte_3( 컬럼-생략가능 ) as ( cte_3 추출 쿼리 - cte_1, cte_2 사용 가능 )



select * from ( cte_1, cte_2, cte_3 사용 가능 )

; – cte 종료 표시 해주어야함

2) 재귀적 cte 사용 예

고정 값 조회 쿼리로 조회된 결과를 기준으로 재귀 호출 쿼리에 적용하고, 재귀 호출 쿼리에 결과가 없을 때까지 cte_1 생성 반복

→ 조직도 조회와 같은 트리 구조 정리에 활용

링크 이력 재귀적 호출에 적용 예정

;with cte_1(컬럼-생략가능)

as

(

( 고정 값 조회 쿼리 )

select * from tbl_1

union all ( union, except, intersects 등 )

( 재귀 호출 쿼리 )

select * from tbl_1 join cte_1

)



select * from cte_1;

인터넷 예제(http://egloos.zum.com/sweeper/v/3001079)

-- Name, Title, Manager, Dept, Grade 5개의 열을 가지는 CTE

WITH HierachyCTE (Name, Title, Manager, Dept, Grade)

AS

(

    -- 앵커 멤버 정의

    -- 그레이드 0을 시작으로 하여,

    SELECT Name, Title, Manager, Dept, 0 AS Grade

    FROM Employee

    WHERE Manager IS NULL  -- 사장은 매니져가 없당께

   
    UNION ALL

   
    -- 재귀 멤버 정의

    -- 그레이드를 1씩 증가시켜간다.

    SELECT AAA.Name, AAA.Title, AAA.Manager, AAA.Dept, BBB.Grade + 1

    FROM Employee AS AAA

    INNER JOIN HierachyCTE AS BBB

    ON AAA.Manager = BBB.Name    

)


-- 결과를 부서와 그레이드 순으로 올림차순 정렬해서 보기

SELECT REPLICATE('> ', Lvl) + Name, Title, Manager, Dept
FROM HierachyCTE ORDER BY Dept, Lvl

 

속성 값 열 문자 토크나이징

구분자 | 로 문자열 TOKENIZE 

MSSQL 2016 이상에서는 STRING_SPLIT 함수가 제공되지만 없는 경우 

아래 구문 함수로 쓰면 됨

재귀 호출로 문자열 토크나이징

DECLARE @ATTR VARCHAR(MAX)
SET @ATTR = '|1|32|10|32768|2|0|0|0|2|0|0|0|0|0|모래내로|0|NULL|1045810|1045811|'

;WITH DELIMITOR (ID,IDX) AS
(
SELECT 1,1
UNION ALL
SELECT ID+1,CONVERT(INT,CHARINDEX('|',@ATTR, IDX+1))
FROM DELIMITOR
WHERE CHARINDEX('|',@ATTR, IDX+1) > 0
)
SELECT A.*,AA.*,SUBSTRING(@ATTR,A.IDX+1,AA.IDX-A.IDX-1)
FROM DELIMITOR A CROSS APPLY (SELECT AA.IDX FROM DELIMITOR AA WHERE AA.ID = A.ID+1) AA