[SQL문제다시풀기] 대여 횟수가 많은 자동차들의 월별 대여 횟수 구하기

silver

Feb 18, 2025

[SQL문제다시풀기] 대여 횟수가 많은 자동차들의 월별 대여 횟수 구하기

Contents

문제 MYSQL ORACLE

문제

school.programmers.co.kr

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

MYSQL

내가 작성한 정답1

: start_date between '2022-08-01' and '2022-10-31'을 두번 작성해야했다.


select month(start_date) month, car_id, count(history_id) records
from CAR_RENTAL_COMPANY_RENTAL_HISTORY
where car_id in (select car_id 
                 from CAR_RENTAL_COMPANY_RENTAL_HISTORY
                 where start_date between '2022-08-01' and '2022-10-31'
                 group by car_id
                 having count(history_id) >= 5
                )
    and start_date between '2022-08-01' and '2022-10-31'
group by 1,2
// group by를 할 때 특정 월의 count가 0이면 아예 해당 월이 결과에서 제외되므로 삭제해도 됨
// -> group by는 존재하는 데이터만 집계한다
having count(history_id) <> 0
order by 1 asc, 2 desc

내가 작성한 정답2 :

cte를 사용하여 반복을 줄였다 → 저번에 작성한 정답과 거의 일치


with r as (select month(start_date) month, car_id, count(history_id) records
            from CAR_RENTAL_COMPANY_RENTAL_HISTORY
            where start_date between '2022-08-01' and '2022-10-31'
            group by 1,2)
select month, car_id, records
from r
where car_id in (select car_id from r group by car_id having sum(records) >= 5)
order by 1 asc, 2 desc

ORACLE

내가 작성한 정답1


select extract(month from start_date) month, car_id, count(history_id) records
from CAR_RENTAL_COMPANY_RENTAL_HISTORY
where to_char(start_date,'yymmdd') between '220801' and '221031'
    and car_id in (select car_id
                  from CAR_RENTAL_COMPANY_RENTAL_HISTORY
                  where to_char(start_date,'yymmdd') between '220801' and '221031'
                  group by car_id
                  having count(history_id) >= 5)
group by extract(month from start_date), car_id
order by month asc, car_id desc

내가 작성한 정답2 :

cte를 사용하여 반복을 줄였다


with a as (select extract(month from start_date) month, car_id, count(history_id) records
           from CAR_RENTAL_COMPANY_RENTAL_HISTORY
           where to_char(start_date,'yymmdd') between '220801' and '221031'
           group by extract(month from start_date), car_id)
select month, car_id, records
from a
where car_id in (select car_id from a group by car_id having sum(records) >= 5)
order by month asc, car_id desc

💡

COUNT, SUM, AVG 등의 집계 함수들은 중첩해서 사용할 수 없다

💡

group by는 존재하는 데이터만 집계한다 → 결과값이 0이면 출력되지 않는다.

select month(start_date) month, car_id, count(history_id) records from CAR_RENTAL_COMPANY_RENTAL_HISTORY where car_id in (select car_id from CAR_RENTAL_COMPANY_RENTAL_HISTORY where start_date between '2022-08-01' and '2022-10-31' group by car_id having count(history_id) >= 5 ) and start_date between '2022-08-01' and '2022-10-31' group by 1,2 // group by를 할 때 특정 월의 count가 0이면 아예 해당 월이 결과에서 제외되므로 삭제해도 됨 // -> group by는 존재하는 데이터만 집계한다 having count(history_id) <> 0 order by 1 asc, 2 desc

with r as (select month(start_date) month, car_id, count(history_id) records from CAR_RENTAL_COMPANY_RENTAL_HISTORY where start_date between '2022-08-01' and '2022-10-31' group by 1,2) select month, car_id, records from r where car_id in (select car_id from r group by car_id having sum(records) >= 5) order by 1 asc, 2 desc

select extract(month from start_date) month, car_id, count(history_id) records from CAR_RENTAL_COMPANY_RENTAL_HISTORY where to_char(start_date,'yymmdd') between '220801' and '221031' and car_id in (select car_id from CAR_RENTAL_COMPANY_RENTAL_HISTORY where to_char(start_date,'yymmdd') between '220801' and '221031' group by car_id having count(history_id) >= 5) group by extract(month from start_date), car_id order by month asc, car_id desc

with a as (select extract(month from start_date) month, car_id, count(history_id) records from CAR_RENTAL_COMPANY_RENTAL_HISTORY where to_char(start_date,'yymmdd') between '220801' and '221031' group by extract(month from start_date), car_id) select month, car_id, records from a where car_id in (select car_id from a group by car_id having sum(records) >= 5) order by month asc, car_id desc