SQL 문제풀이: 데이터의 그룹별 평균 기간 뽑기, DATEDIFF/ AVG

 

 

<데이터 체크>

<

문제 출처: 프로그래머스, 자동차 평균 대여 기간 구하기

테이블명: car_rental_company_rental_history어느 자동차 대여점의 대여 기록 테이블 견본 

컬럼명: history_id대여 기록 아이디, car_id자동차 아이디, start_date대여시작일, end_date대여종료일

데이터베이스 구성: 1개 테이블, 4개 컬럼, 160개 로우

>

>

 

 

 

 

<시나리오>

 

여기 어떤 자동차 대여점이 있다. 이 자동차 대여점에서는 기존 고객들을 확실히 잡아두기 위해 주 고객층에 최대한의 가격 메리트를 부여하는 방향으로 가격 정책을 개편하려고 한다. 이번 EDA탐색적 데이터 분석는 이를 위한 고객 세분화 과정이다. 평균 대여 기간이 7일 이상인 자동차들의 평균 대여 기간(컬럼명: average_duration) 리스트를 출력하는 SQL문을 작성하려면 어떻게 해야 할까?

 

 

 

 

 

 

<풀이>

 

먼저 문제를 해소하기 위해서는 전체 리스트의 평균 대여 기간을 리스트화해야 한다. 파편화되어 있는 데이터를 그룹으로 묶어 주고,

 

 

GROUP BY car_id

 

 

이 데이터의 기준으로 평균 대여 기간을 뽑는다. 대여 기간은 대여종료일과 대여시작일의 차로 계산될 수 있는데, 이 때 대여시작일 역시 하루로 계산되기 때문에 꼭 1을 더해 줘야 한다. 이 값에 평균을 적용해 주고 가독성을 위해 소수점 첫째 자리까지 반올림하는 쿼리를 다음과 같이 짤 수 있다.

 

 

SELECT car_id
	, ROUND(AVG(DATEDIFF(end_date, start_date) + 1), 1) average_duration

 

 

이 중에서 평균 대여 기간이 7일 이상인 값을 뽑으려면 HAVING에 조건을 추가해주면 된다. WHERE가 아닌 HAVING에 넣는 이유는 그룹의 집계값(AVG) 기준으로 연산해야 하기 때문이다.

 

 

HAVING average_duration >= 7

 

 

마무리하면, 완성된 식은 아래와 같을 것이다.

 

 

SELECT car_id
        , ROUND(AVG(DATEDIFF(end_date, start_date) + 1), 1) average_duration
FROM car_rental_company_rental_history
GROUP BY car_id
HAVING average_duration >= 7
ORDER BY average_duration DESC, car_id DESC