2012/04/25 15:56

mssql cte(common table expression) 이란. DB/SQL



요놈 유용하게 사용할 수 있을꺼 같다. 

참고로, 셈플 소스를 넣어보자면.. 
아래와 같고, 결과는 동일하다. 

ORACLE ---------
SELECT LEVEL, ROLE_ID, ROLE_NAME, ROLE_UPID, ROLE_SEQ, ROLE_DESC
   FROM TB_ROLE
START WITH ROLE_UPID IS NULL
CONNECT BY PRIOR ROLE_ID = ROLE_UPID
ORDER SIBLINGS BY ROLE_SEQ
) A

MSSQL ---------
WITH orderedRoleCTE
(
ROLE_ID, ROLE_NAME, ROLE_UPID, ROLE_SEQ, ROLE_DESC
)
AS
(
SELECT ROLE_ID, ROLE_NAME, ROLE_UPID, ROLE_SEQ, ROLE_DESC
   FROM TB_ROLE 
 WHERE ROLE_UPID IS NULL OR ROLE_UPID = ''
UNION ALL
SELECT R.ROLE_ID, R.ROLE_NAME, R.ROLE_UPID, R.ROLE_SEQ, R.ROLE_DESC
    FROM TB_ROLE AS R
              JOIN orderedRoleCTE AS C
                ON R.ROLE_UPID = C.ROLE_ID
)
SELECT *
    FROM orderedRoleCTE 



공용 테이블 식

개발자가 작업 중인 프로젝트 중에는 기본적인 SELECT/FROM/WHERE 문의 유형을 벗어난 복잡한 SQL 문 작성을 포함하는 것이 많습니다. 이러한 시나리오 중 하나로 FROM 절 내에서 인라인 뷰라고도 하는 파생 테이블을 사용하는 Transact-SQL(T-SQL) 쿼리를 작성하는 예가 있습니다. 개발자는 이러한 일반적인 방법으로 하나의 SELECT 문에서 행 집합을 얻은 다음, 즉시 이 행 집합을 다른 테이블, 뷰 및 사용자 정의 함수에 조인할 수 있습니다. 다른 옵션은 파생 테이블 대신 뷰를 사용하는 것입니다. 이러한 옵션은 각기 장단점이 있습니다.
SQL Server™ 2005를 사용하는 경우 필자는 CTE(공용 테이블 식)이라는 세 번째 옵션을 선호합니다. CTE를 사용하면 성능을 저해하지 않고도 코드 가독성과 관리 용이성을 개선할 수 있습니다. 또한 SQL Server 이전 버전에 비해 T-SQL로 재귀 코드를 작성하기가 훨씬 쉬워졌습니다.
이번 달 칼럼에서는 CTE를 사용하여 일반적인 개발 시나리오를 해결하는 데 초점을 맞추겠습니다. 먼저 CTE의 작동 방식과 CTE를 사용하여 해결할 수 있는 시나리오를 설명하겠습니다. 그런 다음 파생 테이블, 뷰 및 사용자 지정 프로시저와 같은 기존 T-SQL 구문에 비해 CTE를 사용할 때의 장점을 알아볼 것입니다. 칼럼 전반에서 예를 제공하고 이러한 예를 어디에 어떻게 사용해야 하는지 설명할 것입니다. 또한 CTE가 재귀 논리를 처리하는 방법을 설명하고 재귀적 CTE가 작동하는 방법을 정의할 것입니다. 이 칼럼에서 설명하는 모든 코드는 MSDN® Magazine 웹 사이트에서 다운로드할 수 있으며, SQL Server 2005와 함께 제공되는 Northwind 및 AdventureWorks 데이터베이스를 사용합니다.

