(프로그래머스 SQL 문제 풀이) 입양 시각 구하기(1), (2)

SELECT HOUR(DATETIME) AS HOUR, COUNT(*) AS COUNT FROM ANIMAL_OUTS
WHERE HOUR(DATETIME) BETWEEN 9 AND 19 # HOUR(DATETIME) >= 9 AND HOUR(DATETIME) <= 19
GROUP BY HOUR
ORDER BY HOUR

---

SET @TIME := -1;

SELECT (@TIME := @TIME + 1) AS HOUR, 
(SELECT COUNT(*) FROM ANIMAL_OUTS WHERE HOUR(DATETIME) = @TIME) AS COUNT
FROM ANIMAL_OUTS
WHERE @TIME < 23
ORDER BY HOUR;

MySQL에선 SET을 통해 사용자 정의 변수를 선언할 수 있다. @가 붙은 변수는 프로시저가 종료되어도 유지되므로 값이 누적된다. 파이썬 함수에서 global을 통해 전역 변수화하는 것과 비슷하다.

SET @var = 1 또는 SET @var := 5;
SELECT @var := 1

위에선 TIME이란 변수에 1을 추가해주면서 23이 될 때까지 반복해주면 된다. 변수를 선언하는 것 이외에도 다른 방법이 있어 정리해본다.

SQLD를 준비하면서도 봤던 WITH RECURSIVE 구문이다. 아래 쿼리문은 Carvin.log 님의 글을 참고했다.

WITH RECURSIVE HOUR AS( # 가상의 테이블 HOUR
SELECT 0 AS h # non-recursive
UNION ALL 
SELECT h+1 FROM HOUR WHERE h < 23); # HOUR 테이블 참조

SELECT h AS HOUR, COALESCE(COUNT(ANIMAL_ID), 0) AS COUNT
FROM HOUR LEFT JOIN ANIMAL_OUTS ANI ON HOUR.h = HOUR(ANI.DATETIME)
GROUP BY HOUR.h;

WITH RECURSIVE 정리(출처: Unreal Engine Study)

1. 메모리 상에 가상의 테이블을 저장
2. 반드시 UNION 사용
3. 반드시 비반복문도 최소한 1개 요구됨
4. 서브커리에서 바깥의 가상의 테이블을 참조하는 문장(반복문)이 반드시 필요함
5. 반복되는 문장은 반드시 정지조건이 요구됨
6. 가상의 테이블을 구성하면서 그 자신(가상의 테이블)을 참조하여 값을 결정할 때 유용함

WITH 구문은 WITH 절에 포함된 서브쿼리 결과를 이름을 붙여 메모리에 저장하고 외부의 메인 쿼리에서 해당 이름을 이용하여 마치 테이블처럼 사용할 수 있다. 따라서 가상의 테이블이라고도 한다.

COALESCE()는 주어진 인자에서 NULL이 아닌 첫 번째 값을 반환하는 함수다. 즉 COUNT(ANIMAL_ID)가 NULL일 경우, 0을 반환하도록 하는 것이다.