[SQL] 프로그래머스 Lv.4 입양 시각 구하기(2)

73. 보호소에서는 몇 시에 입양이 가장 활발하게 일어나는지 알아보려 합니다. 0시부터 23시까지, 각 시간대별로 입양이 몇 건이나 발생했는지 조회하는 SQL문을 작성해주세요. 이때 결과는 시간대 순으로 정렬해야합니다.

 

먼저 입양일을 시간대(HOUR) 별로 그룹화하여 입양 건수를 COUNT 해주는 SQL문을 작성해 봤다.

하지만 값이 존재하는 7-19시까지의 데이터만 출력되었다. 문제에서 요구하는 대로 0시부터 23시까지 모두 출력하려면 0부터 23까지의 값이 존재하는 테이블을 만든 후 JOIN을 해야 한다.

-- HOUR가 7~19까지만 출력

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

 

UNION을 사용해 0부터 23까지 하나씩 입력하여 테이블을 만든 후 시간대별로 입양 건수를 COUNT 한 테이블과 LEFT JOIN 한 뒤 시간 순으로 정렬해 주었다. 이때 COUNT 값이 없을 경우 0을 출력해 주었다.

정답이긴 하지만 쿼리문이 상당히 길어진다.

-- UNION 사용

WITH h AS (
SELECT 0 AS HOUR
UNION SELECT 1
UNION SELECT 2
UNION SELECT 3
UNION SELECT 4
UNION SELECT 5
UNION SELECT 6
UNION SELECT 7
UNION SELECT 8
UNION SELECT 9
UNION SELECT 10
UNION SELECT 11
UNION SELECT 12
UNION SELECT 13
UNION SELECT 14
UNION SELECT 15
UNION SELECT 16
UNION SELECT 17
UNION SELECT 18
UNION SELECT 19
UNION SELECT 20
UNION SELECT 21
UNION SELECT 22
UNION SELECT 23)

SELECT h.HOUR, COALESCE(o.COUNT, 0) COUNT
FROM h LEFT JOIN (SELECT HOUR(DATETIME) HOUR, COUNT(ANIMAL_ID) COUNT
                  FROM ANIMAL_OUTS GROUP BY 1) o ON h.HOUR=o.HOUR
ORDER BY 1

 

쿼리문을 간결하게 만들기 위해서는 다음과 같은 방법으로 0부터 23까지의 테이블을 만들 수 있다.

 

첫 번째로 사용자 정의 변수 SET을 사용하는 방법이다.

• SET @변수명 = 변수 값;   >> 변수 설정
• SET 이외에 SELECT에 변수를 할당할 때는 = 대신 := 을 사용

먼저 사용자 변수 @H를 설정했다. +1 씩 증가시켜 0부터 23까지 나타낼 거라 -1로 설정해 주었다.

다음으로 @H가 1씩 증가하는 시간대 컬럼을 만들고, 입양일의 시간과 @H가 일치할 때의 입양 건수를 COUNT 하여 조회해 주었다. 시간대는 23까지 출력해야 하므로 @H<23 으로 필터링해주었다.

-- 사용자 정의 변수 사용

SET @H = -1;

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

 

두 번째는 재귀함수(WITH RECURSIVE문)를 사용하는 방법이다.

• 재귀함수는 CTE를 정의하면서 재귀적으로 자기 자신을 참조할 수 있는 함수

• -- 0부터 10까지 출력하는 재귀함수 예시
  
  WITH RECURSIVE 테이블명 AS(
      SELECT 0 AS NUM # 초기값 설정
      UNION ALL
      SELECT NUM+1 FROM CTE
      WEHRE NUM < 10 # 반복을 멈추는 조건
  )​

   

재귀함수를 사용해 0부터 23까지의 값을 가진 테이블을 만들어주고 이후는 위와 동일하게 입양 건수 COUNT를 LEFT JOIN으로 병합시켜 주고 COUNT 값이 NULL인 경우 0을 출력시켜 주었다.

-- 재귀함수 사용

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

SELECT h.HOUR, COALESCE(o.COUNT, 0) COUNT
FROM h LEFT JOIN (SELECT HOUR(DATETIME) HOUR, COUNT(ANIMAL_ID) COUNT
                  FROM ANIMAL_OUTS GROUP BY 1) o ON h.HOUR=o.HOUR
ORDER BY 1

 

해당 문제를 Oracle로 풀기 위해서는 계층형 쿼리를 사용하면 된다.

• 계층형 쿼리 : Oracle에서 사용되는 특수한 구문으로 계층형 데이터를 조회하기 위한 쿼리
• 계층형 데이터 : 동일 테이블에서 계층적으로 상위와 하위 데이터가 포함된 데이터
• 계층형 쿼리 형태

START WITH 조건
CONNECT BY [NOCYCLE] 조건
[ORDER SIBLINGS BY 컬럼];

• START WITH : 계층형 데이터 시작점 지정
• CONNECT BY : 부모-자식 관계를 갖는 데이터를 쿼리

0시부터 23시까지의 시간대를 출력하기 위해선 CONNECT BY LEVEL을 이용해야한다.

• LEVEL : 현재 쿼리 결과에서 행이 어떤 계층에 속하는지 나타내는 가상의 열, 1부터 시작
• DUAL : 단일 행과 단일 열을 가지고 있는 가상의 테이블

-- 0부터 24까지 만들기

SELECT LEVEL-1 AS HOUR
FROM DUAL 
CONNECT BY LEVEL <= 24;

-- Oracle

SELECT hour, count(o.datetime) count
FROM
(SELECT LEVEL-1 AS HOUR
 FROM DUAL
 CONNECT BY LEVEL <= 24) a LEFT JOIN animal_outs o on a.hour = to_char(o.datetime, 'HH24')
GROUP BY hour
ORDER BY hour

 

프로그래머스

---

⬇️ 참고 블로그

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