자동차 평균 대여 기간 구하기

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

• 문제 풀이
  • 대여 기간을 계산하여 중첩 쿼리를 사용해 평균 대여 기간이 7일 이상인 자동차들을 구한다.

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

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

  
  

  • 중첩 쿼리를 사용하지 않고 직접 HAVING 구문에서 평균 대여 기간이 7일 이상인 것을 필터링
    • NVL 함수를 사용하여 NULL 값을 0으로 변환

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

• 실행 결과