[MSSQL] 재귀 공통 테이블 식 정의 및 사용 지침

원문 : http://msdn.microsoft.com/ko-kr/library/ms175972.aspx

CTE(공통 테이블 식)라고도 하는 임시로 이름이 지정된 결과 집합을 지정합니다. CTE는 단순 쿼리에서 파생되며 하나의 SELECT, INSERT, UPDATE, MERGE 또는 DELETE 문 실행 범위 내에서 정의됩니다. 이 절은 정의하는 SELECT 문의 일부로 CREATE VIEW 문 내에서도 사용할 수 있습니다. 공통 테이블 식은 자신에 대한 참조를 포함할 수 있으며 이를 재귀 공통 테이블 식이라 합니다.

Transact-SQL 구문 표기 규칙

구문

---

복사

[ WITH <common_table_expression> [ ,...n ] ]

<common_table_expression>::=
    expression_name [ (column_name [ ,...n ] ) ]
    AS
    (CTE_query_definition)

인수

---

expression_name

공통 테이블 식의 유효한 식별자입니다. expression_name은 같은 WITH <common_table_expression> 절에서 정의된 다른 공통 테이블 식의 이름과는 달라야 하지만 expression_name이 기본 테이블 또는 뷰의 이름과 같을 수 있습니다. 쿼리에서 expression_name에 대한 모든 참조는 기본 개체가 아니라 공통 테이블 식을 사용합니다.

column_name

공통 테이블 식에서 열 이름을 지정합니다. 단일 CTE 정의 내에서는 중복 이름이 허용되지 않습니다. 지정한 열 이름 수는 반드시 CTE_query_definition의 결과 집합에 있는 열 수와 일치해야 합니다. 열 이름 목록은 쿼리 정의에 모든 결과 열에 대한 고유한 이름을 제공한 경우에만 선택 사항입니다.

CTE_query_definition

공통 테이블 식을 채울 결과 집합을 위한 SELECT 문을 지정합니다. CTE_query_definition의 SELECT 문은 CTE가 또 다른 CTE를 정의하지는 못한다는 점을 제외하고는 뷰를 만들 때와 동일한 요구 사항을 만족해야 합니다. 자세한 내용은 주의 섹션 및 CREATE VIEW(Transact-SQL)를 참조하십시오.

CTE_query_definition을 두 개 이상 정의하는 경우 UNION ALL, UNION, EXCEPT 또는 INTERSECT 집합 연산자 중 하나로 쿼리 정의를 조인해야 합니다. 재귀 CTE 쿼리 정의를 사용하는 방법은 다음 "주의" 섹션과 공통 테이블 식을 사용하는 재귀 쿼리를 참조하십시오.

주의

---

공통 테이블 식 만들기 및 사용 지침

다음 지침은 비재귀 공통 테이블 식에 적용됩니다. 재귀 공통 테이블 식에 적용되는 지침은 다음에 나오는 "재귀 공통 테이블 식 정의 및 사용 지침"을 참조하십시오.

• CTE 뒤에는 일부 또는 모든 CTE 열을 참조하는 SELECT, INSERT, UPDATE, MERGE 또는 DELETE 문 하나가 있어야 합니다. 뷰의 SELECT 문 정의의 일부로 CREATE VIEW 문 내에 CTE를 지정할 수 있습니다.

• 비재귀 CTE 내에 여러 개의 CTE 쿼리 정의를 정의할 수 있습니다. UNION ALL, UNION, INTERSECT 또는 EXCEPT 집합 연산자 중 하나를 사용해 이 같은 정의를 결합해야 합니다.

• CTE는 같은 WITH 절에서 자신 및 이전에 정의한 CTE를 참조할 수 있지만 전방 참조는 허용되지 않습니다.

• CTE에 둘 이상의 WITH 절을 지정할 수 없습니다. 예를 들어 CTE_query_definition이 하위 쿼리를 포함하는 경우 그 하위 쿼리가 또 다른 CTE를 정의하는 중첩 WITH 절을 포함할 수 없습니다.

