<프로그래머스 61> 대여 횟수가 많은 자동차들의 월별 대여 횟수 구하기(Lv.3)

 

프로그래머스에서 제공하는 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년 8월부터 2022년 10월까지 총 대여 횟수가 5회 이상인 자동차들에 대해서 해당 기간 동안의 월별 자동차 ID 별 총 대여 횟수(컬럼명: RECORDS) 리스트를 출력하는 SQL문을 작성해주세요. 결과는 월을 기준으로 오름차순 정렬하고, 월이 같다면 자동차 ID를 기준으로 내림차순 정렬해주세요. 특정 월의 총 대여 횟수가 0인 경우에는 결과에서 제외해주세요.

---

예시

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

HISTORY_ID	CAR_ID	START_DATE	END_DATE
1	1	2022-07-27	2022-08-02
2	1	2022-08-03	2022-08-04
3	2	2022-08-05	2022-08-05
4	2	2022-08-09	2022-08-12
5	3	2022-09-16	2022-10-15
6	1	2022-08-24	2022-08-30
7	3	2022-10-16	2022-10-19
8	1	2022-09-03	2022-09-07
9	1	2022-09-18	2022-09-19
10	2	2022-09-08	2022-09-10
11	2	2022-10-16	2022-10-19
12	1	2022-09-29	2022-10-06
13	2	2022-10-30	2022-11-01
14	2	2022-11-05	2022-11-05
15	3	2022-11-11	2022-11-11

 

대여 시작일을 기준으로 총 대여 횟수가 5회 이상인 자동차는 자동차 ID가 1, 2인 자동차입니다. 월 별 자동차 ID별 총 대여 횟수를 구하고 월 오름차순, 자동차 ID 내림차순으로 정렬하면 다음과 같이 나와야 합니다.

 

MONTH	CAR_ID	RECORDS
8	2	2
8	1	2
9	2	1
9	1	3
10	2	2

---

♥ 오라클 정답

select extract(month from start_date) as month, car_id, count(*) record
   from car_rental_company_rental_history
   where start_date between to_date('20220801', 'rrrrmmdd') and (to_date('20221031', 'rrrrmmdd') + 1)
   and car_id in (select car_id
      from car_rental_company_rental_history
      where start_date between to_date('20220801', 'rrrrmmdd') and (to_date('20221031', 'rrrrmmdd') + 1)
      group by car_id
      having count(*) >= 5)
   group by extract(month from start_date), car_id
   having count(*) != 0
   order by month asc, car_id desc;

---

이 문제는
EXTRAT()함수, WHERE절 서브쿼리, GROUP BY절
위 3가지를 핵심으로 사용하여 해결할 수 있다. 

 

 

■ EXTRACT() 함수

EXTRACT함수는 날짜형 data type을 가진 특정 데이터로부터

원하는 날짜 정보만 추출하여, 새로운 컬럼의 형태로 추출해주는 함수이다.

select EXTRACT( [날짜요소] FROM [특정컬럼B] ) as [별칭]
    from [테이블명A];

 

특정 테이블 A에 있는 컬럼 중 날짜 변수가 담긴 특정컬럼B로부터 

원하는 날짜요소를 추출하여 새로운 컬럼으로 추출해 주는 쿼리 문이다.

 

- 날짜요소 : YEAR, MONTH, DAY, HOUR, MINUTE, SECOND

(자세한 예시와 실습은 추후 게시물로 업로드할 예정)

하지만 주의해야 할 점은, 
날짜형 데이터 타입 중 DATETIME 타입은
EXTRACT를 활용한 HOUR, MINUTE, SECOND 추출은 할 수 없다는 것이다.

Q. 그럼 어떻게 추출해야 하는가?
DATETIME의 형식을 TIMESTAMP로 일시적으로 변경해 주면 된다. 
그럴 때 사용할 수 있는 함수는 CAST() 함수이다.

 

관련문제는 아래 링크를 통해 풀어볼 수 있다.

<프로그래머스 37> 입양 시각 구하기1(Lv.2)

 

 

■ TO_DATE( [컬럼명/문자열], '변경할 데이터 형태')   =  Oracle
   위의 함수를 사용해서 문자 혹은 숫자 데이터 형식을 → 날짜형 데이터 형식으로 변환한다.

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

 

 

■ STR_TO_DATE( [컬럼명/문자열], '변경할 데이터 형태')   = MySQL

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

 

   * 표시한 형태 해석(대소문자 구분 있음)
   - %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)

 

---

♥ 오라클 해설해보기

 

 #WHERE절 서브쿼리 

WHERE절 서브쿼리를 사용해서

대여 시작일(START_DATE)을 기준으로 2022년 8월 ~ 2022년 10월까지

총 대여횟수가 5회 이상인 자동차들에 대해서 출력한다.

 

 

