[SQL] 입양 시각 구하기(2)

Oracle로 해결하였습니다.

[PRG] 입양 시각 구하기

접근 방법

문제를 쪼개어서 접근하면 다음과 같습니다.

• DATETIME 에서 시간 추출
• 시간별로 동물 세기

시간 추출

시간을 추출하는 방법은 여러 방법이 있지만, 가장 쉬운 방법은 TO_CHAR(DATETIME, 'HH24') 를 사용하는 것 입니다. 주의할 것은 시간을 추출할 때 'HH' 가 아닌 'HH24' 를 사용하여 24시간을 표현해야 하는 것입니다. 이렇게 해야 [0, 24) 의 값이 나오게 됩니다.

마침 과거에 간단한 문법을 제가 정리한 내용이 있어 이를 첨부합니다.

시간별로 동물 세기

이는 기본적인 GROUP BY 를 시간에 대해서 진행하면 됩니다.

현재까지 정리한 내용을 SQL로 나타내면 다음과 같습니다.

  SELECT TO_CHAR(DATETIME, 'HH24') AS HOUR,
      COUNT(ANIMAL_ID) AS COUNT
  FROM ANIMAL_OUTS
  GROUP BY TO_CHAR(DATETIME, 'HH24')

문제점

하지만 문제가 있습니다. 바로, [0, 24) 까지의 모든 시간이 반영되어 있지 않다는 점입니다.

만약 0시 기록이 없을 경우 COUNT 를 0으로 처리해두어야 하는데, 애초에 시간 데이터 자체가 없어서 표시 자체를 해둘 수가 없습니다.

뭔가 저 데이터와 상관없이 [0, 24) 를 모두 가지는 테이블 하나가 있으면 좋겠다는 생각이 듭니다.

만드는 방법에는 직접 작성하는 방법도 있긴 하겠지만 너무 귀찮습니다. 그렇다고 PL/SQL 까지 가는 것도 무겁습니다. 바로 이 때 재귀 쿼리를 생성하면 됩니다.

재귀 쿼리 역시 정리한 것이 있어 이 포스팅을 참고하면 됩니다. 다만 해당 포스팅은 MySQL 기반이라 Oracle 기반은 여기를 참고하면 됩니다.

Oracle 계층형 쿼리

MySQL은 CTE + RECURSIVE 를 사용하는데, Oracle은 CONNECT BY 를 사용합니다. 사용 예시를 [0, 24) 를 모두 가지는 테이블인 TIME_TABLE 을 만들어서 해결해보겠습니다.

WITH TIME_TABLE AS (
    SELECT LEVEL - 1 AS HOUR
    FROM DUAL
    CONNECT BY LEVEL <= 24
)

사실 이 CONNECT BY 는 무려 CTE 없이도 사용 가능합니다!

SELECT column1, column2, ...
FROM table_name
START WITH condition
CONNECT BY [PRIOR] condition

기본적인 문법은 다음과 같이 사용되고, 해당 쿼리를 CTE 말고 단일 쿼리든, 서브 쿼리든 어디든 사용이 가능합니다. Oracle이 CTE 제공 이전부터 계층형 쿼리가 필요할 때 제공하던 강력한 문법이고, 다른 RDBMS에서는 적용이 되지 않으므로 주의하기 바랍니다.

START WITH

START WITH MGR_ID IS NULL --MGR_ID 가 NULL인 ROW를 최상위로 시작

트리에서 루트 노드 설정하는 것처럼 계층 구조에서 최상위 ROW를 선정하는 조건입니다.

CONNECT BY

CONNECT BY PRIOR 부모노드 = 자식노드  
CONNECT BY 자식노드 = PRIOR 부모노드

어떻게 연결되어 있는지를 명시해주는 부분입니다. 기본적으로 자식은 PRIOR 부모라 생각하시면 편합니다.

유용한 함수들

이런 CONNECT BY 와 활용하기좋은 함수들이 몇가지 있는데, 저의 경우에는 LEVEL을 사용해서 계층의 깊이를 표시하였습니다. 이 문제에서는 필수긴 하지만, 알고리즘 풀 때도 깊이 정보 정도는 마킹하는 만큼 습관이 되면 좋습니다.

LEVEL

현재 계층의 깊이를 나타냅니다.

SELECT LEVEL, 'Level ' || LEVEL AS DESCRIPTION
FROM DUAL
CONNECT BY LEVEL <= 5;

SYS_CONNECT_BY_PATH

루트부터 현재 노드까지의 경로를 보여줍니다.

SELECT EMP_NAME,
       SYS_CONNECT_BY_PATH(EMP_NAME, ' -> ') AS PATH
FROM EMPLOYEES
START WITH MGR_ID IS NULL
CONNECT BY PRIOR EMP_ID = MGR_ID;

CONNECT_BY_ROOT

루트 노드의 값을 가져옵니다.

SELECT EMP_NAME,
       CONNECT_BY_ROOT EMP_NAME AS TOP_MANAGER
FROM EMPLOYEES
START WITH MGR_ID IS NULL
CONNECT BY PRIOR EMP_ID = MGR_ID;

예시 답안

WITH TIME_TABLE AS (
    SELECT LEVEL - 1 AS HOUR
    FROM DUAL
    CONNECT BY LEVEL <= 24
), ANIMAL_LIST AS (
	SELECT TO_CHAR(DATETIME, 'HH24') AS HOUR,
        COUNT(ANIMAL_ID) AS COUNT
    FROM ANIMAL_OUTS
    GROUP BY TO_CHAR(DATETIME, 'HH24')
)

SELECT T.HOUR,
    NVL(L.COUNT, 0) AS COUNT
FROM TIME_TABLE T
LEFT JOIN ANIMAL_LIST L ON (T.HOUR = L.HOUR)
ORDER BY HOUR

NULL 처리는 Oracle 제공인 NVL 을 사용하였지만, 다음과 같은 COALESCE 사용을 추천하고 있습니다.

-- COALESCE(expr1, expr2, expr3, ...)
-- 첫 번째 NULL이 아닌 값을 반환
-- 여러 개의 표현식 중에서 선택 가능
WITH TIME_TABLE AS (
    SELECT LEVEL - 1 AS HOUR
    FROM DUAL
    CONNECT BY LEVEL <= 24
), ANIMAL_LIST AS (
	SELECT TO_CHAR(DATETIME, 'HH24') AS HOUR,
        COUNT(ANIMAL_ID) AS COUNT
    FROM ANIMAL_OUTS
    GROUP BY TO_CHAR(DATETIME, 'HH24')
)

SELECT T.HOUR,
    COALESCE(L.COUNT, 0) AS COUNT
FROM TIME_TABLE T
LEFT JOIN ANIMAL_LIST L ON (T.HOUR = L.HOUR)
ORDER BY HOUR