CTE란 무엇인가요?

AI생성 이미지라 KERWWAD ㅎㅎ;;

CTE란 무엇인가요?

Common Table Expression으로 후속 문에서 사용할 수 있는 임시 테이블입니다. 쉽게 말해 쿼리 출력을 참조하는 다른 쿼리를 작성하기 위해서 쿼리출력 결과를 임시로 저장해 후속 문에서 사용합니다. CTE는 하나의 쿼리 안에서만 사용될 수 있고 쿼리종료시 임시테이블의 정보는 모두 삭제됩니다.

비 재귀적 CTE와 재귀적 CTE로 나뉘고 비 재귀적 CTE는 마치 뷰 처럼 사용되고, 재귀적 CTE는 계층적 정보를 나타내는데 유용하게 사용됩니다.

비 재귀적 쿼리는 무엇인가요?

비 재귀적 쿼리는 WITH 절을 이용해 CTE를 정의합니다. 아래의 예시를 보겠습니다.

WITH SalesSummary AS (
    SELECT
        Salesperson,
        SUM(Amount) AS TotalSales
    FROM
        Sales
    GROUP BY
        Salesperson
)
SELECT
    Salesperson,
    TotalSales
FROM
    SalesSummary
WHERE
    TotalSales > 1000;

CTE 쿼리를 통해 각 사원의 판매량의 평균을 SalesSummary 임시 테이블으로 만듭니다. 그리고 후속 쿼리에서 FROM절로 SalesSummary 임시 테이블을 사용합니다.

보시다시피 뷰, 서브쿼리와 비슷해보이는데 차이점을 알아보겠습니다.

CTE VS 서브쿼리

• 서브쿼리의 특징
  • 서브쿼리는 다른 쿼리내에 포함된 쿼리입니다.
  • 서브쿼리는 괄호안에 쿼리를 작성해 서브쿼리를 정의합니다.
• 복잡한 쿼리일때 CTE가 가독성이 더 좋아 명확하게 표현할 수 있습니다.
• CTE의 결과는 여러번 참조될 수 있습니다.
• 서브쿼리는 단일 결과 값을 반환하거나 특정 조건에 맞는 데이터를 필터링하는데 자주 사용됩니다.
• 서브쿼리 예시

SELECT name FROM employees WHERE department_id IN (SELECT id FROM departments WHERE name = 'Sales');

CTE VS 뷰

• 뷰의 특징
  • 뷰는 쿼리의 결과를 나타내는 가상 테이블입니다.
  • 뷰는 데이터베이스에 영구적으로 존재합니다.
• CTE는 쿼리가 실행되는 동안만 존재하고, 뷰는 영구적으로 존재합니다.
• CTE는 메모리에 임시결과 집합을 저장하는 반면, 뷰는 실제 데이터를 저장하지 않고 쿼리 정의만 저장합니다.
• 뷰는 데이터를 추상화해 보안적으로 이점이 있습니다.
• 뷰 예시

CREATE VIEW sales_employees AS SELECT * FROM employees WHERE department = 'Sales'; 
SELECT * FROM sales_employees;

재귀적 CTE

재귀적 CTE는 데이터베이스에서 계층적 데이터를 다룰 수 있는 강력한 도구입니다. 자기참조를 사용해 반복적으로 쿼리를 실행해 트리구조의 데이터를 탐색하는데 유용합니다.

• 앵커멤버
  • 재귀의 기초가 되는 초기 멤버로 재귀적 쿼리의 시작점입니다.
• 재귀멤버
  • 이전 쿼리의 결과를 바탕으로 재귀적으로 자신을 참조하여 실행되는 쿼리입니다. 재귀멤버의 인풋이 0건이 되면 CTE생성이 완료됩니다.
• 아래의 예시를 보겠습니다.

WITH RECURSIVE NumberSequence AS (
    SELECT 1 AS Number
    UNION ALL
    SELECT Number + 1
    FROM NumberSequence
    WHERE Number < 10
)
SELECT * FROM NumberSequence;