뷰, 파생 테이블 및 CTE
CTE는 쿼리가 데이터베이스 내에 테이블로 존재하지 않는 데이터 집합에서 선택해야 하는 경우에 유용합니다. 예를 들어 고객 및 고객 주문을 바탕으로 값을 계산하는 집계된 데이터의 집합에 대해 쿼리를 작성하기를 원할 수 있습니다. 집계된 데이터를 Customers, Orders 및 Order Details 테이블을 함께 조인하고 합계를 계산한 다음 주문의 평균값을 계산할 수 있습니다. 그리고 집계된 행 집합에 대해 쿼리를 실행할 수 있습니다. 한 가지 솔루션은 먼저 집계된 데이터를 수집하는 뷰를 만든 다음 이 뷰를 대상으로 작업할 쿼리를 작성하는 것입니다. 다른 옵션은 파생 테이블을 사용하여 집계된 데이터에 대해 쿼리를 수행하는 것입니다. 이를 위해서 SQL 문을 FROM 절로 옮기고 이에 대해 쿼리를 수행할 수 있습니다.
일반적으로 뷰는 큰 쿼리를 나누어 알아보기 방법으로 쿼리를 수행하기 위해 사용됩니다. 예를 들어 테이블 10개를 조인하고, 수십 개의 열을 선택하며, 연관된 논리 집합을 바탕으로 열을 필터링하는 SELECT 문을 뷰로 나타낼 수 있습니다. 이러한 뷰는 데이터베이스 전체에 걸쳐 다른 SELECT 문에서 쿼리할 수 있습니다. 추상화를 통해 뷰가 나타내는 행 집합에 대한 손쉬운 액세스가 제공되며 데이터를 복제하거나 임시 테이블에 저장할 필요도 없습니다.
뷰는 사용 권한이 허락한다는 가정하에 데이터베이스 전체에 걸쳐 다시 사용할 수 있습니다. 예를 들어 그림 1에서는 뷰를 만든 다음 다른 T-SQL 문에서 다시 사용하고 있습니다. 그러나 데이터를 수집하고 한 번만 사용하려는 경우에는 뷰가 최적의 솔루션이 아닐 수 있습니다. 뷰는 데이터베이스에 존재하며 모든 일괄 처리에서 사용할 수 있는 데이터베이스 개체이므로 단일 T-SQL 일괄 처리에서만 사용할 뷰를 만드는 것은 과잉 처리입니다.
CREATE VIEW vwMyView ASSELECT    EmployeeID, COUNT(*) AS NumOrders,  MAX(OrderDate) AS MaxDateFROM OrdersGROUP BY EmployeeIDGOSELECT     e.EmployeeID, oe.NumOrders, oe.MaxDate, e.ReportsTo AS ManagerID,     om.NumOrders, om.MaxDateFROM     Employees AS e    INNER JOIN vwMyView AS oe ON e.EmployeeID = oe.EmployeeID    INNER JOIN vwMyView AS om ON e.ReportsTo = om.EmployeeID
인라인 뷰라고 하는 파생 테이블을 만드는 다른 옵션이 있습니다. 파생 테이블을 만들려면 간단히 SELECT 문을 괄호로 감싸고 FROM 절 내부로 옮기면 됩니다. 이렇게 생성된 파생 테이블은 쿼리의 대상으로 사용하거나 테이블 또는 뷰와 마찬가지로 조인할 수 있습니다. 그림 2에서는 뷰가 아닌 파생 테이블을 사용하여 그림 1에서 해결한 것과 동일한 쿼리를 해결하고 있습니다. 파생 테이블은 이를 만드는 문 내에서만 액세스할 수 있으므로 일반적으로 쿼리를 읽고 유지 관리하기 어렵게 만듭니다. 이러한 문제는 동일한 일괄 처리 내에서 파생 테이블을 여러 번 사용하려는 경우에 가중됩니다. 다시 사용할 파생 테이블을 여러 번 복사하고 붙여 넣어야 하기 때문입니다.
SELECT     e.EmployeeID, oe.NumOrders, oe.MaxDate,  e.ReportsTo AS ManagerID,     om.NumOrders, om.MaxDateFROM     Employees AS e    INNER JOIN         (SELECT EmployeeID, COUNT(*), MAX(OrderDate)         FROM Orders         GROUP BY EmployeeID) AS oe(EmployeeID, NumOrders, MaxDate)        ON e.EmployeeID = oe.EmployeeID    LEFT JOIN         (SELECT EmployeeID, COUNT(*), MAX(OrderDate)         FROM Orders         GROUP BY EmployeeID) AS om(EmployeeID, NumOrders, MaxDate)        ON e.ReportsTo = om.EmployeeID
CTE는 뷰와 비슷하게 T-SQL을 훨씬 읽기 쉽게 만들어 주므로 이러한 시나리오에 잘 맞습니다. 또한 동일한 일괄 처리 내에서 곧 이은 후속 쿼리에서 다시 사용할 수도 있습니다. 물론 이 범위를 벗어나면 사용할 수 없게 됩니다. 또한 CTE는 언어 수준 구문이므로 SQL Server가 내부적으로 임시 또는 가상 테이블을 만들 필요가 없습니다. CTE의 기본 쿼리는 곧 이은 후속 쿼리에서 참조될 때마다 호출됩니다.
앞서 살펴본 것과 동일한 시나리오를 그림 3과 같이 CTE를 사용하여 작성할 수 있습니다. 집계된 데이터를 수집하는 EmpOrdersCTE는 CTE 바로 다음에 있는 쿼리에서 사용됩니다. CTE를 사용하는 그림 3의 코드는 쿼리를 매우 읽기 쉽게 만들어 주면서도 메타데이터를 저장하기 위한 시스템 개체를 만들지 않습니다.
;WITH EmpOrdersCTE (EmployeeID, NumOrders, MaxDate) AS(  SELECT EmployeeID, COUNT(*), MAX(OrderDate)  FROM Orders  GROUP BY EmployeeID)SELECT     e.EmployeeID,  oe.NumOrders, oe.MaxDate,    e.ReportsTo AS ManagerID,  om.NumOrders, om.MaxDateFROM     Employees AS e    INNER JOIN EmpOrdersCTE oe ON e.EmployeeID = oe.EmployeeID    LEFT JOIN EmpOrdersCTE om ON e.ReportsTo = om.EmployeeID

