[프로그래머스 스쿨 - MY SQL] 동물 보호 문제 Lv.2 ~ LV.4

LV.2 중성화 여부 파악하기

문제

보호소의 동물이 중성화되었는지 아닌지 파악하려 합니다. 중성화된 동물은 SEX_UPON_INTAKE 컬럼에 'Neutered' 또는 'Spayed'라는 단어가 들어있습니다. 동물의 아이디와 이름, 중성화 여부를 아이디 순으로 조회하는 SQL문을 작성해주세요. 이때 중성화가 되어있다면 'O', 아니라면 'X'라고 표시해주세요.

해설

추출해야할 것 animal_id, name, 중성화 여부

조건 "Neutered"나 Spayed가 포함된 중성화 개체는 "O"표시

정렬 animal_id ASC

 

1. SELECT 절에 CASE WHEN 절을 쓰면된다

2. 조건을 걸 때 문자열 일치가 아닌 포함 여부이기 때문에 LIKE "%string%"을 쓰는 것을 잊지말자

3. animal_id대로 정렬한다

 

이를 구현한 코드는 아래와 같다

SELECT animal_id, name,
(CASE
WHEN sex_upon_intake LIKE "%Neutered%" OR sex_upon_intake LIKE "%Spayed%" THEN "O"
ELSE "X"
END) AS "중성화"
FROM Animal_ins
ORDER BY animal_id

https://school.programmers.co.kr/learn/courses/30/lessons/59409

 

LV.3 오랜기간 보호한 동물(2)

문제

입양을 간 동물 중, 보호 기간이 가장 길었던 동물 두 마리의 아이디와 이름을 조회하는 SQL문을 작성해주세요. 이때 결과는 보호 기간이 긴 순으로 조회해야 합니다.

해설

추출해야할 것 animal_id, name

조건 보호기간이 가장 긴 두 마리 동물

정렬 암묵적으로 보호기간이 가장 긴 순서

 

1. Animal_ins와 Animal_outs를 join한다. <보호 시작 날짜의 입양 날짜의 차이를 구하기 위해>

2. DATEDIFF를 통해서 날짜 차이를 구해주고 내림차순 정렬한다 

3. animal_id, name, datediff 값을 인라인뷰로 구현한다

4. 메인 쿼리에서 animal_id와 name만 추출한다

5. 최대 2개의 동물 레코드니까 LIMIT 2 를 적용한다

 

이를 구현한 코드는 아래와 같다

DATEDIFF를 한 후 ORDER BY로 정렬하는 방법이 있고<가장 기초적>

RANK OVER 함수를 써서 랭크 순으로 정렬되게 하는 방법이 있다

두 방법의 차이는 RANK OVER가 실제 날짜 차이 값으로 반환되는게 아닌

순위 값으로 반환되는 점에서 다르다.

<지금은 순위와 날짜차이 값을 쓰지 않으니 딱히 상관은 없다>

-- RANK OVER 버전
SELECT animal_id, name
FROM (SELECT i.animal_id, i.name, RANK() OVER(ORDER BY DATEDIFF(o.datetime, i.datetime) DESC) as ranking
FROM Animal_ins AS i INNER JOIN 
Animal_outs AS o USING(animal_id)) AS t
LIMIT 2

-- BASIC 버전
SELECT animal_id, name
FROM (SELECT i.animal_id, i.name, DATEDIFF(o.datetime, i.datetime) as ranking
FROM Animal_ins AS i INNER JOIN 
Animal_outs AS o USING(animal_id) 
ORDER BY ranking DESC ) AS t
LIMIT 2

https://school.programmers.co.kr/learn/courses/30/lessons/59411

 

LV.4 입양시각 구하기(2)

문제

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

해설

추출해야할 것 hour<시간별>, count<입양빈도>

조건 입양 시간<시>별 입양빈도가 0이어도 hour가 나와야함

정렬 hour 오름차순

 

※ 주의

보통 입양된 시각을 기준으로 GROUP BY를 하는데,

레코드에 존재하는 시간을 기준으로 그룹핑을 한다.

즉, 문제에서 요구하는 0시, 1시, 2시 등 입양 사건이

일어나지 않은 시각은 포함하지 못한다는 것이다

 

1. 이미 존재하는 테이블에서 없는 그룹을 만들어낼 수 없으니,

가상 테이블을 만들어야한다.

2. 0~23의 범위를 갖는 행을 가진 테이블을 재귀함수로 만든다

3. 메인 테이블에서 HOUR(datetime)을 기준으로 그룹화해주고 입양 빈도를 계산하는 쿼리를 만든다

4.  해당 쿼리를 인라인뷰로 놓고 가상의 테이블을 기준으로 JOIN해준다 

5. IFNULL함수를 사용하여 JOIN되었을 때 NULL값을 0으로 채워준다

 

이를 구현한 코드는 아래와 같다.

WITH RECURSIVE cnt AS(
SELECT 0 AS HOUR
UNION ALL
SELECT HOUR + 1
FROM cnt
WHERE HOUR < 23
)

SELECT c.hour, IFNULL(outs.COUNT,0) AS "COUNT"
FROM cnt AS c LEFT JOIN (SELECT HOUR(datetime) AS "HOUR" , COUNT(*) AS "COUNT" 
                         FROM animal_outs
                        GROUP BY HOUR(datetime)) AS outs
ON c.HOUR = outs.HOUR

 

재귀함수를 이용한 가상 테이블을 만들지 못하면 못 푸는 문제인데,

난 이 방법을 아예 몰라서 못풀었다 ㅎㅎ

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

 

RECURSIVE Table

재귀함수는 RECURSIVE를 통해서 구현할 수 있는데

가상 테이블 cnt를 만들어주고

시작 포인트<첫 행>를 잡아주고

UNION / UNION ALL 다음

SELECT 문으로재귀 규칙을 정해주고

가상 테이블을 호출한다

뒤이어 꼭 WHERE 절로 종료 케이스를 지정해줘야한다

 

마지막으로 해당 가상테이블을 호출하면 

규칙에 따라 반환된 테이블을 볼 수 있다

WITH RECURSIVE cnt AS(
SELECT 100 AS HOUR -- 시작포인트
UNION ALL
SELECT HOUR + 3 -- 재귀 
FROM cnt
WHERE HOUR < 2000 -- 종료 조건
)

SELECT * FROM cnt -- 호출