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

🆀 문제 

ANIMAL_OUTS 테이블은 동물 보호소에서 입양 보낸 동물의 정보를 담은 테이블입니다. ANIMAL_OUTS 테이블 구조는 다음과 같으며, ANIMAL_ID, ANIMAL_TYPE, DATETIME, NAME, SEX_UPON_OUTCOME는 각각 동물의 아이디, 생물 종, 입양일, 이름, 성별 및 중성화 여부를 나타냅니다. 보호소에서는 몇 시에 입양이 가장 활발하게 일어나는지 알아보려 합니다. 0시부터 23시까지, 각 시간대별로 입양이 몇 건이나 발생했는지 조회하는 SQL문을 작성해주세요. 이때 결과는 시간대 순으로 정렬해야 합니다.

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

예시

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

 

 

 

 

🅰 풀이

못 풀어서 머리를 끙끙 싸매던 나에게 한 줄기 빛과 같던 밑의 링크✨(감사합니다🙏)

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

해당 포스팅은 위 링크에 나온 sql문을 풀이하는 과정에 대한 포스팅입니다.

 

SELECT l.hour, nvl(count, 0) AS count

FROM (SELECT TO_CHAR(datetime, 'HH24') AS hour, count(*) AS count 
        FROM animal_outs 
        GROUP BY TO_CHAR(datetime, 'HH24') 
        ORDER BY hour) O, 
        (SELECT LEVEL-1 AS hour FROM dual CONNECT BY LEVEL<=24) L
      
WHERE L.hour = O.hour(+)

ORDER BY L.hour;

위 sql을 실행하면 문제의 예시와 같은 결과가 출력됩니다.

 

이제부터 하나하나 뜯어보겠습니다.

 

 

➡️  TO_CHAR

SELECT TO_CHAR(datetime, 'HH24') AS hour, count(*) AS count 
FROM animal_outs 
GROUP BY TO_CHAR(datetime, 'HH24') 
ORDER BY hour

이는 프로그래머스 SQL LEVEL 2의 입양 시각 구하기 문제를 Oracle로 작성한 풀이와 비슷합니다.

오라클에서 날짜 포맷의 변환은 TO_CHAR함수를 이용합니다.

위 datetime 컬럼의 타입은 datetime입니다. datetime type은 DATE, TIMESTAMP, TIMESTAMP WITH LOCAL TIME ZONE 타입입니다.

 

Oracle에서 SYSDATE를 가져오는 쿼리는 다음과 같습니다.

SELECT SYSDATE FROM DUAL;

위 쿼리를 실행시키면 2020-06-07 11:04:25로 결과가 출력됩니다.

함수의 기본형은 TO_CHAR(숫자/날짜 ,  FORMAT)입니다. 날짜함수 SYSDATE를 이용하여 TO_CHAR에 대해 알아봅시다.

SELECT