• CTE_query_definition에는 다음 절을 사용할 수 없습니다.

  • COMPUTE 또는 COMPUTE BY

  • ORDER BY(TOP 절을 지정하는 경우는 제외)

  • INTO

  • 쿼리 힌트가 있는 OPTION 절

  • FOR XML

  • FOR BROWSE

• 일괄 처리에 속한 문에 CTE를 사용할 때는 그 전의 문 다음에 반드시 세미콜론을 추가해야 합니다.

• CTE를 참조하는 쿼리를 사용하여 커서를 정의할 수 있습니다.

• CTE에서 원격 서버 상의 테이블을 참조할 수 있습니다.

• CTE를 실행할 때는 쿼리의 뷰를 참조하는 힌트와 마찬가지로 CTE를 참조하는 힌트가 CTE가 기본 테이블에 액세스할 때 발견되는 다른 힌트와 충돌을 일으킬 수 있습니다. 이러한 경우 쿼리에서 오류를 반환합니다. 자세한 내용은 뷰 확인을 참조하십시오.

• UPDATE 문의 대상이 CTE인 경우 문에서 CTE에 대한 모든 참조가 일치해야 합니다. 예를 들어 FROM 절에서 CTE에 별칭이 지정된 경우 CTE에 대한 다른 모든 참조에서 이 별칭을 사용해야 합니다. CTE 참조가 모호하면 예기치 않은 조인 동작과 의도하지 않은 쿼리 결과가 발생할 수 있습니다. 자세한 내용은 UPDATE(Transact-SQL)를 참조하십시오.

재귀 공통 테이블 식 정의 및 사용 지침

다음 지침은 재귀 공통 테이블 식 정의 작업에 적용됩니다.

• 재귀 CTE 정의는 적어도 하나의 앵커 멤버와 하나의 재귀 멤버로 두 개의 CTE 쿼리 정의를 포함해야 합니다. 앵커 멤버와 재귀 멤버를 여러 개 정의할 수 있지만 앵커 멤버 쿼리 정의는 모두 첫 번째 재귀 멤버 정의 앞에 와야 합니다. 모든 CTE 쿼리 정의는CTE 자체를 참조하지 않는 한 앵커 멤버입니다.

• 앵커 멤버는 UNION ALL, UNION, INTERSECT 또는 EXCEPT 집합 연산자 중 하나를 사용해 결합해야 합니다. UNION ALL은 여러 재귀 멤버를 결합할 때 마지막 앵커 멤버와 첫 번째 재귀 멤버 사이에서 허용되는 유일한 집합 연산자입니다.

• 앵커 멤버 및 재귀 멤버에 있는 열의 수는 같아야 합니다.

• 재귀 멤버에 있는 열의 데이터 형식은 앵커 멤버에 있는 해당 열의 데이터 형식과 반드시 같아야 합니다.

• 재귀 멤버의 FROM 절은 CTE expression_name을 한 번만 참조해야 합니다.

• 다음 항목은 재귀 멤버의 CTE_query_definition에서 허용되지 않습니다.

  • SELECT DISTINCT

  • GROUP BY

  • HAVING

  • 스칼라 집계

  • TOP

  • LEFT, RIGHT, OUTER JOIN(INNER JOIN이 허용됨)

  • 하위 쿼리

  • CTE_query_definition 내부의 CTE에 대한 재귀적 참조에 적용되는 힌트

다음 지침은 재귀 공통 테이블 식 사용 작업에 적용됩니다.

• 재귀 CTE가 반환하는 모든 열은 참가하는 SELECT 문이 반환하는 열의 Null 허용 여부와는 상관없이 Null을 허용합니다.

• 잘못 구성된 재귀 CTE로 인해 무한 루프가 발생할 수 있습니다. 예를 들어 재귀 멤버 쿼리 정의가 부모 열과 자식 열에 대해 모두 같은 값을 반환하면 무한 루프가 생성된 것입니다. 무한 루프를 막기 위해서는 INSERT, UPDATE, MERGE, DELETE 또는 SELECT 문의 OPTION 절에서 MAXRECURSION 힌트와 0부터 32,767 사이의 값을 사용하여 특정 문에 허용되는 재귀 수준을 제한할 수 있습니다. 이 방법으로 루프를 발생시키는 코드 문제를 해결할 때까지 문의 실행을 제어할 수 있습니다. 서버 차원의 기본값은 100입니다. 0을 지정하면 제한이 적용되지 않습니다. 하나의 문에는 하나의 MAXRECURSION 값만 지정할 수 있습니다. 자세한 내용은 쿼리 힌트(Transact-SQL)를 참조하십시오.

