SQLite CTE 쿼리

소개

이 랩에서는 SQLite 에서 Common Table Expressions (CTEs) 의 강력함을 탐구합니다. CTE 를 정의하고 사용하여 쿼리 가독성과 유지 관리성을 향상시키는 방법을 배우게 됩니다. 간단한 CTE 부터 시작하여 재귀 CTE 로 이동합니다. 이 랩을 마치면 CTE 를 사용하여 더 깔끔하고 효율적이며 이해하기 쉬운 SQL 코드를 작성할 수 있게 됩니다.

데이터베이스 및 테이블 생성

이 단계에서는 SQLite 데이터베이스와 employees 테이블을 생성합니다. 이 테이블은 직원 정보를 저장하며, 이후 단계에서 CTE 쿼리를 연습하는 데 사용됩니다.

먼저, LabEx VM 에서 터미널을 엽니다. 기본 경로는 /home/labex/project입니다.

이제 company.db라는 SQLite 데이터베이스를 생성해 보겠습니다. 다음 명령을 실행하여 데이터베이스 파일을 생성하고 SQLite 명령줄 도구를 엽니다.

sqlite3 company.db

SQLite 셸 내부에 있다는 것을 나타내는 프롬프트가 표시됩니다.

SQLite version 3.x.x
Enter ".help" for usage hints.
sqlite>

다음으로, 기본 직원 정보를 저장할 employees라는 테이블을 생성합니다. 이 테이블에는 id, name, department, salary의 네 개의 열이 있습니다. sqlite> 프롬프트에서 다음 SQL 명령을 입력하고 Enter 키를 누릅니다.

CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    name TEXT,
    department TEXT,
    salary INTEGER
);

이 명령은 다음과 같이 employees 테이블을 설정합니다.

• id는 각 직원의 기본 키 역할을 하는 정수입니다.
• name은 직원의 이름을 위한 텍스트 필드입니다.
• department는 직원의 부서를 위한 텍스트 필드입니다.
• salary는 직원의 급여를 위한 정수 필드입니다.

명령이 성공적으로 실행되면 아무런 출력도 표시되지 않습니다.

테이블에 데이터 삽입

employees 테이블을 생성했으므로 이제 데이터를 추가해 보겠습니다. 테이블에 다섯 개의 직원 레코드를 삽입합니다.

sqlite> 프롬프트에서 다음 명령을 하나씩 실행하여 employees 테이블에 다섯 개의 직원 레코드를 삽입합니다.

INSERT INTO employees (name, department, salary) VALUES ('Alice', 'Sales', 50000);
INSERT INTO employees (name, department, salary) VALUES ('Bob', 'Marketing', 60000);
INSERT INTO employees (name, department, salary) VALUES ('Charlie', 'Sales', 55000);
INSERT INTO employees (name, department, salary) VALUES ('David', 'Engineering', 70000);
INSERT INTO employees (name, department, salary) VALUES ('Eve', 'Marketing', 62000);

이 명령은 employees 테이블에 다섯 개의 행을 추가합니다.

• INSERT INTO employees (name, department, salary)는 employees 테이블의 name, department, salary 열에 데이터를 삽입함을 지정합니다.
• VALUES ('Alice', 'Sales', 50000)은 각 레코드에 삽입할 값을 제공합니다.

데이터가 올바르게 추가되었는지 확인하려면 다음 명령을 실행하여 테이블의 모든 레코드를 확인합니다.

SELECT * FROM employees;

예상 출력:

1|Alice|Sales|50000
2|Bob|Marketing|60000
3|Charlie|Sales|55000
4|David|Engineering|70000
5|Eve|Marketing|62000

이 출력은 각 레코드의 id, name, department, salary를 보여줍니다. SELECT * 명령은 지정된 테이블에서 모든 열을 검색합니다.

간단한 CTE 정의

이 단계에서는 SQLite 에서 간단한 Common Table Expression (CTE) 을 정의하고 사용하는 방법을 배웁니다. CTE 는 단일 SQL 문 내에서 참조할 수 있는 임시, 명명된 결과 집합입니다. 복잡한 쿼리를 더 작고 관리하기 쉬운 부분으로 나누어 가독성과 유지 관리성을 향상시키는 데 유용합니다.

