[String, Date/LEVEL2] 자동차 평균 대여 기간 구하기 (MySQL/Oracle)

MySQL

SELECT    CAR_ID
        , ROUND(AVG(DATEDIFF(END_DATE, START_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

• 날짜 계산할 때 처음에 단순하게 END_DATE-START_DATE 이렇게 계산 했는데, 죽어도 안됨. 알고보니 DATEDIFF() 함수를 사용해야 했어야함. 
• 왜 DATEDIFF 함수를 사용함? -> DATEDIFF 함수는 일수로 계산하고 그냥 (-) 하면 초단위로 계산해서 DATEDIFF 함수가 더 정확함.

 

---

Oracle

SELECT CAR_ID
, ROUND(AVG(END_DATE-START_DATE+1), 1) AS AVERAGE_DURATION
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
GROUP BY CAR_ID
HAVING ROUND(AVG(END_DATE-START_DATE+1), 1) >= 7
ORDER BY AVERAGE_DURATION DESC, CAR_ID DESC
;