프로그래머스 입양 시각 구하기(2)

WITH RECURSIVE TIMETABLE(HOUR) AS (
    SELECT 0
    UNION
    SELECT TIMETABLE.HOUR + 1 
    FROM TIMETABLE 
    WHERE TIMETABLE.HOUR < 23
)

SELECT HOUR, COUNT(A.ANIMAL_ID)
FROM TIMETABLE AS T LEFT JOIN ANIMAL_OUTS AS A ON T.HOUR = HOUR(A.DATETIME)
GROUP BY HOUR
ORDER BY HOUR

WITH

SQL에서 WITH 절은 공통 테이블 표현식(CTE, Common Table Expression)을 정의하는 데 사용된다. CTE는 복잡한 쿼리를 더 간결하게 만들고, 가독성을 높이며, 재사용할 수 있는 쿼리 블록을 생성하는 데 유용하다.

기본 구문

WITH CTE_NAME AS (
    -- CTE 정의를 위한 쿼리
    SELECT column1, column2
    FROM table_name
    WHERE condition
)
SELECT *
FROM CTE_NAME
WHERE another_condition;

예제

직원 테이블에서 평균 급여보다 높은 급여를 받는 직원 목록을 가져오는 예제:

WITH AverageSalary AS (
    SELECT AVG(salary) AS avg_salary
    FROM employees
)
SELECT name, salary
FROM employees
WHERE salary > (SELECT avg_salary FROM AverageSalary);

주요 장점

1. 가독성 향상: 복잡한 쿼리를 여러 블록으로 나눌 수 있어 이해하기 쉬워집니다.
2. 재사용성: 동일한 CTE를 여러 번 참조할 수 있습니다.
3. 성능 개선: 일부 데이터베이스에서는 CTE를 사용하여 쿼리 성능을 최적화할 수 있습니다.

CTE는 특히 복잡한 쿼리를 구성하고 싶은 경우 매우 유용한 도구다.

RECURSIVE

SQL에서 재귀 쿼리는 주로 계층적 데이터 구조를 처리할 때 사용된다. 대표적으로 CTE(Common Table Expression)를 사용하여 재귀 쿼리를 작성할 수 있다.

CTE를 이용한 재귀 쿼리

CTE는 WITH 절을 사용하여 정의하며, 재귀 쿼리는 두 부분으로 나눌 수 있다: 기본 쿼리와 재귀 쿼리다.

예제

employees라는 테이블이 있고, 이 테이블에는 직원의 ID, 이름, 그리고 상사의 ID가 포함되어 있다고 가정한다.

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    manager_id INT
);

이제 다음과 같은 데이터를 삽입했다고 가정

INSERT INTO employees (id, name, manager_id) VALUES
(1, '최고경영자', NULL),
(2, '부사장', 1),
(3, '이사', 2),
(4, '팀장', 3),
(5, '직원', 4);

이제 특정 직원(예: ID가 5인 직원)의 상사를 모두 찾는 재귀 쿼리를 작성할 수 있다.

WITH RECURSIVE EmployeeHierarchy AS (
    -- 기본 쿼리: 시작점
    SELECT id, name, manager_id
    FROM employees
    WHERE id = 5  -- 시작 직원의 ID

    UNION ALL

    -- 재귀 쿼리: 상사를 찾는 부분
    SELECT e.id, e.name, e.manager_id
    FROM employees e
    INNER JOIN EmployeeHierarchy eh ON e.id = eh.manager_id
)
SELECT * FROM EmployeeHierarchy;

이 쿼리는 ID가 5인 직원의 상사들을 찾아서 출력한다. 기본 쿼리에서 시작 직원(직원 ID 5)을 선택하고, 재귀적으로 그 직원의 상사를 찾는 방식으로 동작한다.

이와 같은 방식으로 다양한 계층적 데이터를 처리할 수 있다.