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

문제 설명

ANIMAL_OUTS 테이블은 동물 보호소에서 입양 보낸 동물의 정보를 담은 테이블입니다. ANIMAL_OUTS 테이블 구조는 다음과 같으며, ANIMAL_ID, ANIMAL_TYPE, DATETIME, NAME, SEX_UPON_OUTCOME는 각각 동물의 아이디, 생물 종, 입양일, 이름, 성별 및 중성화 여부를 나타냅니다.

 

NAME	TYPE	NULLABLE
ANIMAL_ID	VARCHAR(N)	FALSE
ANIMAL_TYPE	VARCHAR(N)	FALSE
DATETIME	DATETIME	FALSE
NAME	VARCHAR(N)	TRUE
SEX_UPON_OUTCOME	VARCHAR(N)	FALSE

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

 

예시

SQL문을 실행하면 다음과 같이 나와야 합니다.

 

HOUR	COUNT
0	0
1	0
2	0
3	0
4	0
5	0
6	0
7	3
8	1
9	1
10	2
11	13
12	10
13	14
14	9
15	7
16	10
17	12
18	16
19	2
20	0
21	0
22	0
23	0

 

풀이

 

 

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

 

본 문제를 풀기 위해서는 LEVEL 함수를 알고 있는 것이 편한데 이런 원리로 작동한다.

SELECT LEVEL FROM dual CONNECT BY LEVEL <=5;

순차적 목록을 만드는 데 굉장히 유용하다. 이것의 실행 코드는

LEVEL

1

2

3

4

5

 

 

SELECT TO_CHAR(
        TO_DATE(sysdate,'YY-MM-DD')+LEVEL-1, 'YY-MM-DD') 
FROM dual CONNECT by level <= 10;
 

오늘 날짜를 출력하는 sysdate에 LEVEL이 1부터 시작하므로 1을 빼줘서 +0이 되게 만들고 이를 TO_DATE로 날짜형으로 변환한다. 이후 이것을 문자열로 다시한번 현변환 한뒤 CONNECT BY LEVEL 함수로 10일 뒤까지 출력이 된다.

20-03-29
20-03-30
20-03-31
20-04-01
20-04-02
20-04-03
20-04-04
20-04-05
20-04-06
20-04-07

 

 

이를 통해 다시한번 코드를 보면

1 2 3 4 5 6 7

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

일단 시간을 09,10등의 형식으로 형변환해주고 이를 그룹화해주고 시간순으로 정렬도 해준다. 이 테이블을 E로 선언.

LEVEL을 이용하여 00부터 23까지 목록을 만든 테이블 R선언.

 

교집합 뿐만 아니라 NULL인 값의 목록도 표시되어야 하므로 E와 R을 OUTER JOIN을 해준다. 그러면 0~23에서 E에서 존재하지 않는 값에는 NULL값이 생기는 데 이를 NVL함수로 NULL ->0으로 처리해준다.

 

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

프로그래머스