프로그래머스 SQL 풀이 - 오랜 기간 보호한 동물(2)

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

프로그래머스

 

문제 설명

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문을 작성해주세요. 이때 결과는 보호 기간이 긴 순으로 조회해야 합니다.

 

※ 입양을 간 동물이 2마리 이상인 경우만 입력으로 주어집니다.

 

풀이

1. 보호 기간이란?

 

 - 보호 시작일이 입양일보다 이전이다.

 - 테이블을 조회해보면 2건 정도 입양일이 보호 시작일보다 빠른 경우가 있지만 입양을 간 동물이 2마리 이상인 경우만 입력으로 주어진다고 했으므로 예외 케이스로 보면 될 것 같다.

 - 따라서 (입양일 - 보호 시작일)이 보호 기간이고, 값으로 빼기 위해 DATEDIFF() 함수를 사용하면 된다.

 - temp_01 => 동물 별 최대 보호 기간, ID, 이름 조회

 

2. 가장 보호 기간이 긴 2마리 동물을 뽑기 위해 ROW_NUMBER라는 순위 함수를 이용한다.

 

 - ROW_NUMBER()는 순위 함수이다.

 - PARTITION 절(그룹핑), ORDER BY 절(높은/낮은 순위 기준), WINDOW 절(테이블 내 이동 범위)로 이루어진다.

 - 각 동물 별 보호 기간을 별도로 선언된 쿼리를 통해 뽑을 것이므로, 이미 그룹핑된 상태이다. (PARTITION절 생략)

 - ORDER BY 절은 생략 불가 (순위를 매기는 기준이 되기 때문)

 - 순위를 매기는 기준은 보호 기간이고, 내림차순으로 해야 큰 값이 1순위가 된다.

 - temp_02 => temp_01 + 순위

 

3. 메인 절에서 순위가 2 이하인 동물을 조회하면 된다.

 

정답 코드

WITH
temp_01 as ( #동물 별 최대 보호 기간, ID, 이름
    SELECT a.ANIMAL_ID, MAX(a.NAME) as NAME
    , MAX(DATEDIFF(b.DATETIME, a.DATETIME)) as MAX_PERIOD
    FROM ANIMAL_INS a
        JOIN ANIMAL_OUTS b ON a.ANIMAL_ID = b.ANIMAL_ID
    GROUP BY a.ANIMAL_ID
),
temp_02 as ( #보호 기간이 긴 순서대로 순위
    SELECT *, ROW_NUMBER() OVER (ORDER BY MAX_PERIOD DESC) as RNUM
    FROM temp_01
)
SELECT ANIMAL_ID, NAME
FROM temp_02
WHERE RNUM <= 2