● BETWEEN .. AND

BETWEEN .. AND 조건을 사용하여,

2022년 8월 1일 ~ 2022년 10월  31일 데이터만 추출한다.

이때 날짜 조건을 주기 위해서 to_date함수를 이용하여

문자형 데이터를 날짜형 데이터로 바꿔주는 것이다.

 

여기서 주의할 점은 DATE TYPE의 기본 구조는

년월일 시간:분:초 인데, 시간:분:초가 00:00:00으로 되어있다는 것이다.

따라서 2022년 10월 31일 00:00:00초까지의 값만 조건으로 걸러지는 것이기 때문에

+ 1을 해주어야 한다. 

왜냐하면 2022년 10월 31일 23:59:59초에 판매된 데이터도 포함되게 해야 하기 때문이다.

 

그리고 아래와 같이 해당 기간별 car_id별 총 대여횟수를 count(*) 하여 having절로 5회 이상인 자동차에 대해서만 출력할 수 있도록 쿼리문을 작성해준다.

(select car_id
    from car_rental_company_rental_history
    where start_date between to_date('20220801', 'rrrrmmdd') and (to_date('20221031', 'rrrrmmdd') + 1)
    group by car_id
    having count(*) >= 5)

 

대여 시작일(START_DATE)을 기준으로 2022년 8월 ~ 2022년 10월까지

총 대여횟수가 5회 이상인 자동차_id에 대해서 출력되게 되고, 이것을 메인쿼리에서 조건에 사용해줄 것이다.

 

 

 #Main query 

해당 기간 동안의 월 별(month) 자동차_ID(car_id) 별 총 대여횟수(records)를 출력해야한다.

 

처음에 설명한 것을 토대로  month 컬럼을 생성한다.

날짜형 컬럼인 start_date 컬럼에서 month만 추출하기 위해서는 

extract 함수를 써줘야한다.

그럼 아래와 같이 컬럼을 생성할 수 있다.

 

extract(month from start_date) as month

 

그리고 월별 자동차ID별 총 대여횟수를 구해야하는 것이기 때문에

GROUP BY절에서 MONTH와 CAR_ID컬럼을 묶어주고,

그에 해당하는 대여횟수를 COUNT(*)로 구하여 RECORD 컬럼을 생성해준다.

 

이때 총 대여횟수가 0 인 경우는 결과에서 제외해야하기 때문에

아래와 같이 HAVING절에서 조건을 준다.

 

having record != 0 

 

 

-- where절 조건 첫번째 --

 

'해당 기간 동안' 이라는 조건이 있기 때문에

아래의 조건을 메인쿼리에서 한번 더 작성해준다.

 

where start_date between to_date('20220801', 'rrrrmmdd') and (to_date('20221031', 'rrrrmmdd') + 1)

 

 

 

-- where절 조건 두번째 --

 

대여 시작일(START_DATE)을 기준으로 2022년 8월 ~ 2022년 10월까지

총 대여횟수가 5회 이상인 자동차_id에 대해서만 출력해야하기 때문에

 

where절 서브쿼리에서 위의 조건을 완성하여 그 조건에 해당하는 car_id를 구했다.

where절의 출력결과인 car_id는 다중행이기 때문에 equal(=) 이 아닌 in으로 

car_id가 where절에서 출력되는 car_id에 해당하도록 아래와 같이 조건을 작성하는데,

조건은 첫번째 조건과 and로 연결해준다.

and car_id in (select car_id
        from car_rental_company_rental_history
        where start_date between to_date('20220801', 'rrrrmmdd') and (to_date('20221031', 'rrrrmmdd') + 1)

 

마지막으로 month 기준으로 asc하게 정렬해준 뒤

month가 같다면, car_id를 기준으로 desc하게 정렬해준다.

 

 

---

♥ MySQL 정답

select month(start_date) as month, car_id, count(*) record
    from car_rental_company_rental_history
    where start_date between str_to_date('20220801', '%Y%m%d') and str_to_date('20221031', '%Y%m%d')
    and car_id in (select car_id
        from car_rental_company_rental_history
        where start_date between str_to_date('20220801', '%Y%m%d') and str_to_date('20221031', '%Y%m%d')
        group by car_id
        having count(*) >= 5)
    group by month(start_date), car_id
    having count(*) != 0
    order by month asc, car_id desc;

 

♥ MySQL 해설

 

설명은 ORACLE과 같으나 ORACLE과의 차이점은

MONTH(START_DATE) 를 사용하여 월을 출력하는 것과,

숫자 혹은 문자형 데이터를 날짜형 데이터타입으로 변경해주는 함수가

TO_DATE가 아니라 STR_TO_DATE()라는 것이다.

 

 

 

 

 

결과 출력

((이하생략))

 

 

 

 

 

 

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