CTE 는 본질적으로 단일 쿼리의 지속 시간 동안만 존재하는 명명된 하위 쿼리입니다. WITH 절을 사용하여 CTE 를 정의하고, 이름을 지정하고, 결과 집합을 생성하는 쿼리를 지정합니다. 그런 다음 일반 테이블처럼 CTE 의 이름을 주 쿼리에서 참조할 수 있습니다.

기본 구문:

WITH
    cte_name AS (
        SELECT column1, column2
        FROM table1
        WHERE condition
    )
SELECT column1, column2
FROM cte_name
WHERE another_condition;

'Sales' 부서의 직원을 선택하는 CTE 를 정의해 보겠습니다. sqlite> 프롬프트에서 다음 SQL 문을 실행합니다.

WITH
    SalesEmployees AS (
        SELECT id, name, salary
        FROM employees
        WHERE department = 'Sales'
    )
SELECT id, name, salary
FROM SalesEmployees
WHERE salary > 52000;

이 쿼리는 먼저 'Sales' 부서의 모든 직원의 id, name, salary를 선택하는 SalesEmployees라는 CTE 를 정의합니다. 그런 다음 주 쿼리는 급여가 52000 보다 큰 SalesEmployees CTE 에서 id, name, salary를 선택합니다.

예상 출력:

3|Charlie|55000

이것은 Charlie 가 급여가 52000 보다 큰 Sales 부서의 유일한 직원임을 보여줍니다.

재귀 CTE 구축

이 단계에서는 SQLite 에서 재귀 Common Table Expression (CTE) 을 구축하고 사용하는 방법을 배웁니다. 재귀 CTE 는 계층적 또는 트리 구조 데이터를 쿼리하는 데 사용됩니다. 테이블 내의 관계를 탐색하고 계층 구조의 다양한 수준에서 데이터를 검색할 수 있습니다.

재귀 CTE 는 자신을 참조하는 CTE 입니다. 두 부분으로 구성됩니다.

• Anchor Member (앵커 멤버): 재귀의 기본 사례 또는 시작점을 정의하는 초기 SELECT 문입니다.
• Recursive Member (재귀 멤버): CTE 자체를 참조하는 SELECT 문입니다. 이 부분은 재귀 단계를 수행하여 이전 반복의 결과를 기반으로 구축합니다.

앵커 멤버와 재귀 멤버는 UNION ALL 연산자를 사용하여 결합됩니다. 재귀는 재귀 멤버가 빈 결과 집합을 반환할 때까지 계속됩니다.

먼저, 다음과 같은 구조의 employees_hierarchy라는 테이블을 생성해 보겠습니다.

CREATE TABLE employees_hierarchy (
    id INTEGER PRIMARY KEY,
    name TEXT,
    manager_id INTEGER,
    title TEXT
);

이 테이블은 직원 계층 구조를 나타내며, 여기서 manager_id는 직원의 관리자의 id를 참조합니다. sqlite> 프롬프트에서 위의 명령을 실행합니다.

다음으로, employees_hierarchy 테이블에 몇 가지 샘플 데이터를 삽입합니다.

INSERT INTO employees_hierarchy (name, manager_id, title) VALUES ('Alice', NULL, 'CEO');
INSERT INTO employees_hierarchy (name, manager_id, title) VALUES ('Bob', 1, 'VP of Marketing');
INSERT INTO employees_hierarchy (name, manager_id, title) VALUES ('Charlie', 1, 'VP of Sales');
INSERT INTO employees_hierarchy (name, manager_id, title) VALUES ('David', 2, 'Marketing Manager');
INSERT INTO employees_hierarchy (name, manager_id, title) VALUES ('Eve', 3, 'Sales Manager');
INSERT INTO employees_hierarchy (name, manager_id, title) VALUES ('Frank', 4, 'Marketing Specialist');
INSERT INTO employees_hierarchy (name, manager_id, title) VALUES ('Grace', 5, 'Sales Representative');

여기서 Alice 는 CEO (관리자 없음), Bob 과 Charlie 는 Alice 에게 보고하고, David 는 Bob 에게 보고하고, Eve 는 Charlie 에게 보고하고, Frank 는 David 에게 보고하고, Grace 는 Eve 에게 보고합니다. sqlite> 프롬프트에서 위의 명령을 실행합니다.

