프로그래머스 - 입양 시각 구하기(2)

SQL을 공부하고 있던 와중에 프로그래머스에도 SQL 문제들이 있다는 걸 발견했다. 쭉쭉 풀다가 GROUP BY 입양 시각 구하기(2) 에서 막혔다. 느낌적으로는 0~23으로 되어있는 테이블과 ANIMAL_OUTS를 group by 한 테이블을 조인하면 될 거 같다. 풀고 나서 이해한 내용을 MySQL, Oracle로 정리했다.

MySQL

0~23 numerical cte 를 만드는 다양한 방법 중 2가지

-- MySQL의 VALUES를 이용한 방법
WITH hronly (hour) AS (
    VALUES ROW(0),ROW(1),ROW(2),ROW(3),ROW(4),ROW(5),
           ROW(6),ROW(7),ROW(8),ROW(9),ROW(10),ROW(11),
           ROW(12),ROW(13),ROW(14),ROW(15),ROW(16),ROW(17),
           ROW(18),ROW(19),ROW(20),ROW(21),ROW(22),ROW(23)
)

-- recursive cte를 활용한 방법
WITH RECURSIVE hronly AS (
    SELECT 0 AS hour
    UNION ALL
    SELECT hour+1 FROM hronly
    WHERE hour<23
)

이 문제를 처음 풀 때 cte 2개를 만들었다. 근데 cte 한 개로도 풀 수 있다는 걸 나중에 알았다.

MySQL VALUES 풀이

-- two cte using VALUES
WITH hronly (hour) AS (
    VALUES ROW(0),ROW(1),ROW(2),ROW(3),ROW(4),ROW(5),
           ROW(6),ROW(7),ROW(8),ROW(9),ROW(10),ROW(11),
           ROW(12),ROW(13),ROW(14),ROW(15),ROW(16),ROW(17),
           ROW(18),ROW(19),ROW(20),ROW(21),ROW(22),ROW(23)
),
    hrandcount AS (
    SELECT HOUR(DATETIME) AS hour, COUNT(1) AS count
    FROM ANIMAL_OUTS
    GROUP BY hour
    ORDER BY hour
)
-- NULL를 0으로 바꾸기 위해 COALESCE를 사용했다
SELECT hronly.hour, COALESCE(count,0) AS count
FROM hrandcount
RIGHT JOIN hronly ON hrandcount.hour = hronly.hour

Recursive cte 풀이

-- one recursive cte
WITH RECURSIVE hronly AS (
    SELECT 0 AS hour
    UNION ALL
    SELECT hour+1 FROM hronly
    WHERE hour<23
)
-- 0~23 hour를 조인하고 ID로 count 하기
SELECT hour, COUNT(ANIMAL_ID) AS count
FROM ANIMAL_OUTS
RIGHT JOIN hronly ON HOUR(DATETIME) = hour 
GROUP BY hour
ORDER BY hour

Oracle

0~23 numerical cte 를 만드는 다양한 방법 중 2가지

-- Oracle built-in collection
WITH hronly AS (
    SELECT column_value AS hour
    FROM TABLE(sys.ODCIVarchar2List(0,1,2,3,4,5,6,7,8,9,10,11,12,
                                    13,14,15,16,17,18,19,20,21,22,23))
)

-- using connect by level
WITH hronly AS (
    SELECT level-1 AS hour FROM dual
    CONNECT BY level < 25
)

마찬가지로 cte 2개로 풀 수도 있고 한 개로 풀 수도 있다.

Built-in collection 풀이

-- two cte using sys.ODCIVarchar2List
WITH hronly AS (
    SELECT column_value AS hour
    FROM TABLE(sys.ODCIVarchar2List(0,1,2,3,4,5,6,7,8,9,10,11,12,
                                    13,14,15,16,17,18,19,20,21,22,23))
),
    hrandcount AS (
    SELECT hour, COUNT(1) AS count
    FROM (SELECT EXTRACT(HOUR FROM CAST(DATETIME AS TIMESTAMP)) AS hour 
          FROM ANIMAL_OUTS)
    GROUP BY hour
    ORDER BY hour
)
SELECT hronly.hour, COALESCE(count,0) AS count
FROM hrandcount 
RIGHT JOIN hronly ON hronly.hour = hrandcount.hour

Connect by level 풀이

-- one cte using connect by level
WITH hronly AS (
    SELECT level-1 AS hour FROM dual
    CONNECT BY level < 25
)
SELECT hour, COUNT(ANIMAL_ID) AS count
FROM ANIMAL_OUTS
RIGHT JOIN hronly ON EXTRACT(HOUR FROM CAST(DATETIME AS TIMESTAMP)) = hour
GROUP BY hour
ORDER BY hour

Recursive cte를 이용한 방법이 제일 깔끔한 거 같다. 다음에는 recursive cte를 다양하게 사용할 수 있는 방법들을 찾아봐야겠다. 그리고 생각보다 RBDMS 마다 syntax가 많이 다른거 같다는 생각이 든다.

MySQL#

Oracle#

MySQL

Oracle