<프로그래머스 73> 입양 시각 구하기 (2) (Lv.4)

 

프로그래머스에서 제공하는 SQL문제 매일 풀기 챌린지

---

 

 

문제 설명

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

 

 

본 문제는 Kaggle의 "Austin Animal Center Shelter Intakes and Outcomes"에서 제공하는 데이터를 사용하였으며 ODbL의 적용을 받습니다.

---

♥ 정답

with hour24 as
( select (level -1) as hour
    from dual
    connect by level <= 24 )
select h.hour, count(a.datetime) count
    from hour24 h left join animal_outs a
    on to_char(a.datetime, 'hh24') = lpad(h.hour,2,0)
    group by h.hour
    order by h.hour;

((이하생략))

---

 

 

■ WITH .. AS 절

쉽게 말해서 with 절로 temp table을 구성하는 것인데,

create table이 아니라 with절을 사용해서 임시적으로 테이블을 만드는 것이다.

이 임시 테이블을 사용해서 테이블을 생성하여 select 시 활용할 수 있다.

WITH [new임시테이블명] AS 
	( 새로운 임시 테이블로 만들 쿼리문 )
select ....; --만든 쿼리문을 활용해서 검색할 쿼리문

 

with절로 생성한 임시 테이블은 temporart tablespace에 저장되고, 

그 결과를 temp 테이블로 구성되는 것이다.

 

 

■ 계층형 질의문

계층형 질의문은 데이터에서 서열을 발견해서 출력하는 쿼리문이며,

level이라는 컬럼을 볼 수 있게 해준다. 

start with 조건으로 최상위 노드를 설정해주고,

connect by절을 사용해서 계층형 구조에서의 연결조건을 설정할 수 있다.

 

EMP테이블을 사용하여 문법을 적용하면 아래와 같다.

select level, empno, ename, mgr
	from emp
	where [조건] 
	start with [제일 첫번째 행에 나올 사원을 결정하는 절]
	connect by prior [첫번째행의 사원의 부하직원들을 출력하기 위한 연결조건어줌]

#첫번째 행이 무조건 서열 1위로 출력됨.
#CONNECT BY 절에 첫번째행의 사원의 부하직원들을 출력하기 위한 연결조건 적음
#empno=mgr : 사원번호가 mgr인 사원
#LEVEL이라는 컬럼 추가! START WITH 와 CONNECT BY 는 짝꿍

 

 

■ LPAD() 함수 

왼쪽(Left)에 원하는 만큼 특정 값을 채워넣을 때 사용하는 함수이다.

LPAD([컬럼명], [출력할 최종 문자열 길이], [채워넣을 값])

 

원래의 데이터가 출력할 최종 문자열의 길이 보다 긴 경우에는 기존의 값을 그대로 반환하며,

채워 넣을 값을 생략할 경우 기본값으로 공백이 사용된다.

 

예를들면, 아래와 같다.

LPAD( 'SCOTT', 10, '@') => '@@@@@SCOTT' 을 반환한다.

 

 

■ LEFT OUTER JOIN

조인은 여러 개의 테이블의 컬럼들을 활용하여 한 번에 원하는 것을 출력하고자 할 때 사용하는 문법이다.

그 중 LEFT JOIN이란

LEFT 테이블의 모든 레코드는 유지하고, 우측 테이블과 일치하는 레코드가 없을 때 

왼쪽 테이블의 레코드는 유지하고 있음

 

 

■ TO_CHAR( [컬럼명/문자열], '변경할 데이터 형태') 
   위의 함수를 사용해서 날짜 데이터 형식을 → 문자형 데이터 형식으로 변환한다.

   * 표시한 형태 해석(오라클의 경우 대소문자 구분 없음)
   - RRRR/YYYY : Year 연도 4자리로 표기(ex. 2023, 2024)
   - MM, MON: Month 월 2자리로 표기 (ex. 05, 07)
   - DD : Day 일 2자리로 표기 (ex. 01, 31)

   - DAY : 요일 ( ex. 금요일)

   - DY : 요일 ( ex. 금)

   - D : 요일을 숫자로 표현 ( 1은 일요일을 의미하며, 2는 월요일을 의미한다.)

   - HH : 12시간 형식으로 나타내는 시간을 의미 

   - HH24 : 24시간 형식으로 나타내는 시간을 의미

   - MI : 분

   - SS : 초

   - AM/PM : 오전/오후

---

♥ 해설해보기

 

 #WITH .. AS 절 내부 

 

계층형 질의문을 활용하여 HOUR 컬럼을 출력해 내는 것이 중요하다.

CONNECT BY절에서 LEVEL을 사용하여 원하는 수 만큼 반복시켜 행의 값을 출력하고

그 값을 HOUR 컬럼의 데이터 즉, 시간으로 사용한다.

with hour24 as
( select (level -1) as hour
    from dual
    connect by level <= 24 )

 

위와 같이 시간으로 사용할 데이터를 출력하는데,

0시부터 시작해야하기 때문에 -1을 해준다. 

따라서 24까지 포함시켜야 24 -1 = 23까지 출력되는 결과를 가져온다. 

 

위의 쿼리문을 임시테이블 hour24로 이름을 지정해준 후 이 테이블을 사용할 쿼리문을 바로 아래에 작성해주면 된다.

 

 

 #WITH .. AS 절 외부 

 

기존 테이블인 animal_outs와 with 절로 생성한 hour24 테이블을 left join 으로 join해준다.

즉, 아래의 순서로 조인해주는데,

hour24 h left join animal_outs a

 

그 이유는 각 시간대별로 입양이 몇 건 발생했는지 조회해야하기 때문이다.

따라서 시간 컬럼의 값은 모두 출력되어야 한다. 

 

연결조건으로 datatime과 hour을 연결해준다. 

 

hour24 h left join animal_outs a on to_char(a.datetime, 'hh24') = lpad(h.hour,2,0)

 

to_char함수를 사용하여 datatime 컬럼에서 시간을 출력하고,hour컬럼과 연결해주는데, to_char함수를 사용하여 시간을 출력하였을 때 오전 2시의 경우 02로 출력되기 때문에형식을 일치시켜 주기 위해서 hour 컬럼의 형식을 LPAD를 사용하여 1자리 수일 경우 왼쪽에 0을 채울 수 있게 설정해준다.

 

마지막으로

시간대별로 입양이 몇 건 발생했는지 조회해야하기 때문에

group by로 hour컬럼을 묶어주고

select절에서 count 하여 입양 건 수를 세어주어야한다.

 

시간대에 데이터가 없는 경우 null값이 존재하는데,

그 값을 세지 않기 위해서 count(*) 를 사용하지 않고

count(a.datetime) 과 같이 컬럼명을 입력해서 null값을 무시하게 한다.

count(*)로 셀 경우 null값도 포함하기 때문이다.

 

count()의 특징은 값이 없는 경우에도 무조건 값을 반환한다.

따라서 시간대에 입양이 없는 경우에도 0이라는 값을 반환하게 된다. 

 

 

 

 

 

출처 : 프로그래머스 코딩 테스트 연습, https://school.programmers.co.kr/learn/challenges