오늘은 sql문을 공부해보고 싶어 sql 문을 풀어봤습니다.
https://school.programmers.co.kr/learn/courses/30/lessons/157342
문제
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 |
문제 풀이
간만에 하니까 기억이 잘 안나 sql문의 문법 순서를 한번 보고 지나가겠습니다.
문법 작성 순서
SELECT -> FROM -> WHERE -> GROUP BY -> HAVING -> ORDER BY
이제 문제에서 빌리는 날의 평균을 구해서 7일 이상 빌리는 차만 내림차순으로 정리하라고 했습니다.
먼저 빌리는 날을 구해야겠죠?
자동차 대여기간 구하기
TIMESTAMPDIFF(단위, 날짜1, 날짜2)
또는 DATEDIFF(날짜1, 날짜2) 로 구하면 되지만 나중에 쓰임이 더 많을 전자(TIMESTAMPDIFF)를 택하겠습니다.
단위에 들어갈 수 있는 것을 보여드리겠습니다.
- SECOND : 초
- MINUTE : 분
- HOUR : 시
- DAY : 일
- WEEK : 주
- MONTH : 월
- QUARTER : 분기
- YEAR : 연
저희는 일을 사용해야하니까 day를 사용하면 됩니다.
SELECT
CAR_ID,
AVG(TIMESTAMPDIFF(DAY, START_DATE, END_DATE)+1) AS AVERAGE_DURATION
FROM
CAR_RENTAL_COMPANY_RENTAL_HISTORY만 작성하면 하루가 비게 됩니다. 왜냐면 당일은 빠지기 때문입니다. 따라서 뒤에 +1을 하여 값을 구하고 평균을 만들어 줍니다.
차 번호끼리 묶기
위에 작성한 코드의 결과를 보면 한 가지 자동차만 나오기 때문에 차 번호를 group by를 사용하여 자동차끼리 묶습니다.
SELECT
CAR_ID,
AVG(TIMESTAMPDIFF(DAY, START_DATE, END_DATE)+1) AS AVERAGE_DURATION
FROM
CAR_RENTAL_COMPANY_RENTAL_HISTORY
GROUP BY
CAR_ID기간이 7일 이상인 차
처음에는 where 문에 익숙한지라 미친듯이 where문으로 작성해보려고 했지만 말이 안된다는 것을 깨달은 것이 문법의 순서 때문이었고, having을 가지고도 조건문을 작성할 수 있다는 것을 깨닫고는 바로 고쳤습니다. 왜냐하면 from까지만 작성하고 결과를 보면 한개만 나오는데 말이 안되는 것을 하고 있었더라고요.
SELECT
CAR_ID,
AVG(TIMESTAMPDIFF(DAY, START_DATE, END_DATE)+1) AS AVERAGE_DURATION
FROM
CAR_RENTAL_COMPANY_RENTAL_HISTORY
GROUP BY
CAR_ID
HAVING
AVERAGE_DURATION >= 7내림차순 정리
order by ~~~ 사용하면 됩니다.
SELECT
CAR_ID,
AVG(TIMESTAMPDIFF(DAY, START_DATE, END_DATE)+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이렇게 사용하면 정답!인줄 알았죠. 안될겁니다. 왜냐면 예시에는 소숫점 0.1 단위까지만 나오게 해야합니다.
평균 숫자 0.1 단위까지 반올림
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 DESCROUND를 사용해서 0.1 단위까지 나오게 반올림 해주면 완성입니다.
소감
where 문이 아닌 having문 , timestampdiff, round를 새로 사용하면서 새것을 배우고, group by, order by 를 사용하면서 옛것을 다시 복습하니 이 어찌 즐겁지 아니할까요. 온고지신의 느낌이 이렇게 즐겁다니 다음에도 재미난 문제로 돌아오겠습니다.
출처
https://extbrain.tistory.com/78
https://nohriter.tistory.com/129
https://extbrain.tistory.com/51
