[mySQL] #1. 프로그래머스 SQL Kit

프로그래머스 SQL 고득점 Kit TIL 정리

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

난이도

Level 3

문제

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

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

풀이

이 문제는 SQL의 INNER JOIN 을 사용하여 해결하는 문제이다. 두 테이블이 모두 가지는 key인 ANIMAL_ID로 JOIN 한 후, 보호 기간을 계산한다. 이 때 보호 기간은 입양일-보호시작일 로 계산하며, 이는 mySQL에서 DATEDIFF 함수를 사용하여 구한다. 이렇게 구한 보호 기간을 기준으로 내림차순 정렬하여 LIMIT을 이용해 상위 2개의 데이터만 선택한다.

DATEDIFF

• DATEDIFF(구분자, start_date, end_date)
• 구분자는 어떤 기준의 시간 차이를 구할 것인지를 결정하는 것으로, 날짜 차이를 구하고 싶으면 DAY, 시간차면 HOUR, 연 차이면 YEAR을 넣는다.

SELECT ai.animal_id, ai.name
FROM animal_ins as ai
     INNER JOIN animal_outs as ao ON ai.animal_id = ao.animal_id
ORDER BY DATEDIFF(ao.datetime, ai.datetime) DESC
LIMIT 2

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

난이도

Level 4

문제

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

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

풀이

이 문제의 관건은 0시부터 23시를 모두 갖고 있는 hour 칼럼을 만드는 것 이다. 만약 ANIMAL_OUTS 테이블만을 이용해 HOUR 함수로 시간을 구하려 한다면 실패이다. 해당 테이블에는 없는 시간대도 있기 때문에, Recursive CTE를 이용해 임시 테이블을 만든다. Recursive CTE는 재귀쿼리라고도 하며, 자기 자신을 참조하여 테이블을 생성할 수 있다. 이는 아래 코드와 같이 구현한다.

WITH RECURSIVE timetable AS(
SELECT 0 AS hour # Anchor memeber: 초깃값 고정
UNION ALL # 위 쿼리와 아래 쿼리의 값을 연산
SELECT timetable.hour+1 # +1을 이용해 시간대 한시간씩 늘림
FROM timetable WHERE timetable.hour<23) # 반복 멈추는 조건 

이렇게 0시부터 23시까지의 hour 칼럼을 갖고 있는 임시 테이블을 만들었다. 이제 ANIMAL_OUTS와 LEFT JOIN하여 각 시간대별로 입양이 몇 건 발생했는지를 계산하는데, GROUP BY함수를 사용하면 된다. 그리고 시간대순으로 정렬하기 위해 ORDER BY를 사용한다. 전체 코드는 아래와 같다.

WITH RECURSIVE timetable AS(
SELECT 0 AS hour # Anchor memeber: 초깃값 고정
UNION ALL # 위 쿼리와 아래 쿼리의 값을 연산
SELECT timetable.hour+1 # +1을 이용해 시간대 한시간씩 늘림
FROM timetable WHERE timetable.hour<23) # 반복 멈추는 조건 

SELECT t.hour, COUNT(ao.animal_id) AS count # 열 선택
FROM timetable AS t 
     LEFT JOIN animal_outs AS ao ON t.hour = HOUR(ao.datetime) # 임시테이블과 ANIMAL_OUTS의 LEFT JOIN
GROUP BY t.hour
ORDER BY t.hour