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

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

 

지난 문제 '입양 시각 구하기(1)' 이랑 똑같은 문제인 줄 알았으나 아니었다!

SELECT EXTRACT(HOUR FROM CAST (DATETIME AS TIMESTAMP)) AS HOUR, COUNT(*) AS COUNT
FROM ANIMAL_OUTS
WHERE EXTRACT(HOUR FROM CAST (DATETIME AS TIMESTAMP)) BETWEEN 0 AND 23
GROUP BY EXTRACT(HOUR FROM CAST (DATETIME AS TIMESTAMP))
ORDER BY EXTRACT(HOUR FROM CAST (DATETIME AS TIMESTAMP))​

이렇게 하니

 

 

실패......

 

정답은!

 

이렇게 나와야한다!

 

방법은

0 to 23 hour 목록 생성 후 join

 

다른 블로거의 풀이를 따라했다.

대단하다.

 

SELECT R.lv HOUR, NVL(ANI.COUNT,0) COUNT
FROM 
	(SELECT EXTRACT(HOUR FROM CAST (DATETIME AS TIMESTAMP)) AS HOUR, COUNT(*) AS COUNT
	 FROM ANIMAL_OUTS
	 WHERE EXTRACT(HOUR FROM CAST (DATETIME AS TIMESTAMP)) BETWEEN 0 AND 23
	 GROUP BY EXTRACT(HOUR FROM CAST (DATETIME AS TIMESTAMP))
	 ORDER BY HOUR) ANI,
	(SELECT (LEVEL-1)lv FROM dual CONNECT BY LEVEL <=24)R
WHERE R.lv = ANI.HOUR(+) ORDER BY R.lv

내 코드

 

SELECT R.lv, NVL(E.cnt,0) 
    FROM
        (SELECT TO_CHAR(DATETIME,'HH24') as HOUR ,COUNT(*) cnt 
            FROM ANIMAL_OUTS GROUP BY TO_CHAR(DATETIME,'HH24')
                ORDER BY HOUR)E, 
        (SELECT (LEVEL-1)lv FROM dual CONNECT BY LEVEL <=24)R
WHERE R.lv = E.HOUR(+) ORDER BY R.lv

출처: https://blue-boy.tistory.com/195 [코딩배우는 학생]

 

 

ㅁ참고

[Oracle] dual 다중조회 (connect by level)

[PROGRAMMER Level-4] 입양 시각 구하기(2)[Oracle]