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

문제

programmers.co.kr/learn/courses/30/lessons/59413

코딩테스트 연습 - 입양 시각 구하기(2)

풀이

GROUP BY를 이용하여 시간대별로 데이터의 개수를 세는 것은 어렵지 않으나 데이터가 없는 시간에 대해서도 0을 출력해야 한다.

 

이를 위해서 0~23을 데이터로 가지는 테이블을 만들어야 하며 이를 ANIMAL_OUTS 테이블과 JOIN으로 연결해야 한다. WITH를 사용하여 Common Table 표현식을 사용하였다.

WITH
  테이블이름1(A, B) AS (SELECT 컬럼1, 컬럼2 FROM 테이블 이름),
  테이블이름2(C, D) AS (SELECT 컬럼3, 컬럼4 FROM 테이블 이름),
  테이블이름3 AS (SELECT 컬럼5, 컬럼6 FROM 테이블 이름)
  
SELECT A, D
FROM 테이블이름1
JOIN 테이블이름2
WHERE 테이블이름1.A = 테이블이름2.C;

테이블에서 열 이름 결정은 괄호로 묶인 이름 목록으로 결정되며 이름 목록이 생략된 경우, SELECT절의 컬럼명으로 결정된다.

이를 이용하여 아래와 같이 표현하면 0~23을 데이터로 가지는 테이블을 얻을 수 있다.

WITH TB_HOUR(HOUR) AS(
  SELECT 1  UNION ALL
  SELECT 2  UNION ALL
  SELECT 3  UNION ALL
  SELECT 4  UNION ALL
  SELECT 5  UNION ALL
  SELECT 6  UNION ALL
  SELECT 7  UNION ALL
  SELECT 8  UNION ALL
  SELECT 9  UNION ALL
  SELECT 10 UNION ALL
  SELECT 11 UNION ALL
  SELECT 12 UNION ALL
  SELECT 13 UNION ALL
  SELECT 14 UNION ALL
  SELECT 15 UNION ALL
  SELECT 16 UNION ALL
  SELECT 17 UNION ALL
  SELECT 18 UNION ALL
  SELECT 19 UNION ALL
  SELECT 20 UNION ALL
  SELECT 21 UNION ALL
  SELECT 22 UNION ALL
  SELECT 23 UNION ALL
  SELECT 24)
SELECT * FROM TB_HOUR;

SQL문에서도 재귀를 이용할 수 있다. 다음과 같은 쿼리를 작성하면 0~23을 데이터로 가지는 테이블을 얻을 수 있다.

WITH RECURSIVE TB_HOUR(HOUR) AS (
  SELECT 0 # 초기 데이터
  UNION ALL
  SELECT HOUR+1 FROM TB_HOUR WHERE HOUR<23 # 추가되는 데이터
)

코드

-- 공식 문서: https://dev.mysql.com/doc/refman/8.0/en/subqueries.html
-- WITH : 공통 테이블 선언식
-- 재귀 RECURSIVE
-- 1에서 24까지
WITH RECURSIVE TB_HOUR(HOUR) AS (
  SELECT 0
  UNION ALL
  SELECT HOUR+1 FROM TB_HOUR
    WHERE HOUR<23
)
#SELECT * FROM TB_HOUR;

-- 검색
SELECT TB_HOUR.HOUR, COUNT(ANIMAL_OUTS.ANIMAL_ID) AS COUNT
FROM ANIMAL_OUTS
RIGHT OUTER JOIN TB_HOUR 
ON HOUR(ANIMAL_OUTS.DATETIME) = TB_HOUR.HOUR
GROUP BY TB_HOUR.HOUR;

 

참고자료

dev.mysql.com/doc/refman/8.0/en/with.html

MySQL :: MySQL 8.0 Reference Manual :: 13.2.15 WITH (Common Table Expressions)