프로그래머스의 SQL Kit GROUP BY 문제를 풀다가 생소한 문법을 알게 되었습니다.
입양 시각 구하기(1)같은 경우에는 값이 있는 부분만 출력하기에 쉽게 넘어갔지만 (2)는 처음 보는 것이었습니다.

WITH RECURSIVE TIME AS ( 
    SELECT 0 AS HOUR
    UNION ALL
    SELECT HOUR + 1
    FROM TIME
    WHERE HOUR < 23 
)
                
SELECT TIME.HOUR, COUNT(ANI.ANIMAL_ID)
FROM TIME
LEFT JOIN ANIMAL_OUTS AS ANI ON TIME.HOUR = HOUR(ANI.DATETIME)
GROUP BY TIME.HOUR

1. WITH

• CTE (Common Table Expression)을 생성하는 문법

1-1. CTE 정의

• CTE란 해당 SQL문 내에서만 존재하는 일시적인 테이블(결과의 집합)을 말합니다.
• 아래 sql문은 CTE인 CTE1 과 CTE2를 생성하고 이를 통해 JOIN연산과 SELECT연산을 합니다.

WITH
	CTE1 AS (SELECT a,b FROM test1)
    CTE2 AS (SELECT c,d FROM test2)
SELECT b,d FROM CTE1 JOIN CTE2
WHERE CTE1.a = CTE2.c;

• 다른 CTE를 통해 CTE를 정의하는 것도 가능합니다.

1-2.CTE의 열 이름

• 열의 이름은 다음처럼 정할 수 있습니다.

WITH cte (col1, col2) AS
(
  SELECT 1, 2
  UNION ALL
  SELECT 3, 4
)
SELECT col1, col2 FROM cte;

• 열의 이름을 WITH에서 정의하지 않으면 첫번째 SELECT문으로 정해집니다.

WITH cte AS
(
  SELECT 1 AS col1, 2 AS col2
  UNION ALL
  SELECT 3, 4
)
SELECT col1, col2 FROM cte;

2.Recursice CTE

• 서브쿼리에서 스스로를 참조하는 CTE입니다.

2-1.WITH RECURSIVE

WITH RECURSIVE cte (n) AS
(
    SELECT 1
    UNION ALL
    SELECT n + 1 FROM cte WHERE n < 5
)
SELECT * FROM cte;

• 재귀적 CTE의 서브쿼리는 UNION으로 구분된 2 파트로 나눠져 있습니다.

SELECT ...    -- 최초 행 반환 (non recursive)
UNION ALL
SELECT ...    -- 추가 행 반환 (recursive)

• 따라서 1~5를 출력한 위 sql문은 처음 1을 출력하는 SELECT문과 이후 5보다 작은 n에 재귀적으로 n + 1을 반복하는 두번째 SELECT문으로 구성되어 있습니다.
• 두번째 SELECT문이 더이상 행을 생성하지 않을때, 재귀(반복)는 끝이 납니다.
• 두 SELECT문 또한 여러 SELECT문의 유니온으로 구성할 수 있습니다.

SELECT ... UNION SELECT ...   -- 최초 행 반환 (non recursive)
UNION ALL
SELECT ... UNION SELECT ...   -- 추가 행 반환 (recursive)

2-2.재귀적 CTE의 데이터 크기

• 재귀적 CTE에서 행의 데이터 크기는 재귀적이지 않은 파트에 의해 정해집니다.
• 따라서 아래와 같은 sql문은 "abcabcabc..." 같은 문자열이 아니라 char(3)로 크기가 고정되어 "abc"만 남게됩니다.

WITH RECURSIVE cte AS
(
  SELECT 1 AS n, 'abc' AS str
  UNION ALL
  SELECT n + 1, CONCAT(str, str) FROM cte WHERE n < 3
)
SELECT * FROM cte;

• 이를 해결하기 위해선 재귀적이지 않은 파트에서 CAST를 통해 형 변환을 해줘야합니다.

WITH RECURSIVE cte AS
(
  SELECT 1 AS n, CAST('abc' AS CHAR(20)) AS str
  UNION ALL
  SELECT n + 1, CONCAT(str, str) FROM cte WHERE n < 3
)
SELECT * FROM cte;