[ 쿼리 ] Advanced SQL for Data Professionals (1) - Common Table Expression(CTE)

이 포스트는 Medium에 올라온 다음 아티클을 참고하여 정리한 글입니다.

Advanced SQL for Data Professionals

---

이번에 정리할 Advanced SQL 케이스는 Common Table Expression(CTE)이다.

 

한글로 다른 표현이 있나 찾아봤는데 그냥 CTE라고 불리는 것 같다.

CTE는 "SELECT, INSERT, UPDATE, DELETE에서 참조가 가능한 임시의 결과값"이라고 하며 크고 복잡한 쿼리를 단순화 시키는데 유용하고 위계 또는 계층이 있는 데이터를 조작하는데 이점이 있다고 한다.

 

종류로 재귀적 CTE와 비재귀적 CTE가 있다고 한다.

비재귀적 CTE가 있다고 하기보다는 CTE가 있고 활용법중 하나로 재귀적 CTE가 있다고 보면 될 것 같다.

 

이번에 다룰 내용들은 다음과 같다.

- 재귀적 CTE를 활용한 organizational employee hierarchies 조회

- 재귀적 CTE를 활용한 date dimension table createion

---

[ 재귀적 CTE를 활용한 organizational employee hierarchies 조회 ]

우선 쿼리 실습을 위한 테이블을 생성하고 데이터를 입력한다.

Medium 아티클에서 써준 쿼리를 그대로 입력하니 MySQL에서는 데이터 형식 이름이나 문법 차이로 오류가 발생한다.

이 블로그 포스팅에 적혀있는 쿼리들은 MySQL 문법에 맞게 내가 수정해서 사용한 쿼리다.

CREATE TABLE Employees
(
  EmployeeID int,
  FirstName varchar(20),
  LastName varchar(20),
  ManagerID int 
);

INSERT INTO Employees VALUES (1, 'Harper', 'Westbrook', NULL);
INSERT INTO Employees VALUES (2, 'Liam', 'Carrington', 1);
INSERT INTO Employees VALUES (3, 'Evelyn', 'Radcliffe', 1);
INSERT INTO Employees VALUES (4, 'Mason', 'Albright', 2);
INSERT INTO Employees VALUES (5, 'Isla', 'Whitman', 2);
INSERT INTO Employees VALUES (6, 'Noah', 'Sterling', 3);
INSERT INTO Employees VALUES (7, 'Ruby', 'Lennox', 3);
INSERT INTO Employees VALUES (8, 'Caleb', 'Winslow', 5);
INSERT INTO Employees VALUES (9, 'Avery', 'Sinclair', 6);
INSERT INTO Employees VALUES (10, 'Oliver', 'Beckett', 6);

 

입력한 데이터는 각 직원과 그 직원의 상사에 대한 정보로 구성되어있다.

오른쪽의 캡쳐가 실제 입력된 데이터를 조회한 결과이다.

1번 ID를 가진 Harper Westbrook이 상사가 없는 보스인 셈이다.

재귀적 CTE는 이러한 데이터를 다루기에 좋다고 한다.

다음 쿼리가 재귀적 CTE를 활용해 이 데이터에서 직원별 상사 현황을 조회하는 쿼리다.

 

WITH RECURSIVE 
  cteReports (EmpID, FirstName, LastName, MgrID, EmpLevel)
  AS
  (
    SELECT EmployeeID, FirstName, LastName, ManagerID, 1
    FROM Employees
    WHERE ManagerID IS NULL
    UNION ALL
    SELECT e.EmployeeID, e.FirstName, e.LastName, e.ManagerID, 
      r.EmpLevel + 1
    FROM Employees e
    INNER JOIN cteReports r
    ON e.ManagerID = r.EmpID
  )
SELECT
  CONCAT(FirstName,' ',LastName) AS FullName, 
  EmpLevel,
  ( SELECT CONCAT(FirstName,' ',LastName) 
    FROM Employees 
    WHERE EmployeeID = cteReports.MgrID) AS Manager
FROM cteReports 
ORDER BY EmpLevel, MgrID
;

 

쿼리 결과는 오른쪽과 같다.

재귀적이라는 말의 의미를 알 것 같다.

cteReports라는 이름의 임시 결과값을 만들어두고 실제 결과를 도출할 때 이 임시 결과값에서 데이터를 불러오는데 임시 결과값을 만드는 과정에서도 지금까지 만들어진 결과값을 참조한다.

 

 