• WITH RECURSIVE 절을 사용해 재귀적 CTE를 시작합니다.
• NumberSequence가 CTE의 결과 테이블의 이름이 됩니다.
• UNION ALL을 기준으로 위의 쿼리의 결과가 앵커멤버가 되고, 아래쿼리의 결과가 재귀멤버가 됩니다.
• 쿼리 진행순서는 다음과 같습니다.
  • 앵커멤버의 쿼리를 실행해 앵커멤버를 만듭니다. NumberSequence : (1)
  • 앵커멤버를 인풋(Number)로 재귀멤버의 쿼리를 실행합니다.
  • 출력으로 2가 나오는데 이는 NumberSequence테이블에 1과 UNION해 합칩니다. NumberSequence : (1, 2)
  • 이전 재귀멤버인 2를 인풋(Number)로 다음 재귀멤버의 쿼리를 실행합니다.
  • 반복합니다… (1, 2, 3, 4, 5, 6, 7, 8, 9)
  • 이전 재귀멤버인 9를 인풋으로 다음 재귀멤버의 쿼리를 실행합니다.
  • 재귀멤버로 10이 생성되었고 NumberSequence 테이블에 10을 추가합니다. (1, 2, 3, 4, 5, 6, 7, 8, 9, 10)
  • 쿼리의 출력이 0건이기 때문에 재귀를 벗어납니다.

그래서 재귀적 CTE어디에 써먹을수있는데요??

• 재귀적 CTE는 계층적이며 몇단계인지 매번 정의하기 어려운 경우 정보를 표현하는데 유용합니다.
  • ex) 우리조직의 계층은 7단계인데 앞으로 늘어날것같아..
• 조직도 조회

WITH RECURSIVE EmployeeHierarchy AS ( 
	SELECT EmployeeID, Name, ManagerID 
    FROM Employees 
    WHERE ManagerID IS NULL 
    UNION ALL 
    SELECT e.EmployeeID, e.Name, e.ManagerID 
    FROM Employees e 
	INNER JOIN EmployeeHierarchy eh ON e.ManagerID = eh.EmployeeID ) 
SELECT * FROM EmployeeHierarchy;

+------------+-------+-----------+
| EmployeeID | Name  | ManagerID |
+------------+-------+-----------+
|          1 | John  |      NULL |
|          2 | Jane  |         1 |
|          3 | Bob   |         1 |
|          4 | Alice |         2 |
|          5 | Eve   |         3 |
+------------+-------+-----------+

 

• 경로찾기

WITH RECURSIVE PathFinder AS ( 
	SELECT StartNode, EndNode, Path, Cost 
    FROM Paths 
    WHERE StartNode = 'A' 
    UNION ALL 
    SELECT pf.StartNode, p.EndNode, CONCAT(pf.Path, ' -> ', p.EndNode), pf.Cost + p.Cost 
    FROM PathFinder pf 
    INNER JOIN Paths p ON pf.EndNode = p.StartNode ) 
SELECT * FROM PathFinder;

+-----------+---------+------------------+------+
| StartNode | EndNode | Path             | Cost |
+-----------+---------+------------------+------+
| A         | B       | A -> B           |   10 |
| A         | C       | A -> C           |   15 |
| A         | D       | A -> B -> D      |   30 |
| A         | D       | A -> C -> D      |   45 |
| A         | E       | A -> C -> E      |   50 |
| A         | E       | A -> B -> D -> E |   55 |
| A         | E       | A -> C -> D -> E |   70 |
+-----------+---------+------------------+------+
7 rows in set (0.00 sec)

참고자료

https://medium.com/@dataproducts/advanced-sql-an-introduction-to-common-table-expression-cte-9ae8a391dff0

https://ishanjainoffical.medium.com/a-refresher-on-common-table-expressions-ctes-in-sql-eb234ac5fe61

https://medium.com/@theleonwei/mastering-recursive-ctes-in-sql-a-comprehensive-guide-2d70765a0ee6

https://medium.com/@theleonwei/ctes-vs-views-in-sql-essential-distinctions-for-tech-interviews-61181d74cd54

https://medium.com/@nidhig631/cte-vs-temp-tables-b18ef7cd79c7