[MySQL] RECURSIVE 구문 활용해 배열 생성하기 예제 (feat. 빅쿼리 UNNEST)

프로그래머스의 SQL 코테를 풀다가 다음과 같은 문제가 나왔다. 먼저 데이터 'ANIMAL_OUTS'를 보면,

 

SELECT * 
FROM ANIMAL_OUTS 
LIMIT 10;

 

이러한 데이터에서, 시간대별 입양 건수를 조회하라는 문제였다.

즉, DATETIME 열의 HOUR 값에 따라 ANIMAL_ID를 카운트하면 되는데, 이게 그냥 GROUP BY를 사용하면 문제가 있었다.

 

SELECT HOUR(DATETIME), COUNT(ANIMAL_ID)
FROM ANIMAL_OUTS
GROUP BY 1
ORDER BY 1

 

위와 같이 시간대별 입양 건수가 있는 결과만 뽑혀나오는 문제가 생겼다. 

 

 

 

 

 

 

 

하지만 이 문제의 경우 왼쪽 사진과 같이 COUNT값이 0이어도 값이 0으로 나와야 했는데, 그냥 집계함수를 사용하면 0의 경우는 나오지 않았다.

 

 

그래서 사용한 것이 MySQL의 RECURSIVE 구문을 활용해 HOUR 값의 배열(0~23까지의 값)을 만들고,

이 값을 기준으로 HOUR별 COUNT 값을 LEFT JOIN시키는 방법을 사용했다.

 

 

 

 

 

 

 

이하 생략

먼저 HOUR 값을 0부터 23까지 만드는 코드는 다음과 같다.

WITH RECURSIVE num(HOUR) AS(
    SELECT 0
    UNION ALL
    SELECT HOUR+1 FROM num WHERE HOUR<23)
  
SELECT *
FROM num

 

이렇게 HOUR 열의 값 0부터 23까지의 배열이 만들어졌다.

이것을 빅쿼리에서 한다면 GENERATE_ARRAY와 UNNEST 구문을 사용해 만들어주면 된다. 

-- 빅쿼리용 코드
WITH hour_array AS(
  SELECT GENERATE_ARRAY(0,23,1) as hour
)

SELECT 
  hour
FROM hour_array, UNNEST(hour) as hour

 

하지만 이 테스트는 MySQL을 기반으로 하는 것이라 RECURSIVE를 이용했다.

그럼 이제 만들어진 HOUR 열에, 입양 건수를 카운트한 값을 LEFT JOIN시켜보자.

 

WITH RECURSIVE num(HOUR) AS(
    SELECT 0
    UNION ALL
    SELECT HOUR+1 FROM num WHERE HOUR<23)

SELECT A.HOUR, B.COUNT
FROM num A 
LEFT JOIN (SELECT HOUR(DATETIME) AS H, COUNT(ANIMAL_ID) AS COUNT
            FROM ANIMAL_OUTS
            GROUP BY 1) B ON A.HOUR = B.H
ORDER BY 1

 

그런데 이렇게 만든 경우엔 카운트 값이 없는 경우는 NULL로 나오는 문제가 있었다.

그래서 서브쿼리와 CASE WHEN을 사용해, NULL 값이면 0으로 채워주었다.

 

WITH RECURSIVE num(HOUR) AS(
    SELECT 0
    UNION ALL
    SELECT HOUR+1 FROM num WHERE HOUR<23)


SELECT sub.HOUR, 
    CASE WHEN sub.COUNT IS NOT NULL THEN sub.COUNT
    ELSE 0 END AS COUNT
FROM (SELECT A.HOUR, B.COUNT
        FROM num A 
        LEFT JOIN (SELECT HOUR(DATETIME) AS H, COUNT(ANIMAL_ID) AS COUNT
                    FROM ANIMAL_OUTS
                    GROUP BY 1) B ON A.HOUR = B.H) sub
ORDER BY 1

 

완성!

 

문제 출처: 프로그래머스 SQL 고득점 Kit - GROUP BY 입양 시각 구하기(2)