• 재귀 공통 테이블 식을 포함한 뷰를 사용하여 데이터를 업데이트할 수 없습니다.

• CTE를 사용하여 쿼리에 커서를 정의할 수 있습니다. CTE는 커서의 결과 집합을 정의하는 select_statement 인수입니다. 재귀 CTE에는 빠른 정방향 전용 커서 및 정적(스냅숏) 커서만 사용할 수 있습니다. 재귀 CTE에 또 다른 커서 유형을 지정하는 경우 해당 커서 유형이 정적으로 변환됩니다.

• CTE에서 원격 서버 상의 테이블을 참조할 수 있습니다. CTE의 재귀 멤버에서 원격 서버를 참조하는 경우 로컬에서 반복적으로 테이블에 액세스할 수 있도록 각 원격 테이블을 위한 스풀이 생성됩니다. CTE 쿼리인 경우 쿼리 계획에 Index Spool/Lazy Spool이 표시되며 이 스풀은 추가 WITH STACK 조건자를 가집니다. 이는 재귀를 올바르게 수행하는 한 가지 방법입니다.

• CTE의 재귀 부분에 있는 분석 및 집계 함수는 현재 재귀 수준에 대한 집합에만 적용되며 CTE에 대한 집합에는 적용되지 않습니다. ROW_NUMBER와 같은 함수는 CTE의 재귀 부분에 전달된 전체 데이터 집합이 아니라 현재 재귀 수준에 의해 함수로 전달된 데이터 하위 집합에 대해서만 실행됩니다. 자세한 내용은 K. Using analytical functions in a recursive CTE을 참조하십시오.

예

---

1. 간단한 공통 테이블 식 만들기

다음 예에서는 Adventure Works Cycles의 각 관리자에게 직접 보고하는 직원 수를 보여 줍니다.

복사

USE AdventureWorks2008R2;
GO

WITH Sales_CTE (SalesPersonID, NumberOfOrders)
AS
(
    SELECT SalesPersonID, COUNT(*)
    FROM Sales.SalesOrderHeader
    WHERE SalesPersonID IS NOT NULL
    GROUP BY SalesPersonID
)

SELECT SalesPersonID, NumberOfOrders 
FROM Sales_CTE 
ORDER BY SalesPersonID;
GO

2. 공통 테이블 식을 사용한 수 제한 및 평균 보고

다음 예에서는 관리자에게 보고하는 평균 직원 수를 보여 줍니다.

복사

WITH Sales_CTE (SalesPersonID, NumberOfOrders)
AS
(
    SELECT SalesPersonID, COUNT(*)
    FROM Sales.SalesOrderHeader
    WHERE SalesPersonID IS NOT NULL
    GROUP BY SalesPersonID
)
SELECT AVG(NumberOfOrders) AS "Average Sales Per Person"
FROM Sales_CTE;
GO

3. 공통 테이블 식을 두 번 이상 참조

다음 예에서는 SalesOrderHeader 테이블에서 각 판매 직원의 총 판매 주문 수와 가장 최근의 판매 주문 날짜를 보여 줍니다. 실행되는 문에서 CTE는 판매 직원에 대해 선택된 열을 반환하기 위해 한 번 그리고 판매 직원의 관리자에 대해 비슷한 세부 정보를 검색하기 위해 다시 한 번, 총 두 번 참조됩니다. 판매 직원 및 관리자에 대한 데이터는 모두 단일 행으로 반환됩니다.

복사

