대여 횟수가 많은 자동차들의 월별 대여 횟수 구하기
▶ 문제 POINT : 조건에 맞는 날짜 필터링 & 그룹핑
[문제]
CAR_RENTAL_COMPANY_RENTAL_HISTORY 테이블에서 대여 시작일을 기준으로 2022년 8월부터 2022년 10월까지 총 대여 횟수가 5회 이상인 자동차들에 대해서 해당 기간 동안의 월별 자동차 ID 별 총 대여 횟수(컬럼명: RECORDS) 리스트를 출력하는 SQL문을 작성해주세요. 결과는 월을 기준으로 오름차순 정렬하고, 월이 같다면 자동차 ID를 기준으로 내림차순 정렬해주세요. 특정 월의 총 대여 횟수가 0인 경우에는 결과에서 제외해주세요.
[조건]
1. 대여 시작일 기준 2022-08~2022-10월 까지 총 대여횟수가 5회 이상인 자동차
2. 월별 자동차 ID 당 총 대여 횟수
3. 정렬 기준
4. 특정 월의 총 대여 횟수가 0인 경우는 제외
▶ 문제 해결 & 피드백 POINT : 서브쿼리로 날짜 조건 필터링 & 그룹바이로 집계
[풀이 과정]
1. WHERE 절 서브쿼리로 조건기간에 들어가지 않는 대여 횟수 제외. 날짜 조건 이므로 형식 변환하여 진행 → `서브쿼리에서 DATE_FORMAT` 함수 조건 사용이유, `MONTH` 함수 사용시 기간외 년도의 월도 포함될수있어 이를 방지하기 위해
2. 그룹바이 & COUNT 사용하여 월별 자동차 대여 횟수 집계
3. 특정 월의 총 대여 횟수가 0인 경우는 제외하므로 `WHERE` 절에 해당 월의 대여 기록이 없는 경우 제외
4. 정렬 조건 적용
SELECT MONTH(start_date) AS 'MONTH'
,CAR_ID
,COUNT(HISTORY_ID) AS RECORDS
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
WHERE CAR_ID IN (SELECT car_id -- 조건기간내 대여횟수가 5이상인 자동차 필터링
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
WHERE DATE_FORMAT(start_date,'%Y-%m-01') BETWEEN '2022-08-01' AND '2022-10-01'
GROUP BY car_id
HAVING COUNT(HISTORY_ID) >=5 -- PK니까 DISTINCT 생략
)
AND MONTH(start_date) IN (8,9,10) -- 특정 월의 총 대여횟수가 0이 아닌 경우
GROUP BY month(start_date),car_id
ORDER BY 1,2 desc
[오류 사항 & 해결 ]
- 조건사항 잘 못 이해하여 오류
- 문제에서 ` 특정 월의 총 대여 횟수가 0인 경우` 는 자동차별 해당기간(8,9,10월)의 대여 기록이 없는 경우를 제외하라는 말이 아닌, 해당 기간(8,9,10월)에 대여기록이 아예없는 경우 월 자체를 제외하라는 의미임.
- 자동차 중 8,9,10월의 대여기록이 한번이라도 없으면 제외하라는 의미로 문제를 해석하여 아래와 같이 문제를 풀었으나,
-- 자동차별 조건 기간(8,9,10 월) 내 대여기록이 한번이라도 없으면 제외하는 쿼리
WITH sub as (
SELECT car_id
,DATE_FORMAT(start_date,'%Y-%m-01') AS mnth
,COUNT(history_id) OVER (PARTITION BY car_id) AS records -- history_id는 pk 이므로 distinct 제외 / 월 살려주기 위해 윈도우씀
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
WHERE DATE_FORMAT(start_date,'%Y-%m-01') BETWEEN '2022-08-01' AND '2022-10-01'
)
SELECT MONTH(mnth) AS MONTH
,car_id
,records
FROM sub
WHERE records>=5
AND car_id IN (SELECT car_id FROM sub GROUP BY car_id HAVING COUNT(DISTINCT mnth)=3) -- 자동차별 조건 월에 대여기록이 한번이라도 있으면
GROUP BY month,car_id
ORDER BY 1,2 DESC
- 결론적으로 8,9,10에 대여기록이 없으면 해당 월 제외 라는 의미였기 때문에, 문제 조건에서 월 기준 조건 월이 다 포함된다는 `WEHRE MONTH(start_date) IN (8,9,10) ` 조건을 추가하여 해결
[피드백]
- 문제조건을 잘못 이해하여 질의 응답 포함 푸는데 2틀이나 걸린 문제.
- 문제가 어려워질 수록 에러상황보다는 결과는 제대로 나오지만, 오답인 경우가 잦아짐.
→ 쿼리에 오류가 없는 경우 문제를 잘 해석했는지 검토해볼 필요가 있음.
입양 시각 구하기(2)
▶ 문제 POINT : 테이블내 없는 시간 추가하기
[문제]
보호소에서는 몇 시에 입양이 가장 활발하게 일어나는지 알아보려 합니다. 0시부터 23시까지, 각 시간대별로 입양이 몇 건이나 발생했는지 조회하는 SQL문을 작성해주세요. 이때 결과는 시간대 순으로 정렬해야 합니다.
[조건]
1. 입양 시간대 별 입양 건수
2. 시간 순 정렬
3. 단, 0-23시까지 전부 출력. 해당 시간에 입양건이 없으면 전부 '0' 값 출력.
▶ 문제 해결 & 피드백 POINT : 재귀적 CTE & JOIN 활용하여 빈 시간대(없는 시간대)행 채워주기
[풀이 과정]
1. 테이블 내 빈 시간대 확인. 행 추가 하여 0 값 삽입하기 : 재귀적 CTE 정의하여 0-23시까지 행 생성 → 정의한 CTE LEFT JOIN & 그룹바이로 입양 건수 집계
WITH RECURSIVE cte(n) AS(
SELECT 0
UNION ALL
SELECT n+1
FROM cte
WHERE n<23
)
SELECT c.n AS 'HOUR'
,COUNT(a.ANIMAL_ID) AS 'COUNT'
FROM cte c
LEFT JOIN ANIMAL_OUTS a ON c.n= HOUR(a.DATETIME)
GROUP BY c.n
ORDER BY 1
[오류 사항 & 해결 ]
- 그룹바이 시, CTE로 생성한 월을 참조하지 않고, 본 테이블 시간을 참조함. → CTE로 생성한 모든행이 다 안나와서 당황 → 그룹바이 조건 수정후 해결 완료
▶ 재귀적 CTE(WITH REVURSIVE) REMIND
✔️ 재귀적 CTE 란 ?
- 재귀적 CTE(Common Table Expression)는 MySQL에서 계층적 데이터나 반복적인 데이터를 처리할 때 사용되는 기능.
( 즉 , 테이블을 기반으로 재귀적으로 반복해주는 구문)
- MySQL 8.0부터 지원
- 행을 만들때 유용 ( EX. 누락 월 생성)
-- 재귀적 CTE 기본 형태1: 새롭게 생성할 경우 --
WITH RECURSIVE 테이블명 (컬럼명) AS (-- 재귀 호출의 기초가 되는 초기 쿼리
SELECT 초기값 -- 반복을 시작할 첫번째 값
UNION ALL -- 재귀적으로 호출되는 쿼리
SELECT 반복 생성할 쿼리
WHERE 반복 조건(종료 조건) -- FALSE가 나오면 반복종료!! where, limit 등 사용
)
SELECT *
FROM 테이블명;