태터데스크 관리자

도움말
닫기
적용하기   첫페이지 만들기

태터데스크 메시지

저장하였습니다.

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

Posted at 2009.10.29 13:53 // in MSSQL/SQL Server 2005 new Feature // by ㏈ª ☞ β┖υΕJini.κR

공통 테이블 식(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
 
  1. Favicon of http://gdbt.tistory.com BlogIcon 건방진연이

    2009.10.30 15:50 신고 [수정/삭제] [답글]

    개인적으로 기대 만땅이에요 경진대리님 -0-;;;
    앞으로 항상 어려운것만 정리해주시길 바랄께요 *^^*;;

  2. Favicon of http://gdbt.tistory.com BlogIcon 건방진연이

    2009.11.06 14:44 신고 [수정/삭제] [답글]

    경진 대리님 저번에 발표하실때 약간 아리송 하셨서 제가 간단하게 댓글 남겨 둘께요.

    WITH CarPartsCTE(SubPart , Qty)
    AS
    (
    SELECT SubPart , Qty
    FROM CarParts
    WHERE Part = 'Body'

    UNION ALL

    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하고 아래 그냥 UNION ALL 하고 봐보세요 *^^*

    SELECT SubPart , SUM(Qty)
    FROM (
    SELECT SubPart, Qty FROM CarParts WHERE Part = 'Body'
    UNION ALL
    SELECT CarParts.SubPart
    , CarPartsCTE.Qty * CarParts.Qty
    FROM (
    SELECT SubPart, Qty FROM CarParts WHERE Part = 'Body'
    ) AS CarPartsCTE INNER JOIN CarParts
    ON CarPartsCTE.SubPart = CarParts.Part
    WHERE CarParts.CarID = 1
    ) AS A GROUP BY SubPart

    맨위 CTE Query나 밑에 InlineView Query나 같은 결과를 내고 있어요.

    이제 "아하~!" 되시죠 *^^*

  3. Favicon of http://gdbt.tistory.com BlogIcon 건방진연이

    2009.11.06 15:15 신고 [수정/삭제] [답글]

    한가지 좋은 예제가 있습니다.
    해당 예제는 책에서 본것인데 나름대로 CTE 개념을 머리속에 넣기 좋아요 *^^* 한번 해보세요

    예제 데이터베이스는 AdventureWorks 이고, 해당 Table은 Person.Address 을 사용하면 좋을 것 같아요.

    문제는 아래와 같아요

    "도시별 인구를 집계해서 상위 다섯 개의 도시의 평균은 얼마일까?" 일반적인 Transact-SQL 말고 CTE을 접목하여 작성해보세요.

    한가지 팁은 먼저 T-SQL로 만들어보고 CTE로 만들어 보면 쏙~~!! 들어올꺼에요 ~~ !!

댓글을 남겨주세요.