USE AdventureWorks2008R2;
GO
-- Define the CTE expression name and column list.
WITH Sales_CTE (SalesPersonID, SalesOrderID, SalesYear)
AS
-- Define the CTE query.
(
    SELECT SalesPersonID, SalesOrderID, YEAR(OrderDate) AS SalesYear
    FROM Sales.SalesOrderHeader
    WHERE SalesPersonID IS NOT NULL
)
-- Define the outer query referencing the CTE name.
SELECT SalesPersonID, COUNT(SalesOrderID) AS TotalSales, SalesYear
FROM Sales_CTE
GROUP BY SalesYear, SalesPersonID
ORDER BY SalesPersonID, SalesYear;
GO

4. 재귀 공통 테이블 식을 사용하여 여러 수준의 재귀 표시

다음 예에서는 관리자의 계층적 목록 및 이들에게 보고하는 직원을 보여 줍니다.

복사

USE AdventureWorks2008R2;
GO
WITH DirectReports(ManagerID, EmployeeID, Title, EmployeeLevel) AS 
(
    SELECT ManagerID, EmployeeID, Title, 0 AS EmployeeLevel
    FROM dbo.MyEmployees 
    WHERE ManagerID IS NULL
    UNION ALL
    SELECT e.ManagerID, e.EmployeeID, e.Title, EmployeeLevel + 1
    FROM dbo.MyEmployees AS e
        INNER JOIN DirectReports AS d
        ON e.ManagerID = d.EmployeeID 
)
SELECT ManagerID, EmployeeID, Title, EmployeeLevel 
FROM DirectReports
ORDER BY ManagerID;
GO

5. 재귀 공통 테이블 식을 사용하여 두 가지 수준의 재귀 표시

다음 예에서는 관리자와 그들에게 보고하는 직원을 보여 줍니다. 반환되는 수준은 2로 제한됩니다.

복사

USE AdventureWorks2008R2;
GO
WITH DirectReports(ManagerID, EmployeeID, Title, EmployeeLevel) AS 
(
    SELECT ManagerID, EmployeeID, Title, 0 AS EmployeeLevel
    FROM dbo.MyEmployees 
    WHERE ManagerID IS NULL
    UNION ALL
    SELECT e.ManagerID, e.EmployeeID, e.Title, EmployeeLevel + 1
    FROM dbo.MyEmployees AS e
        INNER JOIN DirectReports AS d
        ON e.ManagerID = d.EmployeeID 
)
SELECT ManagerID, EmployeeID, Title, EmployeeLevel 
FROM DirectReports
WHERE EmployeeLevel <= 2 ;
GO

6. 재귀 공통 테이블 식을 사용하여 계층적 목록 표시

다음 예에서는 3번 예를 바탕으로 관리자 및 직원의 이름과 각각의 직함을 추가하는 방법을 보여 줍니다. 관리자와 직원의 계층을 추가로 강조하기 위해 각 수준을 들여쓰기 했습니다.

복사

USE AdventureWorks2008R2;
GO
WITH DirectReports(Name, Title, EmployeeID, EmployeeLevel, Sort)
AS (SELECT CONVERT(varchar(255), e.FirstName + ' ' + e.LastName),
        e.Title,
        e.EmployeeID,
        1,
        CONVERT(varchar(255), e.FirstName + ' ' + e.LastName)
    FROM dbo.MyEmployees AS e
    WHERE e.ManagerID IS NULL
    UNION ALL
    SELECT CONVERT(varchar(255), REPLICATE ('|    ' , EmployeeLevel) +
        e.FirstName + ' ' + e.LastName),
        e.Title,
        e.EmployeeID,
        EmployeeLevel + 1,
        CONVERT (varchar(255), RTRIM(Sort) + '|    ' + FirstName + ' ' + 
                 LastName)
    FROM dbo.MyEmployees AS e
    JOIN DirectReports AS d ON e.ManagerID = d.EmployeeID
    )
SELECT EmployeeID, Name, Title, EmployeeLevel
FROM DirectReports 
ORDER BY Sort;
GO

7. MAXRECURSION을 사용하여 문 취소

잘못 구성된 재귀 CTE가 무한 루프에 진입하는 것을 방지하는 데 MAXRECURSION을 사용할 수 있습니다. 다음 예에서는 의도적으로 무한 루프를 만들고 MAXRECURSION 힌트를 사용하여 재귀 수준을 2로 제한하는 방법을 보여 줍니다.

복사

