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

프로그래머스 코딩테스트 연습 SQL Level 4

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

• Database에 없는 시간까지 출력해야 하는 문제 없는사람 손들어 같은..

질문 목록을 살펴보니 WITH RECURSIVE, SET, 서브쿼리 등을 이용한 풀이가 있어서 하나씩 따라해 보았다.

WITH RECURSIVE

MySQL 5.7버전 이상에서만 사용 가능한 계층형 쿼리
WITH RECURSIVE [변수] AS ()

WITH RECURSIVE TIMETALE AS (SELECT 0 AS HOUR
                                UNION ALL
                            SELECT HOUR+1
                                FROM TIMETALE
                                WHERE HOUR < 23)
SELECT TIMETALE.HOUR, COUNT(HOUR(AO.DATETIME)) AS COUNT
    FROM TIMETALE
    LEFT JOIN ANIMAL_OUTS AS AO
    ON TIMETALE.HOUR = HOUR(AO.DATETIME)
GROUP BY TIMETALE.HOUR
ORDER BY TIMETALE.HOUR;

SET

SQL에서 SET @[변수명] = ['값'] 또는 SET @[변수명] := ['값'] 으로 변수를 선언할 수 있다.
SELECT 문에서는 =만 가능하다고 한다. 세미콜론도 꼭 해야함.

SET @hour := -1; -- 변수 선언

SELECT (@hour := @hour + 1) as HOUR,
(SELECT COUNT(*) FROM ANIMAL_OUTS WHERE HOUR(DATETIME) = @hour) as COUNT
FROM ANIMAL_OUTS
WHERE @hour < 23

UNION / UNION ALL (서브쿼리)

2개 이상의 쿼리를 결합할 때 사용
UNION 결합 시 중복 제거
UNION ALL 모든 결과를 출력

SELECT H1.HOUR, IFNULL(OUTS.COUNT,0) AS COUNT 
	FROM ( 
    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 ) H1 
LEFT JOIN ( 
SELECT HOUR(DATETIME) AS 'HOUR'
, COUNT(*) AS 'COUNT' FROM ANIMAL_OUTS 
GROUP BY HOUR
) AS OUTS ON H1.HOUR = OUTS.HOUR

JSON_TABLE Functions

사용법: Reference 참고
JSON_TABLE(expr, path COLUMNS (column_list) [AS] alias)

SELECT
  HOURS.HOUR, 
  IFNULL(AO.CNT, 0) AS COUNT
FROM
  JSON_TABLE(
      "[0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23]",
      '$[*]' COLUMNS (HOUR INT PATH '$')
  ) HOURS
 LEFT JOIN (
    SELECT 
      HOUR(DATETIME) as HOUR, 
      COUNT(*) as CNT
    FROM ANIMAL_OUTS 
    GROUP BY HOUR(DATETIME)
) AS AO ON AO.HOUR = HOURS.HOUR