--날짜 포맷
TO_CHAR(SYSDATE,'YYYYMMDD'),--20200607
TO_CHAR(SYSDATE,'YYYY-MM-DD'),--2020-06-07
TO_CHAR(SYSDATE,'YYYY/MM/DD'),--2020/06/07
TO_CHAR(SYSDATE,'YYYY'),--2020
TO_CHAR(SYSDATE,'YY'),--20
TO_CHAR(SYSDATE,'MM'),--06
TO_CHAR(SYSDATE,'MON'),--JUN
TO_CHAR(SYSDATE,'D'),--1 (주중의 일을 1~7로 표시(일요일 = 1)
TO_CHAR(SYSDATE,'DD'),--07
TO_CHAR(SYSDATE,'DDD'),--159 (1년기준 일 수)
TO_CHAR(SYSDATE,'DAY'),--SUNDAY (요일 표시)
TO_CHAR(SYSDATE,'DY'), --SUN (요일을 한자리로 표시)
TO_CHAR(SYSDATE,'WW'), -- 23 (일년 기준 주를 00 ~ 53 형태로 표시)
TO_CHAR(SYSDATE,'W'), -- 1 (한달 기준 몇번째 주)
TO_CHAR(SYSDATE,'DL'),--Sunday, June 07, 2020

--사간 포맷
TO_CHAR(SYSDATE,'AM'),--AM
TO_CHAR(SYSDATE,'PM'),--AM
TO_CHAR(SYSDATE,'HH'), --11 (시간을 0 ~ 12 형태로 표시)
TO_CHAR(SYSDATE,'HH24'), --11 (시간을 0 ~ 24 형태로 표시)
TO_CHAR(SYSDATE,'MI'), --15	 (분을 00 ~ 59 형태로 표시)
TO_CHAR(SYSDATE,'SS'), --51	 (초을 01 ~ 59 형태로 표시)

--세기 포맷
TO_CHAR(SYSDATE,'CC'), --21 (세기)
TO_CHAR(SYSDATE,'BC') --AD

FROM DUAL;

 

 

 

➡️ CONNECT BY 

SELECT LEVEL-1 AS hour FROM dual CONNECT BY LEVEL<=24;

Oracle에서는 계층 데이터를 위해 CONNECT BY 절을 지원합니다.

(오라클에서 계층형 쿼리는 START WITH... CONNECT BY 절로 생성할 수 있습니다. 해당 포스팅에서 START WITH은 언급하지 않습니다.)

CONNECT BY 절의 결과에는 LEVEL이라는 칼럼이 있으며, 이는 계층의 깊이를 의미합니다❗️

LEVEL 함수를 이용하면  순차적 목록을 수월하게 만들 수 있습니다.

 

SELECT LEVEL FROM dual CONNECT BY LEVEL <=5;

이 SQL문을 실행하면 다음과 같은 결과를 출력합니다.

LEVEL

1

2

3

4

5

따라서 문제의 의도에 맞게 0부터 24까지의 숫자를 순서대로 출력하려면 SELECT 문에 LEVEL-1을 작성하고, CONNECT BY절에는 LEVEL<=24라는 조건을 걸어주어야 합니다.

 

CONNECT BY LEVEL을 이용하여 다음과 같은 SQL문을 작성할 수도 있습니다. 오늘 날짜로부터 10일간의 날짜를 순서대로 출력하는 SQL문입니다. LEVEL은 1부터 시작하기 때문에 LEVEL-1을 통해 오늘 날짜부터 출력하도록 했습니다.

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

이를 실행하면 아래와 같은 결과가 출력됩니다.

to_char(to_date(sysdate,'yy-mm-dd')+level-1,'yy-mm-dd')
07-06-20
07-06-21
07-06-22
07-06-23
07-06-24
07-06-25
07-06-26
07-06-27
07-06-28
07-06-29

 

 

➡️ OUTER JOIN

WHERE L.hour = O.hour(+)

조인하는 여러 테이블에서 한 쪽에는 데이터가 있고, 한쪽에는 데이터가 없는 경우, 데이터가 있는 쪽 테이블의 내용을 모두 출력하는 것입니다. 즉, 조건에 맞지 않아도 해당하는 행을 출력하고 싶을 때 사용할 수 있습니다.

정상적으로 조인 조건을 만족하지 못하는 행들도 출력하기 위해 OUTER JOIN을 사용합니다.

❗️Oracle에서 OUTER JOIN 연산자는  (+)  입니다. 

❗️조인시킬 값이 없는 조인 측에 (+)를 위치시킵니다.

❗️OUTER JOIN 연산자는 표현식의 한 편에만 올 수 있습니다.

❗️매칭되는 데이터가 없는 경우, NULL로 표시합니다.

 

SQL문을 다음과 같이 바꾼다면, 결과는 아래와 같습니다.

WHERE L.hour(+) = O.hour

HOUR	COUNT
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

O.hour이 L.hour의 값을 모두 포함하고 있기 때문에 L.hour의 값만 나오게 됩니다.

 

 

 

➡️ NVL

SELECT l.hour, nvl(count, 0) AS count

NVL은 오라클에만 있는 함수로, NVL 함수는 값이 null인 경우 지정값을 출력합니다. 

위 SQL문에서는 count가 null인 경우 0을 출력하도록 합니다.

 

🤞 BONUS !

Oracle에는 NVL2도 있습니다!

NVL2("컬럼명", "지정값1", "지정값2");

NVL2는 null이 아닌 경우 지정값1을 출력하고, null인 경우 지정값2를 출력합니다.

 

 

 

 

 

 

 

 

 

 

🔗 참고 링크

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

[Oracle] 오라클 다양한 날짜 / 시간 포맷 변경 (TO_CHAR) 사용법 & 예제

[오라클|Oracle] 날짜타입 가지고 놀기 - TO_DATE, TO_CHAR

[ORACLE] CONNECT BY 함수 사용하기

[Oracle] 조인 (JOIN), 아우터 조인(Outer Join)

[SQL]OUTER JOIN(외부조인)에 대해 알아보자!

[Oracle|오라클] NVL, NVL2 함수 사용방법 (null, 공백, 치환)