<프로그래머스 58> 자동차 대여 기록에서 대여중/대여 가능 여부 구분하기(Lv3.)

 

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

---

 

문제 설명

다음은 어느 자동차 대여 회사의 자동차 대여 기록 정보를 담은 CAR_RENTAL_COMPANY_RENTAL_HISTORY 테이블입니다. CAR_RENTAL_COMPANY_RENTAL_HISTORY 테이블은 아래와 같은 구조로 되어있으며, HISTORY_ID, CAR_ID, START_DATE, END_DATE 는 각각 자동차 대여 기록 ID, 자동차 ID, 대여 시작일, 대여 종료일을 나타냅니다.

 

Column name	Type	Nullable
HISTORY_ID	INTEGER	FALSE
CAR_ID	INTEGER	FALSE
START_DATE	DATE	FALSE
END_DATE	DATE	FALSE

 

 

---

문제

CAR_RENTAL_COMPANY_RENTAL_HISTORY 테이블에서 2022년 10월 16일에 대여 중인 자동차인 경우 '대여중' 이라고 표시하고, 대여 중이지 않은 자동차인 경우 '대여 가능'을 표시하는 컬럼(컬럼명: AVAILABILITY)을 추가하여 자동차 ID와 AVAILABILITY 리스트를 출력하는 SQL문을 작성해주세요. 이때 반납 날짜가 2022년 10월 16일인 경우에도 '대여중'으로 표시해주시고 결과는 자동차 ID를 기준으로 내림차순 정렬해주세요.

---

예시

예를 들어 CAR_RENTAL_COMPANY_RENTAL_HISTORY 테이블이 다음과 같다면

HISTORY_ID	CAR_ID	START_DATE	END_DATE
1	4	2022-09-27	2022-09-27
2	3	2022-10-03	2022-10-04
3	2	2022-10-05	2022-10-05
4	1	2022-10-11	2022-10-16
5	3	2022-10-13	2022-10-15
6	2	2022-10-15	2022-10-17

 

2022년 10월 16일에 대여 중인 자동차는 자동차 ID가 1, 2인 자동차이고, 대여 가능한 자동차는 자동차 ID가 3, 4이므로, '대여중' 또는 '대여 가능' 을 표시하는 컬럼을 추가하고, 자동차 ID를 기준으로 내림차순 정렬하면 다음과 같이 나와야 합니다

CAR_ID	AVAILABILITY
4	대여 가능
3	대여 가능
2	대여중
1	대여중

 

---

♥ 오라클 정답

select car_id, decode(max(n_availability),1,'대여중','대여가능') as availability
   from ( select car_id, 
	( case when to_date('20221016','rrrrmmdd') between start_date and end_date then 1
    	else 0 end ) as n_availability
    	from car_rental_company_rental_history ) d   
   group by car_id
   order by car_id desc;

이 문제는
in line view( from 절의 서브쿼리 ),
case when 조건문, decode(), to_date(), between .. and, max()
를 사용하여 해결할 수 있는 문제이다.

---

■ IN LINE VIEW ( FROM 절의 서브쿼리 )

대여 가능 여부에 따라 숫자로 변환하기 위하여 사용하였다.즉, 대여중인 경우 1로 출력하고, 대여 가능인 경우 0으로 출력하도록 하여 메인쿼리에 TRUE, FLASE를 숫자형식으로 반환한다.

 

 

■  TO_DATE( [컬럼명], '변경할 데이터 형태' )

 

위의 함수를 사용해서 숫자 혹은 문자형 데이터 형식을 → 날짜형 데이터 형식으로 변환한다.

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

 

■  CASE WHEN 조건문

CASE WHEN [조건1] TEHN [조건1이 참일 때 출력할 값]
    WHEN [조건2] THEN [조건2가 참일 때 출력할 값]
    ELSE [조건1,조건2..등 모두 해당되지 않을 때 출력할 값]
    END

 

 

■  DECODE() 함수

DECODE( 컬럼명, 조건, 조건을 만족할 때 출력할 값, 기본값)
DECODE( 컬럼명, 조건1, 조건1을 만족할 때 출력할 값, 조건2, 조건2를 만족할 때 출력할 값 )

 

 

---

 

 

 #IN LINE VIEW ( FROM 절의 서브쿼리 ) 

 

1. SELECT 절

대여 가능 여부에 따라 숫자로 변환하기 위하여 사용하였다.

즉, 대여중인 경우 1로 출력하고, 대여 가능인 경우 0으로 출력하도록 하여

메인쿼리에 TRUE, FLASE를 숫자형식으로 반환한다.

그럼 대여중, 대여가능을 어떻게 구분할지 알아보자

2022년 10월 16일에 반납하는 경우까지 포함하여
해당 날짜에 대여 중인 자동차의 경우 '대여중'에 해당한다.
이를 알기 위해서는 
대여 시작일(START_DATE)와 대여 종료일(END_DATE) 사이에 
2022년 10월 16일을 포함하고 있는지를 확인하면 된다.
만약 보함하고 있다면 '대여중' 포함하고 있지 않다면 '대여 가능' 으로 보면 된다.

따라서 기준일이 되는 2022년 10월 16일을 비교하기 위해서
설명한 TO_DATE()함수를 사용하여 아래와 같이 숫자인20221016을 DATE 형식으로 변경해준다.

TO_DATE('20221016', 'rrrrmmdd') between START_DATE and END_DATE