CTE의 구조
다음은 간단한 CTE 예를 통해서 CTE를 작성하는 방법을 살펴보겠습니다. CTE는 WITH 키워드로 시작됩니다. 그러나 CTE가 일괄 처리의 첫 번째 문이 아닌 경우에는 WITH 키워드 앞에 세미콜론을 붙여야 합니다. 필자는 최선의 방법으로 모든 CTE 앞에 세미콜론을 붙이는 것을 선호합니다. 세미콜론이 필요한지 기억하는 것보다는 이렇게 하는 것이 훨씬 일관성이 있습니다.
WITH 키워드 다음에는 CTE의 이름을 지정하고 그 다음에는 선택적인 열 별칭 목록을 지정합니다. 열 별칭은 CTE 내의 SELECT 문에서 반환하는 열에 해당합니다. 선택적인 열 별칭 다음에는 필수 항목인 AS 키워드를 지정합니다. 그리고 AS 키워드 다음에는 CTE를 정의하는 쿼리 식을 괄호로 감싸고 지정합니다.
다음 예를 살펴보겠습니다.
;WITH myCTE (CustID, Co) AS(  SELECT CustomerID, CompanyName FROM Customers)SELECT CustID, Co FROM myCTE
CustomerID와 CompanyName열에는 CustID와 Co라는 별칭을 지정했으며, 곧이어 오는 CTE는 해당 열 별칭을 사용하여 CTE를 참조하는 SELECT 문입니다.

