[26.02.20 코테(MySQL/Oracle)]-자동차 평균 대여 기간 구하기

Lv2. 자동차 평균 대여 기간 구하기 (MySQL)

링크: https://school.programmers.co.kr/learn/courses/30/lessons/157342?language=mysql

 

내가 작성한 코드

-- 풀이1 (DATEDIFF)
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

-- 풀이2 (TIMESTAMPDIFF)
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;

-- 풀이3 (WITH)
WITH RENTAL_DIFF AS (SELECT CAR_ID,
                            DATEDIFF(END_DATE, START_DATE) + 1 AS DURATION
                     FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY)

SELECT CAR_ID, 
       ROUND(AVG(DURATION), 1) AS AVERAGE_DURATION
FROM RENTAL_DIFF
GROUP BY CAR_ID
HAVING AVERAGE_DURATION >= 7
ORDER BY AVERAGE_DURATION DESC, CAR_ID DESC;

 

Oracle

-- (산술 연산(-))
SELECT CAR_ID,
       TO_CHAR(ROUND(AVG(END_DATE - START_DATE + 1), 1), 'FM9999.0') 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 ROUND(AVG(END_DATE - START_DATE + 1), 1) DESC, CAR_ID DESC;

 

날짜 계산 함수 (MySQL vs Oracle)

• +1을 더하는 이유
  • 대여 기간이나 근무 일수를 구할 때는 시작일 당일도 하루로 포함해야 하기 때문이다
• MySQL
  • DATEDIFF(끝, 시작)
  • TIMESTAMPDIFF(단위, 시작, 끝)
• Oracle
  • 함수를 사용하지 않고 산술연산으로 가능하다
  • 종료날짜 - 시작날짜
  • 정수값만 얻고싶다면 (정확히 '며칠'인지)
    • TRUNC(END_DATE) - TRUNC(START_DATE)