[SQL]SQL반복문_재귀적 CTE(WITH RECURSIVE)

WITH CTE(Common Table Ezpression) : 재귀 쿼리, 계층 구조 쿼리, 크리 구조 쿼리

재귀 CTE쿼리란 : 자체 이름을 참조하는 하위 쿼리가 있는 CTE이다. 특히 시리즈 생성이나 계층적 구조의 데이터를 순회할 때 사용된다.

• MySQL :: MySQL 8.4 Reference Manual :: 15.2.20 WITH (Common Table Expressions)

시리즈 생성

예시) 1~10까지의 정수 시리즈

WITH RECURSIVE natural_sequence AS ( -- 재귀적 공통 테이블을 natural_sequence에 저장
  SELECT 1 AS n		-- 1을 선택하여 n이라고 정의
  UNION ALL		-- 두 개의 select 쿼리 결과를 합친다. all은 중복된 값을 허용하는 옵션. 재귀 쿼리에서는 첫 번째 결과와 두번째 쿼리 결과를 결합한다.
  SELECT n + 1 FROM natural_sequence	-- 이전 값에 1을 더한 값을 반환
  WHERE n < 10		-- 재귀 종료 조건
)
SELECT * 
FROM natural_sequence;

실행 결과

 

예시) 0시부터 23시까지, 각 시간대별로 입양이 몇 건이나 발생했는지 조회(0건 발생한 시간대도 출력)

https://school.programmers.co.kr/learn/courses/30/lessons/59413

프로그래머스

 

-- 중간과정 : 0시부터 23시까지의 테이블 작성
WITH RECURSIVE hours AS (
	SELECT 0 AS n
	UNION ALL
	SELECT  n + 1 FROM hours
	WHERE n < 23
)
SELECT *
FROM hours;

중간과정 실행결과

>> 위 테이블과 animal_outs 테이블을 join해 Null 값을 coalesce로 표시

-- 정답
WITH RECURSIVE hours AS (
	SELECT 0 AS HOUR
	UNION ALL
	SELECT  HOUR + 1 FROM hours
	WHERE HOUR < 23
)
SELECT hours.hour, COALESCE(COUNT(animal_id)) AS 'COUNT'
FROM hours 
LEFT JOIN animal_outs ao ON hours.hour = HOUR(ao.datetime)
GROUP BY hours.hour
ORDER BY hours.hour;

 

 

날짜 순서

예시 테이블

>> 2022-02-03, 2022-02-05의 데이터도 보고 싶다면

WITH RECURSIVE dates(date) AS (
	SELECT '2020-02-01'
    UNION ALL
    SELECT date + INTERVAL 1 DAY
    FROM dates
    WHERE date < '2022-02-07')
SELECT dates.date, COALESCE(SUM(price),0) sales
FROM dates LEFT JOIN sales ON dates.date = sales.order_date
GROUP BY dates.date;

 

출력 예시

 

 

 

참고 블로그 : https://jjon.tistory.com/entry/Recursive-CTECommon-Table-Expression-%ED%99%9C%EC%9A%A9

Recursive CTE(Common Table Expression) 활용