소개
이 랩에서는 MySQL 에서 Common Table Expressions(CTE) 를 사용하는 방법을 배우게 됩니다. CTE 는 복잡한 쿼리를 논리적이고 읽기 쉬운 단계로 분할하여 단순화하는 데 도움이 됩니다. CTE 는 단일 SQL 문 내에서 참조할 수 있는 임시 명명된 결과 집합을 생성합니다.
먼저 데이터베이스를 설정하고 직원 데이터를 필터링하기 위한 간단한 CTE 를 생성합니다. 그런 다음 계층적 데이터 (예: 조직도) 를 처리하기 위해 재귀 CTE 를 탐색합니다. 마지막으로 CTE 를 테이블과 조인하여 더 고급 데이터 분석을 수행하는 방법을 배우게 됩니다.
데이터베이스 및 간단한 CTE 설정
이 첫 번째 단계에서는 데이터베이스 환경을 설정하고 간단한 Common Table Expression(CTE) 을 정의합니다. CTE 는 WITH 절을 사용하여 정의되며 단일 쿼리 기간 동안만 존재하는 임시 뷰 역할을 합니다. 이를 통해 복잡한 쿼리를 더 쉽게 읽고 관리할 수 있습니다.
먼저 데스크톱에서 터미널을 엽니다.
root 사용자로 MySQL 서버에 연결합니다. 이 랩 환경에서는 비밀번호 없이 sudo를 사용하여 연결할 수 있습니다.
sudo mysql -u root
연결되면 MySQL 프롬프트 (mysql>) 가 표시됩니다. 이제 labex_db라는 데이터베이스를 생성하고 해당 데이터베이스로 전환합니다.
CREATE DATABASE IF NOT EXISTS labex_db;
USE labex_db;
다음으로 employees 테이블을 생성하고 샘플 데이터를 삽입합니다. 이 테이블은 직원의 부서 및 급여를 포함한 기본 정보를 저장합니다.
CREATE TABLE IF NOT EXISTS employees (
employee_id INT PRIMARY KEY,
first_name VARCHAR(50),
last_name VARCHAR(50),
department VARCHAR(50),
salary DECIMAL(10, 2)
);
INSERT INTO employees (employee_id, first_name, last_name, department, salary) VALUES
(1, 'John', 'Doe', 'Sales', 60000.00),
(2, 'Jane', 'Smith', 'Marketing', 75000.00),
(3, 'Robert', 'Jones', 'Sales', 55000.00),
(4, 'Emily', 'Brown', 'IT', 90000.00),
(5, 'Michael', 'Davis', 'Marketing', 70000.00);
이제 테이블이 준비되었으므로 'Sales' 부서의 직원만 선택하는 간단한 CTE 를 정의해 보겠습니다.
WITH SalesEmployees AS (
SELECT employee_id, first_name, last_name, salary
FROM employees
WHERE department = 'Sales'
)
SELECT * FROM SalesEmployees;
이 쿼리를 자세히 살펴보겠습니다.
WITH SalesEmployees AS (...):SalesEmployees라는 CTE 를 정의합니다.- 괄호 안의 쿼리는
department가 'Sales'인employees테이블에서 직원을 선택합니다. SELECT * FROM SalesEmployees;: 이는 임시SalesEmployees결과 집합에서 모든 데이터를 검색하는 기본 쿼리입니다.
Sales 부서의 직원만 나열하는 다음 출력이 표시되어야 합니다.
+-------------+------------+-----------+----------+
| employee_id | first_name | last_name | salary |
+-------------+------------+-----------+----------+
| 1 | John | Doe | 60000.00 |
| 3 | Robert | Jones | 55000.00 |
+-------------+------------+-----------+----------+
2 rows in set (0.00 sec)
이는 첫 번째 CTE 가 올바르게 작동함을 확인합니다. 다음 단계를 위해 MySQL 셸에 계속 머물러 주십시오.
계층적 데이터를 위한 재귀 CTE 생성
재귀 CTE 는 조직도 또는 중첩된 카테고리와 같은 계층적 데이터를 쿼리하는 강력한 기능입니다. 재귀 CTE 는 트리와 같은 구조를 레벨별로 처리하기 위해 자체를 참조합니다. "앵커 멤버"(시작점) 와 "재귀 멤버"(반복) 로 구성됩니다.
이 단계에서는 직원 계층을 나타내는 테이블을 생성하고 재귀 CTE 를 사용하여 표시합니다.
먼저 MySQL 셸에 있는 동안 employee_hierarchy 테이블을 생성합니다. 이 테이블에는 다른 직원의 employee_id를 가리키는 manager_id가 포함되어 계층 구조를 만듭니다.
CREATE TABLE IF NOT EXISTS employee_hierarchy (
employee_id INT PRIMARY KEY,
employee_name VARCHAR(50),
manager_id INT
);
INSERT INTO employee_hierarchy (employee_id, employee_name, manager_id) VALUES
(1, 'David', NULL),
(2, 'Emily', 1),
(3, 'Frank', 1),
(4, 'Grace', 2),
(5, 'Henry', 2),
(6, 'Ivy', 3),
(7, 'Jack', 3);
이 구조에서 'David'는 manager_id가 NULL이므로 최상위 관리자입니다.
이제 이 계층 구조를 탐색하기 위한 재귀 CTE 를 작성합니다. RECURSIVE 키워드가 필요합니다.
WITH RECURSIVE EmployeeHierarchyCTE AS (
-- Anchor member: 최상위 관리자를 선택합니다.
SELECT employee_id, employee_name, manager_id, 0 AS level
FROM employee_hierarchy
WHERE manager_id IS NULL
UNION ALL
-- Recursive member: 자체와 조인하여 하위 직원을 찾습니다.
SELECT e.employee_id, e.employee_name, e.manager_id, h.level + 1
FROM employee_hierarchy e
INNER JOIN EmployeeHierarchyCTE h ON e.manager_id = h.employee_id
)
SELECT * FROM EmployeeHierarchyCTE ORDER BY level, employee_name;
이 쿼리를 분석해 보겠습니다.
- 앵커 멤버: 첫 번째
SELECT문은 계층 구조의 루트 (manager_id가NULL인 곳) 를 찾고level0 을 할당합니다. - 재귀 멤버: 두 번째
SELECT문은employee_hierarchy를 CTE 자체 (EmployeeHierarchyCTE) 와 조인합니다. CTE 에 이미 있는employee_id와 일치하는manager_id를 가진 모든 직원을 찾고level을 증가시킵니다. - UNION ALL: 이 연산자는 앵커 멤버와 재귀 멤버의 결과를 결합합니다.
쿼리는 레벨이 있는 전체 조직도를 보여주는 다음 출력을 생성합니다.
+-------------+---------------+------------+-------+
| employee_id | employee_name | manager_id | level |
+-------------+---------------+------------+-------+
| 1 | David | NULL | 0 |
| 2 | Emily | 1 | 1 |
| 3 | Frank | 1 | 1 |
| 4 | Grace | 2 | 2 |
| 5 | Henry | 2 | 2 |
| 6 | Ivy | 3 | 2 |
| 7 | Jack | 3 | 2 |
+-------------+---------------+------------+-------+
7 rows in set (0.00 sec)
재귀 CTE 를 사용하여 계층적 데이터를 성공적으로 쿼리했습니다. 마지막 단계를 위해 MySQL 셸에 계속 머물러 주십시오.
CTE 와 테이블 조인
CTE 는 일반 테이블처럼 다른 테이블과 조인할 수 있습니다. 이는 CTE 에서 집계된 데이터를 다른 테이블의 상세 데이터와 결합하는 데 유용합니다. 이 단계에서는 부서별 평균 급여를 계산하는 CTE 를 생성한 다음 employees 테이블과 다시 조인합니다.
MySQL 셸에 있는 동안 다음 쿼리를 실행합니다. 이 쿼리는 AvgSalaryByDepartment라는 CTE 를 정의한 다음 employees 테이블과 조인합니다.
WITH AvgSalaryByDepartment AS (
SELECT
department,
AVG(salary) AS avg_salary
FROM employees
GROUP BY department
)
SELECT
e.employee_id,
e.first_name,
e.department,
e.salary,
a.avg_salary
FROM
employees e
JOIN
AvgSalaryByDepartment a ON e.department = a.department;
다음은 분석입니다.
AvgSalaryByDepartmentCTE 는 각 부서의 평균 급여를 계산합니다.- 그런 다음 기본 쿼리는
employees테이블 (별칭e) 을 이 CTE(별칭a) 와department열을 기준으로 조인합니다. - 이를 통해 각 직원의 급여를 해당 부서의 평균 급여 옆에 표시할 수 있습니다.
예상 출력은 다음과 같습니다.
+-------------+------------+------------+----------+--------------+
| employee_id | first_name | department | salary | avg_salary |
+-------------+------------+------------+----------+--------------+
| 1 | John | Sales | 60000.00 | 57500.000000 |
| 2 | Jane | Marketing | 75000.00 | 72500.000000 |
| 3 | Robert | Sales | 55000.00 | 57500.000000 |
| 4 | Emily | IT | 90000.00 | 90000.000000 |
| 5 | Michael | Marketing | 70000.00 | 72500.000000 |
+-------------+------------+------------+----------+--------------+
5 rows in set (0.00 sec)
결과를 수동으로 확인할 수 있습니다. 예를 들어 'Sales' 부서의 평균 급여는 (60000 + 55000) / 2 = 57500이며 이는 출력과 일치합니다. 이는 쿼리가 올바르게 작동함을 확인합니다.
CTE 와 테이블을 성공적으로 조인했습니다. 이제 MySQL 셸을 종료할 수 있습니다.
exit;
요약
이 실습에서는 MySQL 에서 공통 테이블 표현식 (CTE) 을 효과적으로 사용하는 방법을 배웠습니다. 데이터베이스와 테이블을 설정하는 것으로 시작하여 다양한 유형의 CTE 를 생성하는 과정을 진행했습니다.
다음과 같은 내용을 배웠습니다.
WITH절을 사용하여 데이터를 필터링하고 쿼리 가독성을 향상시키는 간단한 CTE 정의- 테이블의 계층적 데이터를 탐색하고 표시하기 위한 재귀 CTE 생성
- CTE 와 테이블을 조인하여 집계된 결과와 상세 행 수준 데이터를 결합하여 더 복잡한 분석 수행
이러한 기술은 특히 복잡한 로직이나 데이터 구조를 다룰 때 깔끔하고 유지 관리하기 쉬우며 강력한 SQL 쿼리를 작성하는 데 필수적입니다.
