[Programmers] 입양 시각 구하기(2) (Lv.4)

https://chloe-chitchat.tistory.com/42?category=1108732 

[Programmers] 입양 시각 구하기(1) (Lv.2)

 

위 문제의 심화 버전이다.

 

 

왼쪽: Lv.2에선 이런 결과가 출력됐다.

오른쪽: Lv.4에선 이렇게 데이터가 없는 그룹까지 만들어야 한다.

 

Lv.4문제에 Lv.2쿼리를 적용해보면 7시-18시까지 나온다.

문제에선 0시-23시까지 출력하라 했다.

즉 문제에 있는 DATETIME 컬럼을 GROUP BY할게 아니라 HOUR 컬럼을 인위적으로 만들어야 한다.

 

• 방법1: SET으로 새로운 변수 선언
• 방법2: WITH RECURSIVE 재귀 쿼리로 새로운 테이블 생성

 

방법1: SET으로 새로운 변수 선언

SET @HOUR = -1;

SELECT (@HOUR := @HOUR +1) AS HOUR,
    (SELECT COUNT(ANIMAL_ID)
    FROM ANIMAL_OUTS
    WHERE HOUR(DATETIME)=@HOUR) AS COUNT
FROM ANIMAL_OUTS
WHERE @HOUR < 23;

SET + @ 는 새로운 변수를 선언하는 함수다. DML을 간단히 배우면서 들어본 적은 있었는데 이렇게 써봤다...

SET에서 := 는 대입연산자다. 그냥 =로 써도 SET절에선 알아서 비교연산자가 아닌 대입연산자로 이해하는거 같긴 한데 그래도 확실한 건 :=인가보다.

 

즉 쿼리를 뜯어보면,

SET절: -1로 셋팅

SELECT절: 0부터 시작

HOUR열: SET으로 만든 새로운 변수, 테이블에서 나온 것 아님

COUNT열: Lv.2에서 만든 쿼리, 테이블에서 나온 HOUR = SET으로 만든 HOUR로 조건 줘서 연결해주기

WHERE절: HOUR 반복 끊어주기... 이때 24 미만이 아니라 23 미만으로 해야함

 

 

방법2: WITH RECURSIVE 재귀 쿼리로 새로운 테이블 생성

 

WITH RECURSIVE 이라는 재귀 쿼리를 사용한 방법도 있다

 

우선 CTE(Common Table Expression)라는 게 있다... 메모리 상에 원하는 데이터를 일시적으로 저장해 테이블처럼 쓸 수 있다. 진짜 테이블 생성과는 다른데, 쿼리 결과의 집합을 가상의 테이블처럼 이름도 붙여주고 다른 쿼리 쓸때 부를 수도 있다. CTE을 생성하는 방법은 WITH문과 WITH RECURSIVE문 두가지가 있다. 재귀여부에 따라 사용하면 된다.

 

WITH문은 알고 있었고 사용할 줄 아니 RECURSIVE만 다뤄보겠다.

 

-- 형식
WITH RECURSIVE 테이블명 AS (
    SELECT -- 비반복문, 초기값 설정 위해
    UNION ALL -- 첫행과 앞으로의 반복을 잇기 위해
    SELECT -- 반복문, 반복 설정 위해
    WHERE -- 정지조건
)

 

-- 예시
WITH RECURSIVE num_table AS (
    SELECT 0 AS num
    UNION ALL
    SELECT num + 1 FROM num_table
    WEHRE num < 10 
)

0~10까지의 값을 갖는 테이블을 만드는 예시다.

첫 SELECT문에서 0을 만들고, 두번째 SELECT문에선 어떻게 반복할지 말하고, WHERE에서 정지조건을 줬다.

꼭 UNION ALL과 함께 사용한다거나 쿼리 안에 자기자신 테이블명을 참조한다는 특징이 있다.

 

-- 답안
WITH RECURSIVE rc AS (
	SELECT 0 AS hour
    UNION ALL
    SELECT hour + 1 
    FROM rc 
    WHERE hour < 23 )
    
SELECT rc.hour, COUNT(HOUR(ao.datetime)
FROM rc LEFT JOIN animal_outs ao ON rc.hour = HOUR(ao.datetime)
GROUP BY rc.hour

 재귀함수 이해만 된다면 난 이게 더 간단해보인다

위의 WITH RECURSIVE문으로 0부터 23까지의 rc.hour를 만들어줬고

밑의 쿼리에서 만든 hour와 입양테이블의 datetime 속 hour를 연결해줬다

 

 

혼자선 못 풀었을 문제다... 아무튼 프로그래머스도 이제 완결!