문제 설명
다음은 어느 자동차 대여 회사의 자동차 대여 기록 정보를 담은 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 테이블에서 평균 대여 기간이 7일 이상인 자동차들의 자동차 ID와 평균 대여 기간(컬럼명: AVERAGE_DURATION) 리스트를 출력하는 SQL문을 작성해주세요. 평균 대여 기간은 소수점 두번째 자리에서 반올림하고, 결과는 평균 대여 기간을 기준으로 내림차순 정렬해주시고, 평균 대여 기간이 같으면 자동차 ID를 기준으로 내림차순 정렬해주세요.
예시
예를 들어 CAR_RENTAL_COMPANY_RENTAL_HISTORY 테이블이 다음과 같다면
| HISTORY_ID | CAR_ID | START_DATE | END_DATE |
|---|---|---|---|
| 1 | 1 | 2022-09-27 | 2022-10-01 |
| 2 | 1 | 2022-10-03 | 2022-11-04 |
| 3 | 2 | 2022-09-05 | 2022-09-05 |
| 4 | 2 | 2022-09-08 | 2022-09-10 |
| 5 | 3 | 2022-09-16 | 2022-10-15 |
| 6 | 1 | 2022-11-07 | 2022-12-06 |
자동차 별 평균 대여 기간은
- 자동차 ID가 1인 자동차의 경우, 대여 기간이 각각 5일, 33일, 30일인 기록이 존재하므로 평균 22.7일
- 자동차 ID가 2인 자동차의 경우, 대여 기간이 각각 1일, 3일인 기록이 존재하므로 평균 2일
- 자동차 ID가 3인 자동차의 경우, 대여 기간이 30일인 기록만 존재하므로 평균 30일 입니다. 평균 대여 기간이 7일 이상인 자동차는 자동차 ID가 1, 3인 자동차이고, 평균 대여 기간 내림차순 및 자동차 ID를 기준으로 내림차순 정렬하면 다음과 같이 나와야 합니다.
| CAR_ID | AVERAGE_DURATION |
|---|---|
| 3 | 30.0 |
| 1 | 22.7 |
출처: 프로그래머스 코딩 테스트 연습, https://school.programmers.co.kr/learn/challenges
✔️ 문제접근
TIMESTAMPDIFF()를 사용해서 날짜 차를 구한다. 이때, 날짜 차는END_DATE-START_DATE+1이기 때문에, +1을 해준다.AVG()함수를 사용해 평균을 구해준다.ROUND()함수를 사용해 소숫점 둘째자리에서 반올림하도록 해준다.CAR_ID컬럼 기준GROUP BY를 진행한다.- 평균 기간이 7일 이상이어야 하므로
HAVING절에 조건을 걸어준다. - 문제에서 제시한대로
ORDER BY를 진행한다.
⭐️ 내 정답
SELECT CAR_ID, ROUND(AVG(TIMESTAMPDIFF(DAY, START_DATE, END_DATE)+1),1) AS AVERAGE_DURATION
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
GROUP BY CAR_ID
HAVING AVERAGE_DURATION >= 7
ORDER BY AVERAGE_DURATION DESC, CAR_ID DESC;🔎 짚고 넘어가기
DATEDIFF vs TIMESTAMPDIFF: 날짜 차이를 계산하는 두 가지 방법
MySQL에서 날짜나 시간 간의 차이를 계산할 때 자주 사용하는 함수로는 DATEDIFF와 TIMESTAMPDIFF가 있습니다. 이 두 함수는 비슷한 목적을 가지고 있지만, 사용법과 반환 값에서 차이가 있습니다.
1. DATEDIFF
DATEDIFF는 두 날짜 간의 일 단위 차이를 계산하는 간단한 함수입니다. 시간 부분은 무시되며, 오직 날짜만을 비교합니다. 반환 값은 두 날짜 사이의 일 수입니다.
DATEDIFF('2024-08-01', '2024-07-25');
# 72. TIMESTAMPDIFF
TIMESTAMPDIFF는 두 날짜 또는 시간 간의 차이를 특정 단위(년, 월, 일, 시 등)로 계산할 수 있는 좀 더 유연한 함수입니다. 첫 번째 인자로 비교 단위를 지정할 수 있어, 보다 다양한 비교가 가능합니다.
TIMESTAMPDIFF(DAY, '2024-08-01 10:00:00', '2024-07-25 15:30:00');
# 7
TIMESTAMPDIFF(HOUR, '2024-08-01 10:00:00', '2024-07-25 15:30:00')
# 186