[SQL][프로그래머스]입양 시각 구하기(2) - SUBQUERY + UNION ALL + GROUP BY + COUNT AS + WITH RECURSIVE CTE + CONNECT BY MYSQL ORACLE

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

프로그래머스

코드설명

SUBQUERY + UNION ALL + GROUP BY + COUNT 를 활용합니다.

 

문제에서 특이한점은, 없는 데이터도 표시해야한다는 것 입니다.

그렇기에 아래에 임시테이블을 하나 만들어주고, 이 테이블에 LEFT OUTER JOIN을 통해서 각 시간대에 ANIMAL_OUTS 데이터들을 붙여줍니다. 

( SELECT 0 AS DATETIME
        UNION ALL 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 )

 

해당 테이블에 JOIN 되지 않은 레코드들의 갯수는 0 개이므로 COUNT(*) 값을 출력하면 됩니다.

 

현재는 0시~23시이기에 총 24개만 처리하면 되었지만, 만약 24개가 아니라 1000개면 어떻게 할까요?

 WITH RECURSIVE TIME_RANGE AS (
     SELECT 0 AS DATETIME
     UNION ALL
     SELECT DATETIME + 1 FROM TIME_RANGE WHERE DATETIME < 23
 )
 
         
SELECT * FROM TIME_RANGE

 

이 RECURSIVE 위의 코드가 어떻게 이루어져있는지 분석해보겠습니다.

RECURSIVE CTE는 두 부분으로 구성됩니다.

1. 앵커 멤버(기준점), 즉 초기행을 생성합니다.

2. 재귀 멤버( : 앵커 멤버를 기반으로 추가행을 생성합니다.

 

위의 부분을 기준으로 위의 코드를 나눠봤습니다.

1.앵커멤버는 DATETIME이고, 초기행으로써  DATETIME 을 0 으로 설정합니다.

SELECT 0 AS DATETIME

 

2. 재귀 멤버 부분으로써, 앵커 멤버 DATETIME을 기반으로 아래 부분이 반복적으로 실행됩니다.

DATETIME + 1 은 이전 값에 1을 더하고, WHERE DATETIME <23 은 DATETIME이 23전까지 실행되도록 처리합니다.

UNION ALL
SELECT DATETIME + 1 FROM TIME_RANGE WHERE DATETIME < 23

 

 

코드

 SELECT 
    TEMPTABLE.DATETIME AS HOUR,
    COUNT(AO.ANIMAL_ID) AS COUNT
 FROM  ( SELECT 0 AS DATETIME
        UNION ALL 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 ) AS TEMPTABLE
LEFT OUTER JOIN ANIMAL_OUTS AS AO
ON HOUR(AO.DATETIME) = TEMPTABLE.DATETIME
GROUP BY TEMPTABLE.DATETIME

 

위의 0부터 23까지를 WITH RECURSIVE 로 임시테이블을 만들어서 변수로 손쉽게 생성합니다.

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

 

ORACLE

ORACLE의 CONNECT BY를 활용합니다.

이떄 LEVEL을 행의 개수라고 생각하면 됩니다.

 

또, COUNT값에서 만약 COUNT(*) 로 할경우 레코드의 개수로 세기 때문에, NULL언 ANIAML_OUTS 레코드도 세개 됩니다. 그러므로 COUNT(AO.ANIMAL_ID)로 처리해서 집계함수가 자동으로 NULL이 아닌것은 세지않도록 합니다.(레코드를 세느냐 아니면 컬럼의 필드값을 세느냐의 차이입니다.)

WITH TIMERANGE AS (
    SELECT LEVEL - 1 AS DATETIME
FROM DUAL
CONNECT BY LEVEL <= 24)

SELECT TR.DATETIME, COUNT(AO.ANIMAL_ID)
FROM TIMERANGE TR
LEFT OUTER JOIN ANIMAL_OUTS AO
ON TR.DATETIME = TO_CHAR(AO.DATETIME, 'HH24')
GROUP BY TR.DATETIME
ORDER BY TR.DATETIME ASC

 

 

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

SELECT TT.HOUR, COUNT(AO.ANIMAL_ID)
FROM TEMPTABLE TT
LEFT OUTER JOIN ANIMAL_OUTS AO
ON TT.HOUR = TO_CHAR(AO.DATETIME, 'HH24')
GROUP BY TT.HOUR
ORDER BY TT.HOUR ASC