CTE의 이해
CTE 설계를 시작하기 전에 CTE가 작동하는 방식과 어떤 규칙을 따라야 하는지 이해해야 합니다. 이 섹션에서는 CTE를 사용할 수 있는 용도와 함께 CTE 내부에서 수행할 수 있는 작업과 그렇지 못한 작업에 대해 살펴보겠습니다. 우선 CTE는 T-SQL 일괄 처리, 사용자 정의 함수, 저장 프로시저, 트리고 및 뷰 내에서 만들고 사용할 수 있습니다.
CTE 바로 다음에 오는 문에서만 CTE를 참조할 수 있습니다. 이것은 CTE를 사용하려면 T-SQL 일괄 처리에서 CTE 다음에 즉시 CTE를 참조하는 쿼리를 작성해야 한다는 것을 의미합니다. 다름 들어 다음 일괄 처리는 오류가 발생합니다.
;WITH myCTE (CustID, Co) AS(  SELECT CustomerID, CompanyName FROM Customers)SELECT CompanyName FROM Customers WHERE CustomerID = 'ALFKI'SELECT CustID, Co FROM myCTE
이 코드에서 myCTE는 바로 다음에 있는 첫 번째 쿼리에서만 사용할 수 있습니다. 두 번째 쿼리에서 myCTE를 참조하면 CTE는 범위를 벗어나며 "개체 이름 'myCTE'이(가) 잘못되었습니다."라는 예외가 발생합니다.
CTE는 어떤 방식으로든 데이터를 다시 처리하는 다른 쿼리에서 참조하기 위한 목적으로 사용되므로 CTE의 쿼리는 ORDER나 COMPUTE와 같은 문을 포함할 수 없습니다. 그러나 FOR XML과 같은 복잡한 문은 CTE를 정의하고 CTE에서 작동하도록 사용할 수 있습니다. 예를 들어 다음과 같이 FOR XML 절을 사용하여 CTE를 쿼리하고 해당 결과를 반환할 수 있습니다.
;WITH myCTE AS(  SELECT c.CustomerID, c.CompanyName, o.OrderID, o.OrderDate  FROM Customers c   INNER JOIN Orders o ON c.CustomerID = o.CustomerID)SELECT CustomerID, CompanyName, OrderID, OrderDate FROM myCTE FOR XML AUTO
일단 CTE를 정의한 뒤에는 다음에 오는 첫 번째 쿼리에서 이를 여러 번 참조할 수 있습니다. 이러한 특성은 쿼리가 CTE를 두 번 이상 참조해야 하는 경우 특히 유용합니다. 그림 3의 코드 샘플에서는 쿼리에서 EmpOrdersCTE를 두 번 참조하여 직원과 해당 직원의 관리자를 얻는 방법을 보여 줍니다. 쿼리를 복제하는 것보다는 CTE를 두 번 참조하는 것이 더 간단하므로 이러한 방법은 동일한 행 집합을 두 번 이상 참조해야 하는 경우 매우 유용합니다.
CTE를 SELECT 문에서만 사용해야 하는 것은 아니며 CTE가 생성하는 행 집합을 참조하는 어떤 문에서나 CTE를 사용할 수 있습니다. 이것은 CTE 다음에 CTE를 사용하는 SELECT, INSERT, UPDATE 또는 DELETE 문이 올 수 있음을 의미합니다. CTE를 사용하는 쿼리에 전진 전용 및 스냅샷 커서를 사용할 수도 있습니다.
또는 CTE 다음에 다른 CTE가 오는 것도 가능합니다. 이 기술은 중간 결과를 행 집합에 수집하려는 경우 CTE로 다른 CTE를 만드는 데 사용할 수 있습니다. 다른 CTE로 구성된 CTE를 만들 때는 CTE 정의를 쉼표로 분리합니다.
그림 4에서는 직원의 목록과 각 직원별 전체 주문 횟수를 수집하는 EmpOrdersCTE를 정의합니다. MinMaxOrdersCTE라는 두 번째 CTE는 첫 번째 EmpOrdersCTE를 쿼리하고 행 집합에 집계 함수를 수행하여 직원의 평균, 최소 및 최대 주문 횟수를 확인합니다.
;WITH EmpOrdersCTE (EmployeeID, NumOrders)AS(  SELECT EmployeeID, COUNT(*)  FROM Orders  GROUP BY EmployeeID),MinMaxOrdersCTE (Mn, Mx, Diff)AS(  SELECT MIN(NumOrders), MAX(NumOrders), AVG(NumOrders)  FROM EmpOrdersCTE)SELECT Mn, Mx,    DiffFROM MinMaxOrdersCTE
WITH 키워드 다음 쉼표로 분리하여 여러 개의 CTE를 정의할 수 있습니다. 이 경우 각 CTE는 다음 CTE에서 참조되어 새로운 CTE를 구성합니다. CTE 정의 다음에 오는 DML(데이터 조작 언어) 문에서는 WITH 절 내에 정의된 모든 CTE를 참조할 수 있습니다.

