SQL에서는 WITH 절을 CTE라고도 합니다. 이는 쿼리 내에서 임시 결과 집합을 생성할 수 있게 해주는 강력한 기능입니다. CTE의 주요 역할 중 하나는 복잡한 쿼리를 더 작고 재사용 가능한 하위 쿼리로 단순화하는 것입니다. 이는 장기적으로 코드를 더 읽기 쉽고 유지 관리하기 쉽게 만드는 데 도움이 됩니다.
WITH 절과 지원되는 기능을 사용하여 공통 테이블 표현식의 작동을 탐색하는 이 튜토리얼에 참여하세요.
요구사항:
데모 목적으로 다음을 사용합니다.
- MySQL 버전 8.0 이상
- Sakila 샘플 데이터베이스
주어진 요구 사항이 충족되면 CTE 및 WITH 절에 대해 자세히 알아볼 수 있습니다.
SQL WITH 절
WITH 절을 사용하면 공통 테이블 표현식으로 알려진 하나 이상의 임시 결과 세트를 정의할 수 있습니다.
다른 테이블이나 결과 집합과 마찬가지로 기본 쿼리에서 결과 CTE를 참조할 수 있습니다. 이는 모듈식 SQL 쿼리를 생성하는 데 중요한 역할을 합니다.
CTE 구문은 요구 사항에 따라 약간 다를 수 있지만 다음은 SQL의 기본 CTE 구문을 보여줍니다.
WITH cte_name (열1, 열2, ...) AS (
-- CTE 쿼리
선택하다 ...
에서 ...
어디 ...
)
-- 주요 쿼리
선택하다 ...
에서 ...
cte_name 가입 ON ...
어디 ...
CTE를 생성하고 사용하려고 한다는 것을 SQL 데이터베이스에 알리는 WITH 키워드로 시작합니다.
다음으로 다른 쿼리에서 참조할 수 있도록 CTE의 이름을 지정합니다.
CTE에 열 별칭이 포함된 경우 열 이름의 선택적 목록도 지정합니다.
다음으로 CTE 쿼리 정의를 진행합니다. 여기에는 CTE가 수행하는 모든 작업 또는 데이터가 한 쌍의 괄호로 묶여 포함됩니다.
마지막으로 CTE를 참조하는 기본 쿼리를 지정합니다.
사용 예:
CTE를 사용하고 작업하는 방법을 이해하는 가장 좋은 방법 중 하나는 실제 예를 살펴보는 것입니다.
Sakila 샘플 데이터베이스를 예로 들어 보겠습니다. 가장 많은 임대 건수를 보유한 상위 10명의 고객을 찾고 싶다고 가정해 보겠습니다.
다음 CTE를 살펴보세요.
SQL WITH 절을 사용하여 임대 수가 가장 많은 상위 10명의 고객을 찾습니다.
CustomerRentals AS 사용(SELECT c.customer_id, c.first_name, c.last_name, COUNT(r.rental_id) AS rent_count
고객 C로부터
JOIN 렌탈 r ON c.customer_id = r.customer_id
GROUP BY c.customer_id, c.first_name, c.last_name
)
선택하다 *
고객렌털로부터
Rental_count DESC로 주문
제한 10;
주어진 예에서는 WITH 키워드와 CTE에 할당하려는 이름을 사용하여 새 CTE를 정의하는 것부터 시작합니다. 이 경우 'CustomerRentals'라고 합니다.
CTE 본문 내에서는 고객 및 임대 테이블을 조인하여 각 고객의 임대 수를 계산합니다.
마지막으로 기본 쿼리에서는 CTE의 모든 열을 선택하고 임대 건수에 따라 결과를 정렬하고(내림차순) 출력을 상위 10개 행으로 제한합니다.
이를 통해 다음 출력에 표시된 대로 임대 수가 가장 많은 고객을 가져올 수 있습니다.
재귀적 CTE
다른 경우에는 계층적 데이터 구조를 다룰 수도 있습니다. 이것이 재귀적 CTE가 작동하는 곳입니다.
계층적 조직을 탐색하거나 트리와 같은 구조를 표현하려는 경우를 예로 들어 보겠습니다. WITH RECURSIVE 키워드를 사용하여 재귀적 CTE를 생성할 수 있습니다.
재귀적 CTE를 보여주기 위해 Sakila 데이터베이스에 사용할 수 있는 계층적 데이터가 없으므로 기본 예를 설정하겠습니다.
CREATE TABLE 부서(Department_id INT PRIMARY KEY AUTO_INCREMENT,
부서_이름 VARCHAR(255) NOT NULL,
parent_department_id INT,
FOREIGN KEY (parent_department_id) 참조 부서(department_id)
);
INSERT INTO 부서(department_name, parent_department_id)
가치
('회사', NULL),
('금융', 1),
('HR', 1),
('회계', 2),
'모집', 3),
('급여', 4);
이 경우 임의의 데이터가 포함된 샘플 '부서' 테이블이 있습니다. 부서의 계층 구조를 찾으려면 다음과 같이 재귀 CTE를 사용할 수 있습니다.
RECURSIVE DepartmentHierarchy AS 사용(SELECT 부서_ID, 부서_이름, 상위_부서_ID
부서에서
parent_department_id가 NULL인 경우
유니온 올
SELECT d.department_id, d.department_name, d.parent_department_id
부서 d에서
JOIN DepartmentHierarchy dh ON d.parent_department_id = dh.department_id
)
선택하다 *
FROM 부서 계층;
이 경우 재귀적 CTE는 NULL 'parent_department_id'(루트 부서)가 있는 부서에서 시작하여 하위 부서를 재귀적으로 검색합니다.
결론
이 튜토리얼에서는 WITH 키워드를 사용하는 방법을 이해함으로써 공통 테이블 표현식과 같은 SQL 데이터베이스의 가장 기본적이고 유용한 기능에 대해 배웠습니다.