공통 테이블 식(Common Table Expressions) 과 재귀 쿼리

공통 테이블 식(CTE) 은 단순 쿼리로부터 파생된 임시 결과 집합입니다. CTE 는 파생된 테이블을 사용하는 것처럼, 동일한 수단으로 사용 되어질 수 있습니다. CTE는 또한 자신에 대한 참조를 포함할 수 있습니다. 이는 데이터베이스 개발자가 재귀 쿼리를 작성할 수 있도록 해줍니다.  CTE 는 또한 뷰 대신 사용 되어질 수도 있습니다. 개념적으로는 임시테이블과 비슷합니다.더 적은 코드로 임시테이블, 재귀호출등을 처리할 수 있습니다.

 기존 SQL 2000 에서 사용 하던 방식

select SalesPersonID, count(*) as NumSales into #tmp 
from Sales.SalesOrderHeader Group by SalesPersonID

select ts.SalesPersonID, sp.SalesYTD, ts.NumSales
from Sales.SalesPerson sp inner join #tmp ts 
on ts.SalesPersonID = sp.SalesPersonID
order by NumSales Desc

 SQL 2005의 CTE 를 이용한 방식

with TopSales
(SalesPersonID, NumSales) 
as 
( Select SalesPersonID, Count(*)
  From Sales.SalesOrderHeader Group BY SalesPersonID)

select ts.SalesPersonID, sp.SalesYTD, ts.NumSales
from Sales.SalesPerson sp inner join TopSales ts
on ts.SalesPersonID = sp.SalesPersonID
order by NumSales Desc

 

1. 파생된 테이블로 사용된 CTE 필터링 하기

뷰로 리턴된 결과집합을 필터링하는 것과 마찬가지로, 여러분은 공통 테이블 식(CTE) 안에 포함된 결과 집합을 필터링 할 수 있습니다.

WITH SalesCTE(ProductID , SalesOrderID)
AS 
(
	SELECT ProductID , COUNT(SalesOrderID)
	FROM Sales.SalesOrderDetail
	GROUP BY ProductID
)
SELECT * FROM SalesCTE WHERE SalesOrderID > 50 --50회이상주문된모든제품들

2. CTE 로 집계하기

공통 테이블 식(CTE) 으로 리턴된 데이터를 가지고 집계 또는 그 밖의 다른 계산을 수행하는 것은 기본 테이블로부터 리턴된 데이터를 가지고 집계 및 기타 계산을 수행하는 것과 유사합니다.

WITH SalesCTE(ProductID , SalesOrderID)
AS 
(
	SELECT ProductID , COUNT(SalesOrderID)
	FROM Sales.SalesOrderDetail
	GROUP BY ProductID
)
SELECT AVG(SalesOrderID) FROM SalesCTE WHERE SalesOrderID > 50
-- 50 회이상주문된모든제품들의평균주문횟수

3. 공통 테이블 식(CTE)을 사용하는 재귀 쿼리

공통 테이블 식(CTE)의 진정한 위력은 재귀 쿼리를 작성하는 데에 있습니다. 공통 테이블 식(CTE) 이 자기 자신에 대한 참조가 가능하기 때문에, 재귀 쿼리의 작성을 비교적 간단히, 수월하게 할 수 있습니다.

CREATE TABLE CarParts 
(
	CarID	int			NOT NULL
,	Part	varchar(15)
,	SubPart	varchar(15)
,	Qty		int
)
GO
INSERT CarParts VALUES(1 , 'Body' , 'Door' , 4)
INSERT CarParts VALUES(1 , 'Body' , 'Trunk Lid' , 1)
INSERT CarParts VALUES(1 , 'Body' , 'Car Hood' , 1)
INSERT CarParts VALUES(1 , 'Door' , 'Handle' , 1)
INSERT CarParts VALUES(1 , 'Door' , 'Lock' , 1)
INSERT CarParts VALUES(1 , 'Door' , 'Window' , 1)
INSERT CarParts VALUES(1 , 'Body' , 'Rivets' , 1000)
INSERT CarParts VALUES(1 , 'Door' , 'Rivets' , 100)
INSERT CarParts VALUES(1 , 'Door' , 'Mirror' , 1)

자동차의 부품 및 하위 부품에 대한 정보를 가지고 있는 테이블을 만들기 위한 쿼리 입니다. 보통 이러한 정보를 담고 있는 테이블을 재료표(bill of materials)라고 부릅니다. 재귀적 CTE 는 재료표 , 조직도 및 기타 계층적인 구조를 갖는 데이터를 반환하는 작업을 쉽게 할 수 있도록 합니다.
지금 생성하는 테이블 정보에서 Body 와 같은 부품이 여러 하위 부품들을 가지고 있다는 사실을 주목하십시오.. 예를 들어, Body 부품은 1000 개의 못(rivet) 을 하위 부품으로 가지고 있고,  Body 의 하위 부품인 Door  또한 자신(Door) 의 하위 부품으로 100개의 못(rivet)을 가지고 있습니다.

 

WITH CarPartsCTE(SubPart , Qty)
AS 
(
	-- 앵커멤버(Anchor Member): 
	-- CarPartsCTE 자신을참조하지않는SELECT 쿼리
	SELECT SubPart , Qty
	FROM CarParts
	WHERE Part = 'Body'
	UNION ALL
	-- 재귀멤버(Recursive Member):
	-- CTE(CarPartsCTE) 자기자신을참조하는SELECT 쿼리
	SELECT CarParts.SubPart
 , 		CarPartsCTE.Qty * CarParts.Qty
	FROM CarPartsCTE INNER JOIN CarParts
	ON CarPartsCTE.SubPart = CarParts.Part
	WHERE CarParts.CarID = 1
)
-- 출력쿼리
SELECT SubPart , SUM(Qty) as q FROM CarPartsCTE GROUP BY SubPart

재귀적 CTE 는 최소한 2개 이상의 쿼리들로 구성 되어집니다. 첫번째 요소로 구성된 쿼리는 CTE 자체를 참조하지 않는 쿼리입니다.  이러한 쿼리 정의는 CTE 구조의 기본 결과 집합을 형성하기 때문에 앵커 멤버(anchor member)라고 합니다. 두 번째 구성요소는 재귀 멤버(recursive member) 라고 불리워지는 재귀 쿼리입니다. 이러한 쿼리들은 UNION ALL 연산자에 의해 구별되어 집니다.
자동차의 본체(Body) 를  만들기 위해 필요한 부품들의 총 수량에 대한 리스트를 얻어오는 쿼리 입니다.  공통 테이블 식(CTE)은 맨 처음  간단한 쿼리(“SELECT SubPart, Qty FROM CarParts WHERE Part = 'Body'”.)로부터 기본 결과 집합을 만듭니다. 그 다음,  이 부품들의 하위 부품들과  이 하위 부품의 개수(필요한 상위 부품의 개수 *  상위 부품 단위당 필요한 하위 부품 개수) 에 대한 정보를 얻어오는 쿼리문을 UNION ALL 연산자로 결합합니다. 두 번째 쿼리문의 INNER JOIN 문장 안에 CarPartsCTE 라는 이름을 가진 공통 테이블 식(CTE) 자체에 대한 참조 (재귀 참조)  를 포함하는 것입니다.

재귀 쿼리를 이용한 1~ 10까지 쿼리

WITH TEST AS(
 SELECT IDX = 1 UNION ALL
 SELECT IDX = IDX + 1 FROM TEST WHERE IDX < 10
)SELECT * FROM TEST