이제 Alice (CEO) 아래의 전체 계층 구조를 검색하기 위해 재귀 CTE 를 구축해 보겠습니다. 다음 SQL 문을 실행합니다.

WITH RECURSIVE
    EmployeeHierarchy(id, name, manager_id, title, level) AS (
        -- Anchor member: Select the CEO
        SELECT id, name, manager_id, title, 0 AS level
        FROM employees_hierarchy
        WHERE manager_id IS NULL

        UNION ALL

        -- Recursive member: Select employees reporting to the current level
        SELECT e.id, e.name, e.manager_id, e.title, eh.level + 1
        FROM employees_hierarchy e
        JOIN EmployeeHierarchy eh ON e.manager_id = eh.id
    )
SELECT id, name, title, level
FROM EmployeeHierarchy;

이 쿼리는 EmployeeHierarchy라는 재귀 CTE 를 정의합니다. 앵커 멤버는 CEO 를 선택합니다 (여기서 manager_id는 NULL 입니다). 재귀 멤버는 employees_hierarchy 테이블을 EmployeeHierarchy CTE 와 조인하여 이전 수준에서 선택된 직원에게 보고하는 직원을 찾습니다. level 열은 계층 구조의 깊이를 추적합니다.

예상 출력:

1|Alice|CEO|0
2|Bob|VP of Marketing|1
3|Charlie|VP of Sales|1
4|David|Marketing Manager|2
5|Eve|Sales Manager|2
6|Frank|Marketing Specialist|3
7|Grace|Sales Representative|3

이것은 전체 직원 계층 구조를 보여주며, level은 보고 수준을 나타냅니다.

복잡한 쿼리에 CTE 통합

이 단계에서는 SQLite 에서 CTE 를 더 복잡한 쿼리에 통합하는 방법을 배웁니다. 단일 쿼리 내에서 여러 CTE 를 사용하는 방법을 보게 됩니다.

쉼표로 구분하여 단일 쿼리에서 여러 CTE 를 정의할 수 있습니다. 이를 통해 복잡한 쿼리를 여러 논리적 단계로 나누고 각 단계를 CTE 로 나타낼 수 있습니다.

다음 구조의 department_salaries라는 새 테이블을 생성해 보겠습니다.

CREATE TABLE department_salaries (
    department TEXT,
    total_salary INTEGER
);

이 테이블은 각 부서의 총 급여를 저장합니다. sqlite> 프롬프트에서 위의 명령을 실행합니다.

이제 CTE 를 사용하여 각 부서의 총 급여를 계산하고 결과를 department_salaries 테이블에 삽입해 보겠습니다. 다음 SQL 문을 실행합니다.

WITH
    DepartmentTotalSalaries AS (
        SELECT department, SUM(salary) AS total_salary
        FROM employees
        GROUP BY department
    )
INSERT INTO department_salaries (department, total_salary)
SELECT department, total_salary
FROM DepartmentTotalSalaries;

SELECT * FROM department_salaries;

이 쿼리는 먼저 employees 테이블을 사용하여 각 부서의 총 급여를 계산하는 DepartmentTotalSalaries라는 CTE 를 정의합니다. 그런 다음 DepartmentTotalSalaries CTE 의 결과를 department_salaries 테이블에 삽입합니다. 마지막으로, 결과를 표시하기 위해 department_salaries 테이블에서 모든 데이터를 선택합니다.

예상 출력:

Sales|105000
Marketing|122000
Engineering|70000

이것은 각 부서의 총 급여를 보여줍니다.

요약

이 랩에서는 SQLite 에서 Common Table Expression (CTE) 을 정의하고 사용하는 방법을 배웠습니다. 테이블에서 데이터를 선택하는 간단한 CTE 로 시작하여 계층적 데이터를 쿼리하기 위한 재귀 CTE 로 이동했습니다. 마지막으로, CTE 를 더 복잡한 쿼리에 통합하는 방법을 배웠습니다. CTE 는 더 깔끔하고, 효율적이며, 이해하기 쉬운 SQL 코드를 작성하기 위한 강력한 도구입니다.