# WITH RECURSIVE라는 구문을 빼고 그 내부의 쿼리만 가져오면 다음과 같다.
# 이 쿼리는 오류를 발생시킨다.
# cteReports라는 테이블이 정의되어있지 않기 때문이다.
# 캡쳐1
SELECT EmployeeID, FirstName, LastName, ManagerID, 1
FROM Employees
WHERE ManagerID IS NULL
UNION ALL
SELECT e.EmployeeID, e.FirstName, e.LastName, e.ManagerID, 
  r.EmpLevel + 1
FROM Employees e
INNER JOIN cteReports r
ON e.ManagerID = r.EmpID
;

# 위의 쿼리에서 UNION ALL 이전의 쿼리만 가져오면 다음과 같고 오류없이 조회된다.
# 이 쿼리는 ManagerID가 NULL인 직원 즉 보스를 조회하는 쿼리다.
# 캡쳐2
SELECT EmployeeID, FirstName, LastName, ManagerID, 1
FROM Employees
WHERE ManagerID IS NULL
;

# UNION ALL 이후의 쿼리만 가져오면 다음과 같다.
# 해당 쿼리에서 cteReports를 참조하는데 이 쿼리만 실행하면 오류가 발생하나
# with RECURSIVE 기능으로 ManagerID가 NULL인 직원을 조회한 결과를 참조할 것이다.
# r.EmpLevel + 1 의 결과에 0 + 1 의 결과인 1이 들어간다는 뜻이다.
# 재귀적으로 그 다음에는 1 + 1, 2 + 1로 이어진다.
SELECT e.EmployeeID, e.FirstName, e.LastName, e.ManagerID, 
  r.EmpLevel + 1
FROM Employees e
INNER JOIN cteReports r
ON e.ManagerID = r.EmpID
;

# 그렇게 조회된 결과를 다른 조정 없이 조회하려면 다음과 같은 쿼리로 조회된다.
# 캡쳐3
WITH RECURSIVE 
  cteReports (EmpID, FirstName, LastName, MgrID, EmpLevel)
  AS
  (
    SELECT EmployeeID, FirstName, LastName, ManagerID, 1
    FROM Employees
    WHERE ManagerID IS NULL
    UNION ALL
    SELECT e.EmployeeID, e.FirstName, e.LastName, e.ManagerID, 
      r.EmpLevel + 1
    FROM Employees e
    INNER JOIN cteReports r
    ON e.ManagerID = r.EmpID
  )
SELECT *
FROM cteReports 
;

캡쳐1. cteReports가 정의되지 않아서 오류가 발생

---

캡쳐2. ManagerID가 NULL인 데이터를 조회한 결과

---

캡쳐3. 재귀적 CTE 결과를 그대로 조회했을 때

---

[ 재귀적 CTE를 활용한 date dimension table createion ]

Medium 아티클에서 말하는 date dimention table은 날짜 필드를 가진 테이블을 의미한다.

이를 만들 때 원하는 시작 일자와 마지막 일자를 입력하면 해당 날짜들 사이의 모든 날짜들의 데이터를 가지는 테이블이 만들어지는 것이다.

WITH RECURSIVE dates (date, year, month) AS (
    SELECT '2024-01-01' AS date
      , EXTRACT(YEAR FROM '2024-01-01') AS year
      , EXTRACT(MONTH FROM '2024-01-01') AS month
    UNION ALL 
    SELECT DATE_ADD(date , INTERVAL 1 day) AS date
      , EXTRACT(YEAR FROM DATE_ADD(date , INTERVAL 1 DAY)) AS year
      , EXTRACT(MONTH FROM DATE_ADD(date , INTERVAL 1 DAY)) AS month
	FROM dates
    WHERE date <= '2024-01-10' -- Put the end date here 
)
SELECT *
FROM dates
;

 

쿼리 결과는 오른쪽과 같다.

같은 방식으로 응용하면 시간 데이터를 가진 테이블, 일정 간격을 가지는 숫자 등으로 활용할 수도 있겠다.

재귀적 CTE의 활용법을 이해하기 좋은 예시였다.

 

 

 

---

Databricks SQL에서는 재귀적 CTE를 지원하지 않는다고 한다. 그 대신 EXPLODE 함수로 같은 결과를 만들 수 있다.

 

실무에서는 CTE를 사용해본적이 없었는데 앞으로 사용할 기회가 많을 것 같다.

다음 포스트에서는 중복 행 제거(Removing Duplicated Rows) 내용을 정리한다.