USE AdventureWorks2008R2;
GO
--Creates an infinite loop
WITH cte (EmployeeID, ManagerID, Title) as
(
    SELECT EmployeeID, ManagerID, Title
    FROM dbo.MyEmployees
    WHERE ManagerID IS NOT NULL
  UNION ALL
    SELECT cte.EmployeeID, cte.ManagerID, cte.Title
    FROM cte 
    JOIN  dbo.MyEmployees AS e 
        ON cte.ManagerID = e.EmployeeID
)
--Uses MAXRECURSION to limit the recursive levels to 2
SELECT EmployeeID, ManagerID, Title
FROM cte
OPTION (MAXRECURSION 2);
GO

코딩 오류를 교정한 다음에는 더 이상 MAXRECURSION이 필요하지 않습니다. 다음 예에서는 교정된 코드를 보여 줍니다.

복사

USE AdventureWorks2008R2;
GO
WITH cte (EmployeeID, ManagerID, Title)
AS
(
    SELECT EmployeeID, ManagerID, Title
    FROM dbo.MyEmployees
    WHERE ManagerID IS NOT NULL
  UNION ALL
    SELECT  e.EmployeeID, e.ManagerID, e.Title
    FROM dbo.MyEmployees AS e
    JOIN cte ON e.ManagerID = cte.EmployeeID
)
SELECT EmployeeID, ManagerID, Title
FROM cte;
GO

8. 공통 테이블 식을 사용하여 SELECT 문에서 재귀적 관계를 선택적으로 단계별 진행

다음 예에서는 ProductAssemblyID = 800 자전거를 제작하는 데 필요한 부품과 구성 요소의 계층을 보여 줍니다.

복사

USE AdventureWorks2008R2;
GO
WITH Parts(AssemblyID, ComponentID, PerAssemblyQty, EndDate, ComponentLevel) AS
(
    SELECT b.ProductAssemblyID, b.ComponentID, b.PerAssemblyQty,
        b.EndDate, 0 AS ComponentLevel
    FROM Production.BillOfMaterials AS b
    WHERE b.ProductAssemblyID = 800
          AND b.EndDate IS NULL
    UNION ALL
    SELECT bom.ProductAssemblyID, bom.ComponentID, p.PerAssemblyQty,
        bom.EndDate, ComponentLevel + 1
    FROM Production.BillOfMaterials AS bom 
        INNER JOIN Parts AS p
        ON bom.ProductAssemblyID = p.ComponentID
        AND bom.EndDate IS NULL
)
SELECT AssemblyID, ComponentID, Name, PerAssemblyQty, EndDate,
        ComponentLevel 
FROM Parts AS p
    INNER JOIN Production.Product AS pr
    ON p.ComponentID = pr.ProductID
ORDER BY ComponentLevel, AssemblyID, ComponentID;
GO

9. UPDATE 문에서 재귀 CTE 사용

다음 예에서는 ProductAssemblyID 800을 만드는 데 직접 또는 간접적으로 사용되는 모든 부분과 구성 요소의 PerAssemnblyQty 값을 업데이트합니다. 공통 테이블 식은 ProductAssemblyID 800를 만드는 데 직접 사용되는 부분 및 해당 구성 요소를 만드는 데 사용되는 부분과 같은 식으로 이어지는 계층적 목록을 반환합니다. 이렇게 공통 테이블 식이 반환한 행만 수정됩니다.

복사

USE AdventureWorks2008R2;
GO
WITH Parts(AssemblyID, ComponentID, PerAssemblyQty, EndDate, ComponentLevel) AS
(
    SELECT b.ProductAssemblyID, b.ComponentID, b.PerAssemblyQty,
        b.EndDate, 0 AS ComponentLevel
    FROM Production.BillOfMaterials AS b
    WHERE b.ProductAssemblyID = 800
          AND b.EndDate IS NULL
    UNION ALL
    SELECT bom.ProductAssemblyID, bom.ComponentID, p.PerAssemblyQty,
        bom.EndDate, ComponentLevel + 1
    FROM Production.BillOfMaterials AS bom 
        INNER JOIN Parts AS p
        ON bom.ProductAssemblyID = p.ComponentID
        AND bom.EndDate IS NULL
)
UPDATE Production.BillOfMaterials
SET PerAssemblyQty = c.PerAssemblyQty * 2
FROM Production.BillOfMaterials AS c
JOIN Parts AS d ON c.ProductAssemblyID = d.AssemblyID
WHERE d.ComponentLevel = 0; 

