MySQL Common Table Expression(CTE) WITH

---

Common Table Expression

CTE(Common Table Expression)는 서브쿼리로 쓰이는 파생 테이블과 비슷한 개념으로 사용된다.
VIEW는 권한이 필요하고 사전에 정의를 해야 하는 반면 CTE는 권한이 필요없고 하나의 쿼리문이 끝날 때 까지만 지속되는 일회성 테이블이다.
CTE는 주로 복잡한 쿼리문에서 코드의 가독성과 재사용성을 위해 파생테이블 대신 사용하기에 유용하다.
CTE에는 재귀적 CTE와 비재귀적 CTE가 있다.
(MySQL은 8.0부터 지원한다.)

필요한 컬럼과 데이터를 미리 정의해 놓고 활용할 수 있어서 효율적인 쿼리를 작성하는데 유용하다.

-- CTE로 임시 테이블 생성
WITH seperate_bike(year, month, day, hour, count) AS(
    SELECT EXTRACT(YEAR FROM datetime),
        EXTRACT(MONTH FROM datetime),
        EXTRACT(DAY FROM datetime),
        EXTRACT(HOUR FROM datetime), count FROM bike
)

-- 생성된 임시 테이블을 활용한 쿼리
SELECT month, SUM(count) AS total_count
FROM seperate_bike
GROUP BY month
ORDER BY month;

WITH [RECURSIVE] 테이블명 AS (
    SELECT -- 무조건 필수
    [UNION ALL] -- RECURSIVE 시 필수. 다음에 이어붙어야 할 때 사용
    SELECT
    [WHERE -] -- RECURSIVE 시 필수. 정지 조건 필요할 때 사용
)

WITH 구문

WITH 구문 이후에 오는 쿼리에서 임시 테이블의 테이블명을 사용하여 값을 참조할 수 있다.

• TARGET_TABLE의 행 수 만큼 0의 값을 가진 임시 테이블

WITH CTE AS (
    SELECT 0 AS NUM
    UNION ALL
    SELECT 0 FROM TARGET_TABLE -- TARGET_TABLE의 행 수만큼 반복
)

WITH RECURSIVE 구문

WITH RECURSIVE 구문은 가상 테이블을 생성하면서 가상 테이블 자신의 값을 참조하여 값을 결정할 때 사용된다.

• 0~10의 값을 가진 임시 테이블

WITH RECURSIVE CTE AS(
    SELECT 0 AS NUM -- 초기값 설정
    UNION ALL
    SELECT NUM+1 FROM CTE -- 자신의 값을 참조
    WEHRE NUM < 10 -- 반복을 멈추는 조건
)​

예제 문제

입양 시각 구하기(2)

각 시간대에 값이 없더라도 COUNT 컬럼에 0을 출력해야 한다.
이를 위해 0~23까지 시간을 가지는 임시 테이블을 JOIN하여 쿼리문을 작성했다.

WITH RECURSIVE TIME AS(
    SELECT 0 AS HOUR
    UNION ALL
    SELECT HOUR + 1
    FROM TIME
    WHERE HOUR < 23
)

SELECT TIME.HOUR, COUNT(ANIMAL_ID) `COUNT`
FROM TIME LEFT JOIN (
    SELECT ANIMAL_ID, HOUR(DATETIME) HOUR
    FROM ANIMAL_OUTS
    ) D USING (HOUR)
GROUP BY 1
ORDER BY 1;

---

Reference

• YaHwang: SQL로 CTE를 표현하는 WITH 활용하기
• 호랭개발자: 계층 쿼리 - WITH, WITH RECURSIVE 사용법