재귀의 규칙
재귀 알고리즘을 구현하는 데도 CTE를 사용할 수 있습니다. 재귀 논리는 스스로를 호출하는 알고리즘을 작성해야 할 때 유용하며, 데이터의 중첩된 집합을 탐색하는 데 자주 사용됩니다. T-SQL과 같은 언어에서 재귀 논리를 작성하기는 특히 까다롭지만 CTE를 설계한 목적 중 하나는 바로 이러한 재귀 논리를 해결하기 위한 것이었습니다. 재귀 CTE를 작성하는 기본 공식은 다음과 같습니다.
  1. 최상위 수준(앵커 멤버)을 반환하는 쿼리를 작성합니다.
  2. 재귀 쿼리(재귀 멤버)를 작성합니다.
  3. 첫 번째 쿼리에 재귀 번째 쿼리로 UNION을 수행합니다.
  4. 행이 반환되지 않는 경우에 대비합니다. 이것이 종료 검사입니다.
다음은 재귀 CTE의 예입니다.
;WITH myRecursiveCTE(col1, col2, ... coln) AS(   -- Anchor Member Query  UNION ALL  -- Recursive Member Query that references myRecursiveCTE)
CTE를 포함하지 않는 사용자 지정 재귀 프로시저를 작성할 때는 반드시 명시적 종료 절을 추가해야 합니다. 종료 절은 최종적으로는 재귀 알고리즘을 종료하고 재귀 호출 스택을 원상 복귀하는 임무를 담당합니다. 이러한 절이 없으면 코드에 무한 루프가 발생합니다.
CTE에는 종료 절의 처리를 도울 수 있는 두 가지 측면이 있습니다. 첫 번째는 재귀 멤버가 레코드 0개를 반환하는 경우에 해당하는 암시적인 종료 절입니다. 이러한 경우 재귀 멤버 쿼리는 CTE를 재귀적으로 호출하지 않고 호출 스택을 원상 복귀합니다. 두 번째는 명시적으로 MAXRECURSION 수준을 설정할 수 있다는 것입니다.
MAXRECURSION 수준은 CTE를 포함하는 일괄 처리 내에서 명시적으로 설정하거나 서버 쪽 설정을 통해 설정할 수 있습니다. 서버 차원 설정의 기본값은 변경하지 않은 경우 100입니다. 이 설정은 CTE가 자신을 재귀적으로 호출할 수 있는 횟수를 제한합니다. 한계에 다다르면 예외가 발생합니다. MAXRECURSION 수준을 설정하는 구문은 다음과 같이 CTE 다음의 SELECT 문에서 OPTION 절을 사용하는 것입니다.
-- DEFINE YOUR CTE HERESELECT * FROM EmpCTEOPTION (MAXRECURSION 7)
이 밖에도 재귀 CTE를 설계할 때 염두에 두어야 할 몇 가지 다른 규칙이 있습니다. 재귀 CTE는 앵커 멤버와 재귀 멤버를 모두 포함해야 합니다. 두 멤버에는 같은 수의 열이 있어야 하며 두 멤버에 속한 열은 데이터 형식이 일치해야 합니다. 재귀 멤버는 CTE를 한 번만 참조할 수 있으며 멤버에 다음 절이나 키워드를 사용할 수 없습니다.
  • SELECT DISTINCT
  • GROUP BY
  • HAVING
  • TOP
  • LEFT/RIGHT OUTER JOIN