이제 기준일에 따라 대여 가능 여부를 나누는 것에 성공하였으므로,

메인쿼리에 대여 가능 여부에 따라 숫자로 변환할 것이다.

즉, CASE WHEN 조건문을 사용하여 대여중인 경우 1로 출력하고, 대여 가능인 경우 0으로 출력하도록 하여

메인쿼리에 TRUE, FLASE를 숫자형식으로 반환한다.

 

(case when to_date('20221016', 'rrrrmmdd') between start_date and end_date then 1 else 0 end ) as n_availability

이유를 미리 설명하면, 

자동차_ID별 대여가능 여부는 최종적으로 하나만 출력되어야 한다.

하지만, 대여가능여부를 출력하는 것에만 초점을 맞춰서 문제를 풀면,

아래와 같이 대여한 각 HISTORY_ID 마다 대여가능여부가 전부 출력되기 때문에

이 문제를 해결하고자 숫자로의 변환을 우선적으로 한 것이다.

 

 

 

 # MAIN QUERY 

 

이제 출력해야할 컬럼명 car_id, availability 를 나열해준다.

AVAILAVILITY 컬럼은 아래와 같이 만들어야한다.

 

우선 DECODE 함수를 사용하여

IN LINE VIEW에서 생성한 N_AVAILABILITY 컬럼이 1인 경우 '대여중', 그렇지 않을 경우 '대여 가능'으로 출력되게 하고, 별칭을 AVAILAVILITY 로 지정해준다.

결과적으로 아래와 같은 쿼리문이 완성된다.

select car_id, decode(n_availability, 1, '대여중', '대여 가능') as availability
from ( select car_id, 
      (case when to_date('20221016', 'rrrrmmdd') between start_date and end_date then 1
       else 0 end ) as n_availability from car_rental_company_rental_history ) d         
order by car_id desc;

 

그런데, 위에서 잠깐 설명했듯이 아래와 같은 오류가 발생한다.

자동차_ID별 대여가능 여부는 최종적으로 하나만 출력되어야 하는데,

아래와 같이 대여한 각 HISTORY_ID 마다 대여가능여부가 전부 출력되고 있다.

 

인라인뷰에서 생성한 n_availability 컬럼과 max함수를 그리고 group by절을 이용하여 이 문제를 해결할 수 있다.

 

max(n_availability)와 group by car_id를 사용했는데, 

아래의 그림과 함께 어떻게 해결해냈는지 확인해보자.

 

group by car_id 를 사용하여

car_id 별 결과를 출력할 것인데,

 

max(n_availability) 를 하면, 

위의 그림 처럼 각 car_id별 가장 큰 n_availability 를 출력하게 된다.

 

'대여중' 인 car_id는 max(n_availability) 값이

1과 0으로 구성되어 있기 때문에 1을 반환하게 된다.

 

'대여 가능' 인 car_id는 max(n_availability) 값이

0으로만 구성되어 있기 때문에  0을 반환하게 된다.

 

최종적으로 car_id별로 1 혹은 0 중 하나의 값만 갖는 결과를 가지기 때문에

case when 조건문 혹은 decode를 사용하여 1일 경우 '대여중', 0일 경우 '대여 가능'을 출력해주면 

아래와 같이 원하는 결과를 가지게 되어 문제를 해결할 수 있다.

 

최종 결과 출력

((이하 생략))

 

---

♥ MySQL 정답

select car_id, if(max(n_availability)=1, '대여중', '대여 가능') as availability
from ( select car_id, 
      (case when str_to_date('20221016', '%Y%m%d') between start_date and end_date then 1
       else 0 end ) as n_availability from car_rental_company_rental_history ) d     
group by car_id       
order by car_id desc;

 

설명은 오라클과 같으나 사용하는 함수가 아래와 같이 다르다는 것만 참고

 

 

■ IN LINE VIEW ( FROM 절의 서브쿼리 )

대여 가능 여부에 따라 숫자로 변환하기 위하여 사용하였다.

즉, 대여중인 경우 1로 출력하고, 대여 가능인 경우 0으로 출력하도록 하여

메인쿼리에 TRUE, FLASE를 숫자형식으로 반환한다.

 

 

■ STR_TO_DATE( [컬럼명or 값], [형태] )

 

문자나 숫자 형식의 데이터를 날짜 형식으로 변환해주는 함수이다.

 

 * 표시한 형태 해석(대소문자 구분 있음)
   - %Y : Year 연도를 4자리로 표기 (ex. 2023, 2024)
   - %m : Month 월을 2자리로 표기(ex. 05, 12)
   - %d : Day 일을 두 자리로 표기(ex. 04, 28)

* 참고
   - %c :Month 월을 한 자리로 표기(ex. 5, 12) 
   - %e :Day 일을 한자리로 표기(ex. 4, 28)

 

■  CASE WHEN 조건문

CASE WHEN [조건1] TEHN [조건1이 참일 때 출력할 값]
    WHEN [조건2] THEN [조건2가 참일 때 출력할 값]
    ELSE [조건1,조건2..등 모두 해당되지 않을 때 출력할 값]
    END

 

 

■  DECODE() 함수

 

IF(조건, 조건이 참일 때 출력할 값, 조건이 거짓일 때 출력할 값)
IF(조건1, 조건1이 참일 때 출력할 값, ( IF( 조건2, 조건2가 참일 때 출력할 값, 조건2가 거짓일 때 출력할 값)))

 

 

 

 

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