10. 여러 앵커 및 재귀 멤버 사용

다음 예에서는 지정된 인물의 모든 조상을 반환하기 위해 여러 개의 앵커 및 재귀 멤버를 사용하는 방법을 보여 줍니다. 이 예는 재귀 CTE가 반환한 가족 계보를 구성하기 위해 하나의 테이블을 만들고 값을 삽입합니다.

복사

-- Genealogy table
IF OBJECT_ID('dbo.Person','U') IS NOT NULL DROP TABLE dbo.Person;
GO
CREATE TABLE dbo.Person(ID int, Name varchar(30), Mother int, Father int);
GO
INSERT dbo.Person 
VALUES(1, 'Sue', NULL, NULL)
      ,(2, 'Ed', NULL, NULL)
      ,(3, 'Emma', 1, 2)
      ,(4, 'Jack', 1, 2)
      ,(5, 'Jane', NULL, NULL)
      ,(6, 'Bonnie', 5, 4)
      ,(7, 'Bill', 5, 4);
GO
-- Create the recursive CTE to find all of Bonnie's ancestors.
WITH Generation (ID) AS
(
-- First anchor member returns Bonnie's mother.
    SELECT Mother 
    FROM dbo.Person
    WHERE Name = 'Bonnie'
UNION
-- Second anchor member returns Bonnie's father.
    SELECT Father 
    FROM dbo.Person
    WHERE Name = 'Bonnie'
UNION ALL
-- First recursive member returns male ancestors of the previous generation.
    SELECT Person.Father
    FROM Generation, Person
    WHERE Generation.ID=Person.ID
UNION ALL
-- Second recursive member returns female ancestors of the previous generation.
    SELECT Person.Mother
    FROM Generation, dbo.Person
    WHERE Generation.ID=Person.ID
)
SELECT Person.ID, Person.Name, Person.Mother, Person.Father
FROM Generation, dbo.Person
WHERE Generation.ID = Person.ID;
GO

11. 재귀 CTE에서 분석 함수 사용

다음 예에서는 CTE의 재귀 부분에서 분석 또는 집계 함수를 사용할 때 발생할 수 있는 문제를 보여 줍니다.

복사

DECLARE @t1 TABLE (itmID int, itmIDComp int)
INSERT @t1 VALUES (1,10), (2,10);

DECLARE @t2 TABLE (itmID int, itmIDComp int)
INSERT @t2 VALUES (3,10), (4,10);

WITH vw AS
(
    SELECT itmIDComp, itmID
    FROM @t1

    UNION ALL

    SELECT itmIDComp, itmID
    FROM @t2
)
,r AS
(
    SELECT t.itmID AS itmIDComp
           , NULL AS itmID
           ,CAST(0 AS bigint) AS N
           ,1 AS Lvl
    FROM (SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4) AS t (itmID)

UNION ALL

SELECT t.itmIDComp
    , t.itmID
    , ROW_NUMBER() OVER(PARTITION BY t.itmIDComp ORDER BY t.itmIDComp, t.itmID) AS N
    , Lvl + 1
FROM r 
    JOIN vw AS t ON t.itmID = r.itmIDComp
)

SELECT Lvl, N FROM r

다음은 쿼리의 예상 결과입니다.

복사

Lvl  N
1    0
1    0
1    0
1    0
2    4
2    3
2    2
2    1

다음은 쿼리의 실제 결과입니다.

복사

Lvl  N
1    0
1    0
1    0
1    0
2    1
2    1
2    1
2    1

N은 CTE 재귀 부분의 각 패스에 대해 1을 반환하는데 그 이유는 해당 재귀 수준에 대한 데이터 하위 집합만 ROWNUMBER로 전달되기 때문입니다. 쿼리 재귀 부분이 반복될 때마다 각각 하나의 행만 ROWNUMBER로 전달됩니다.