간단한 재귀 연습
데이터와 행 집합의 관점에서 재귀는 동일한 데이터 집합에 대해 다른 조건을 적용하여 동일한 논리를 반복적으로 수행해야 하는 문제를 해결하는 데 사용됩니다. 예를 들어 모든 판매 사원을 검색하고 해당 사원의 관리자를 찾아 계층 순서대로 데이터를 반환해야 한다고 가정해 보겠습니다. 그림 5에서는 재귀를 사용하여 영업 부사장 밑에서 일하는 직원의 목록을 수집하는 CTE를 사용하는 솔루션을 보여 주고 있습니다.
;WITH EmpCTE(EmployeeID, EmployeeFirstName, EmployeeLastName, MgrID, SalesLevel)AS(   -- Anchor Member   SELECT EmployeeID, FirstName, LastName, ReportsTo, 0  FROM Employees  WHERE EmployeeID = 2 -- Start with the VP of Sales  UNION ALL    -- Recursive Member   SELECT     e.EmployeeID, e.FirstName, e.LastName, e.ReportsTo, m.SalesLevel+1  FROM     Employees AS e    INNER JOIN EmpCTE m ON e.ReportsTo = m.EmployeeID) -- Using the CTESELECT EmployeeID, EmployeeFirstName, EmployeeLastName,        MgrID, SalesLevel  FROM EmpCTE
몇 가지 추가적인 측면을 제외하고 그림 5에 있는 재귀 CTE는 표준 CTE와 매우 비슷합니다. 표준 CTE는 행 집합을 정의하는 쿼리 한 개를 포함하는데 반해, 재귀 CTE는 두 개의 쿼리 정의를 정의합니다. 첫 번째 쿼리 정의인 앵커 멤버는 CTE가 호출될 때 실행될 쿼리를 정의합니다. 두 번째 쿼리 정의인 재귀 멤버는 앵커 멤버와 동일한 열과 데이터 형식을 반환하는 쿼리를 정의합니다. 재귀 멤버는 또한 재귀적으로 CTE에 콜백을 수행하는 데 사용할 값을 검색합니다. 쿼리의 결과는 UNION 문을 사용하여 함께 가져오게 됩니다.
그림 5의 EmpCTE는 영업 부사장(EmployeeID = 2)에 대한 직원 레코드를 가져오는 앵커 멤버를 보여 줍니다. 앵커 멤버 쿼리의 마지막 열은 계층의 0번째 수준, 즉 최상위를 나타내는 0 값을 반환합니다. 재귀 멤버의 쿼리는 이전 직원 밑에서 근무하는 직원의 목록을 얻습니다. 이를 위해 Employees 테이블을 EmpCTE에 조인합니다.
재귀 멤버에서도 동일한 열이 검색되지만 SalesLevel 열은 현재 직원의 관리자를 얻고, 관리자의 SalesLevel을 얻은 다음, 이를 1만큼 증가시켜서 계산됩니다. m.SalesLevel+1 식은 우리의 앵커 멤버로부터 검색한 영업 부사장 바로 밑에서 근무하는 모든 직원에게 SalesLevel 1 값을 할당합니다. 이러한 직원 밑에서 일하는 모든 직원은 SalesLevel 2 값을 가집니다. SalesLevel 값은 영업 조직 계층의 이어지는 각 수준에서 이와 같이 점차적으로 증가합니다.

요약
쿼리 내에서 복잡한 파생 테이블을 사용하거나 T-SQL 일괄 처리 외부에 정의가 있는 뷰를 참조하는 것이 비하면 CTE는 T-SQL을 훨씬 읽기 쉽게 작성하는 방법을 제공합니다. CTE는 또한 재귀 알고리즘을 사용하는 데 따르는 어려움을 완화하는 데 도움을 주는 훨씬 개선된 도구를 제공합니다. 표준 CTE 또는 재귀 CTE 중 어떤 것을 사용하는지에 관계없이 CTE를 통해 여러 일반적인 개발 시나리오의 문제를 해결하고 성능을 저하시키지 않고도 가독성을 높일 수 있게 될 것입니다.

덧글

댓글 입력 영역