MySQL_WITH RECURSIVE(재귀 쿼리)

WITH RECURSIVE(MysQL 5.8이상 지원)

• 메모리 상에 가상의 테이블을 저장
• 실제로 테이블을 생성하거나 데이터 삽입(INSERT)을 하지 않아도 가상의 테이블을 만들 수 있다.(재귀 쿼리 이용)

WITH RECURSIVE 테이블명 AS(
    SELECT 초기값 AS 컬럼명
    UNION ALL
    SELECT 컬럼 계산식 FROM 테이블명 WHERE 제약조건
    )

예시

WITH RECURSIVE cte AS(
    SELECT 0 AS h
    UNION ALL
    SELECT h + 1 FROM cte WHERE h < 23
    )

SELECT * FROM CTE

아래와 같은 결과가 나옵니다. (23까지 출력)

이렇게 재귀 쿼리를 활용하는 이유는
예를 들어, 갖고 있는 테이블에는 7시부터 21시까지의 데이터만 존재할 때, 시간값으로 Group by 후 정렬해도
데이터가 갖고 있지 않은 시간대의 값은 출력할 수 없습니다.
그러나 만약 '0시'에는 몇개, '1시'에는 몇개 와 같이 데이터에 존재하지 않는 시간대를 표현하고 싶을 때
위와 같은 가상의 시간 테이블을 만들고 JOIN을 통해 데이터가 갖지 않는 시간대도 표현해줄 수 있습니다.

WITH RECURSIVE cte AS(
    SELECT 0 AS h
    UNION ALL
    SELECT h + 1 FROM cte WHERE h < 23
    )

SELECT c.h, COUNT(ANIMAL_ID)
FROM cte AS c
LEFT JOIN ANIMAL_OUTS AS a ON c.h = HOUR(a.DATETIME)
GROUP BY c.h;

아래와 같이 데이터가 갖고 있지 않은 시간대에도 카운트값을 0으로 표시하며 나타낼 수 있게 됩니다.

참고: 프로그래머스
참고: 재귀 쿼리 계층구조