<프로그래머스 49> 오랜 기간 보호한 동물(1)(Lv.3)

 

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

---

 

문제 설명

ANIMAL_INS 테이블은 동물 보호소에 들어온 동물의 정보를 담은 테이블입니다. ANIMAL_INS 테이블 구조는 다음과 같으며, ANIMAL_ID, ANIMAL_TYPE, DATETIME, INTAKE_CONDITION, NAME, SEX_UPON_INTAKE는 각각 동물의 아이디, 생물 종, 보호 시작일, 보호 시작 시 상태, 이름, 성별 및 중성화 여부를 나타냅니다.

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

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

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

---

문제

아직 입양을 못 간 동물 중, 가장 오래 보호소에 있었던 동물 3마리의 이름과 보호 시작일을 조회하는 SQL문을 작성해주세요. 이때 결과는 보호 시작일 순으로 조회해야 합니다.

---

예시

예를 들어, ANIMAL_INS 테이블과 ANIMAL_OUTS 테이블이 다음과 같다면

 

[ ANIMAL_INS ]

ANIMAL_ID	ANIMAL_TYPE	DATETIME	INTAKE_CONDITION	NAME	SEX_UPON_INTAKE
A354597	Cat	2014-05-02 12:16:00	Normal	Ariel	Spayed Female
A373687	Dog	2014-03-20 12:31:00	Normal	Rosie	Spayed Female
A412697	Dog	2016-01-03 16:25:00	Normal	Jackie	Neutered Male
A413789	Dog	2016-04-19 13:28:00	Normal	Benji	Spayed Female
A414198	Dog	2015-01-29 15:01:00	Normal	Shelly	Spayed Female
A368930	Dog	2014-06-08 13:20:00	Normal		Spayed Female

 

[ ANIMAL_OUTS ]

ANIMAL_ID	ANIMAL_TYPE	DATETIME	NAME	SEX_UPON_OUTCOME
A354597	Cat	2014-05-02 12:16:00	Ariel	Spayed Female
A373687	Dog	2014-03-20 12:31:00	Rosie	Spayed Female
A368930	Dog	2014-06-13 15:52:00		Spayed Female

 

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

NAME	DATETIME
Shelly	2015-01-29 15:01:00
Jackie	2016-01-03 16:25:00
Benji	2016-04-19 13:28:00

 

※ 입양을 가지 못한 동물이 3마리 이상인 경우만 입력으로 주어집니다.

 

 

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

---

문제는 2가지 방식으로 풀 수 있다.

 

1. NOT EXISTS +  EQUI JOIN 
2. LEFT OUTER JOIN을 사용

> 1번은 오라클에 적용해보고, 2번은 mysql에 적용해보았다.

---

♥ 오라클 정답 : NOT EXISTS 와 EQUI JOIN 사용

# 서브쿼리의 select절에 컬럼명 사용
select i.name, i.datetime
    from animal_ins i
    where not exists 
    (select o.name 
     from animal_outs o
     where i.animal_id = o.animal_id)
    order by i.datetime asc fetch first 3 rows only;
    
# 서브쿼리의 select절에 싱글쿼테이션을 사용하여 특정값 입력    
 select i.name, i.datetime
    from animal_ins i
    where not exists 
    (select 'x'
     from animal_outs o
     where i.animal_id = o.animal_id)
    order by i.datetime asc fetch first 3 rows only;

 

---

오라클 정답에서 사용된 코드

■ NOT EXISTS 문

A 테이블에는 존재하는데, B에는 존재하지 않는 데이터를 출력할 때 사용

- 메인쿼리(A)의 데이터를 먼저 읽고 서브쿼리(B)에 그 데이터가 존재하지 않는지 찾아본다.

 

■ EQUI JOIN 사용

두 컬럼에 동일한 데이터를 가지는 컬럼이 존재할 때 사용할 수 있는 조인방법

- 두 테이블에서 동일한 데이터를 가지는 컬럼명을 WHERE절에서 연결조건으로 사용

 

■ ORDER BY절에서 FETCH FIRST 사용

정렬된 결과 중 반환될 행의 수를 제한해 줄 때 사용한다.

순서대로 정렬된 값 중 맨 위의 값에서 부터

연속적인 특정 값들중 몇 개만 출력할 때 사용할 수 있다.

- order by [정렬할 컬럼명] asc/desc FETCH FIRST [숫자] ROWS ONLY

숫자 부분에 원하는 수를 넣으면,

정렬된 데이터 중 위에서 부터 몇개의 행을 출력할 것인지 지정할 수 있다.

예를 들어 아래와 같이 입력했다면,
order by datetime asc fetch first 1 rows only;

가장 오래 보호소에 있었던 동물 1마리의 이름과 보호 시작일을 조회하게된다.

 

오라클 정답 해설

위의 내용을 기반으로 해설해보면,

 

# 1. MAIN QUERY ( ANIMAL_INS )

 

아직 입양을 가지 못 한 동물을 출력해야하기 때문에

동물 보호소에 들어 온 모든 동물들의 정보가 담긴 ANIMAL_INS 테이블이 메인쿼리가 되어야 한다.

왜냐하면, ANIMAL_INS 테이블에는 존재하는데, ANIMAL_OUTS 테이블에 존재하지 않는 데이터가 

입양가지 못 한 동물의 데이터이기 때문이다.

 

 

-- SELECT절

출력해야할 이름(NAME)과 보호시작일(DATETIME)을 입력해주는데,

컬럼을 가져올 테이블 명 혹은 테이블의 별칭을

온점(.)을 사용하여 컬렴과 연결해준다.

 

 

-- FROM절

main query로 사용할 테이블명을 기입하고,

컬럼명 앞에 편하게 명시해주기 위해서 별칭을 넣어준다. 

 

 

-- WHERE절

WHERE에 NOT EXISTS를 문과 함께 서브쿼리를 사용해준다. 

 

 

-- ORDER BY 절

ASC로 정렬된 DATETIME의 값들은

가장 오래 보호소에서 있는 순서대로 정렬되는 것이기 때문에

위에서부터 3마리만 출력하기 위해서 아래와 같은 코드를 사용할 수 있다.

 

order by datetime asc fetch first 3 rows only

---

# 2. SUB QUERY ( ANIMAL_OUTS )

 

-- SELECT절

< 방법 1 : 서브쿼리 테이블의 컬럼명을 넣어주기 >

서브쿼리의 from절의 테이블이 가지고 있는 

컬럼명 중 아무거나 넣어주거나,

 

< 방법 2 : 싱글쿼테이션으로 둘러싸여진 원하는 값 넣어주기 >

싱글쿼테이션으로 둘러싸여진 원하는 값을 넣어주면 된다.

단, NULL은 제외하고 아무런 값이나 가능하다.

 

select 'x'

 

위와 같이 'x'를 넣어주었을 경우 수행되는 원리 설명

ex)
1. main 쿼리에 450이라는 animal_Id가 존재
2. id : 450 을 sub 쿼리도 가지고 있는지 확인하기 위해 서브쿼리에 450이라는 값을 넘김
3. 서브쿼리 내에서 450이라는 값이 존재하지 않으면 x가 출력되면서 main query로 넘김
4. 서브쿼리에 없는 450에 대한 데이터가 출력됨

 

-- FROM 절

데이터를 가져올 테이블 명(ANIMAL_OUTS) 입력

 

-- WHERE 절

ANIMAL_INS 테이블과 ANIMAL_OUTS 테이블을

동일한 데이터를 가진 컬럼명을 연결조건으로 사용해서 JOIN해준다.

---

♥   MySQL 정답 : LEFT JOIN 사용

select i.name, i.datetime
from animal_ins i left join animal_outs o 
on i.animal_id = o.animal_id
where o.animal_id is null
order by i.datetime asc
limit 3;

---

MySQL정답에서 사용된 코드

 

■ LEFT OUTER JOIN

LEFT 테이블의 모든 레코드는 유지하고, 

우측 테이블과 일치하는 레코드가 없을 때 

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

- 즉 위의 쿼리를 보면 ANIMAL_INS 테이블의 모든 레코드를 유지하고,

ANIMAL_OUTS 테이블에 해당하는 레코드가 없는 경우에도 NULL로 결과를 반환한다.

 

■ LIMIT 함수

쿼리 결과에서 반환되는 행의 수를 제한하는 데 사용되는 함수

주로 일부 결과만 필요한 경우에 사용된다.

- LIMIT [처음부터 시작해서 반환할 행의 수 ]

- LIMIT [반환할 행의 시작위치(0부터 시작)], [반환할 행의 수] 

 

MySQL 정답 해설

위의 내용을 기반으로 해설해보면,

 

-- ON 절

ANIMAL_INS 테이블과 ANIMAL_OUTS 테이블을 동일한 데이터를 가진 컬럼명을 연결조건으로 사용해서 JOIN해준다.

 

-- WHERE 절

LEFT OUTER JOIN을 사용한 이유를 해석해보면,animal_outs테이블의 animal_id가 animal_ins 테이블에 존재하지 않을 때 null값으로 출력되기 때문이다.

 

따라서 animal_id가 IS NULL 인 데이터만 출력한다는 조건을 넣어주면,

결국에는 입양 보낸 동물 정보를 담은 테이블에는 정보가 없다는 것이다.

입양을 가지 못 한 동물들은 ANIMAL_INS에만 정보가 존재하므로 이런 정답이 도출되었다.

 

-- ORDER BY 절

ASC로 정렬된 DATETIME의 값들은

가장 오래 보호소에서 있는 순서대로 정렬되는 것이기 때문에

위에서부터 3마리만 출력하기 위해서 아래와 같은 코드를 사용할 수 있다.

 

order by i.datetime asc limit 3

 

 

 

 

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