Level 3 - 어려운 편

문제

해당 문제 링크

정답

[1] - 더 간단

SELECT MONTH(START_DATE) AS MONTH, CAR_ID, COUNT(*) AS RECORDS  FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
WHERE CAR_ID IN (SELECT  CAR_ID FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY 
                    WHERE (DATE_FORMAT(START_DATE, '%Y-%m') BETWEEN '2022-08' AND '2022-10')
                    GROUP BY CAR_ID
                    HAVING COUNT(CAR_ID) >=5)
    AND (DATE_FORMAT(START_DATE, '%Y-%m') BETWEEN '2022-08' AND '2022-10')
GROUP BY MONTH, CAR_ID
HAVING RECORDS > 0
ORDER BY MONTH ASC, CAR_ID DESC

코드 참고 사이트(1)

[2]

SELECT MONTH(HISTORY.START_DATE) AS MONTH, HISTORY.CAR_ID, COUNT(*) AS RECORDS FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY AS HISTORY
INNER JOIN ( SELECT CAR_ID, COUNT(*) AS CNT FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
                WHERE YEAR(START_DATE) = 2022 AND MONTH(START_DATE) >= 8 AND MONTH(START_DATE) <= 10 GROUP BY CAR_ID) SUB_HISTORY
ON HISTORY.CAR_ID = SUB_HISTORY.CAR_ID WHERE SUB_HISTORY.CNT >= 5 AND YEAR(HISTORY.START_DATE) = 2022 AND MONTH(HISTORY.START_DATE) >= 8 AND MONTH(HISTORY.START_DATE) <= 10
GROUP BY HISTORY.CAR_ID, MONTH(HISTORY.START_DATE)
ORDER BY MONTH(HISTORY.START_DATE) ASC, HISTORY.CAR_ID DESC

코드 참고 사이트(2)

풀이

2시간동안 문제를 읽고 써보았지만, 잘 모르겠어서 코드를 참고하여 공부하며 작성했다.

문제를 봐보자

CAR_RENTAL_COMPANY_RENTAL_HISTORY 테이블에서 대여 시작일을 기준으로 2022년 8월부터 2022년 10월까지 총 대여 횟수가 5회 이상인 자동차들에 대해서 해당 기간 동안의 월별 자동차 ID 별 총 대여 횟수(컬럼명: RECORDS) 리스트를 출력하는 SQL문을 작성해주세요. 결과는 월을 기준으로 오름차순 정렬하고, 월이 같다면 자동차 ID를 기준으로 내림차순 정렬해주세요. 특정 월의 총 대여 횟수가 0인 경우에는 결과에서 제외해주세요.

하나씩 코드를 작성해보자

CAR_RENTAL_COMPANY_RENTAL_HISTORY 테이블에서 대여 시작일을 기준으로 2022년 8월부터 2022년 10월까지 총 대여 횟수가 5회 이상인 자동차들에 대해서 해당 기간 동안의 월별 자동차 ID 별 총 대여 횟수(컬럼명: RECORDS) 리스트를 출력하는 SQL문을 작성해주세요.

• CAR_RENTAL_COMPANY_RENTAL_HISTORY 테이블에서 대여 시작일을 기준으로 2022년 8월부터 2022년 10월까지 총 대여 횟수가 5회 이상인 자동차들에 대해서 =>

SELECT  CAR_ID FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY 
                    WHERE (DATE_FORMAT(START_DATE, '%Y-%m') BETWEEN '2022-08' AND '2022-10')
                    GROUP BY CAR_ID
                    HAVING COUNT(CAR_ID) >=5)

• 해당 기간 동안의 월별 자동차 ID 별 총 대여 횟수(컬럼명: RECORDS) 리스트를 출력하는 SQL문
  => 해당 기간 동안의 월별
  => 자동차 ID 별
  => 총 대여 횟수

SELECT MONTH(START_DATE) AS MONTH, CAR_ID, COUNT(*) AS RECORDS  FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
WHERE CAR_ID IN (SELECT  CAR_ID FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY 
                    WHERE (DATE_FORMAT(START_DATE, '%Y-%m') BETWEEN '2022-08' AND '2022-10')
                    GROUP BY CAR_ID
                    HAVING COUNT(CAR_ID) >=5)
    AND (DATE_FORMAT(START_DATE, '%Y-%m') BETWEEN '2022-08' AND '2022-10')

• 결과는 월을 기준으로 오름차순 정렬하고, 월이 같다면 자동차 ID를 기준으로 내림차순 정렬해주세요. =>

ORDER BY MONTH ASC, CAR_ID DESC

• 특정 월의 총 대여 횟수가 0인 경우에는 결과에서 제외해주세요. =>

GROUP BY MONTH, CAR_ID
HAVING RECORDS > 0

2022년 8월 ~ 2022년 10월에 해당하는 조건 두 번 사용

대여 시작일이 8월 ~ 10월 사이가 아닌 데이터들이 존재한다면,메인 쿼리에 8월부터 10월 사이에 대한 WHERE 조건문이 없다면 COUNT(HISTORY_ID)의 집계값은 7월 케이스의 COUNT와 8월 ~ 10월 사이 케이스의 COUNT를 합한 값이 출력될 것이다.
즉, 출력되는 COUNT(HISTORY_ID)도 8월 ~ 10월 사이에 기간으로 제한을 둬야 문제의 조건에 맞게 8월 ~ 10월 사이에 대한 집계만 진행된다는 것을 알 수 있다.

---

문법

날짜 관련 함수

• DATE_FORMAT()
  MONTH = %m(두 글자) %c(한 글자)
• DATE() : 날짜 부분 반환
• YEAR() : 년도 리턴, 범위 1000~9999
• MONTH() : 월 리턴, 범위 1~12
• DAY() : 일 리턴, 1~31
• HOUR() : 시간 리턴, 범위 1~24
• MINUTE() : 분 리턴, 범위 1~59

BETWEEN A AND B

A와 B를 사이 모든 값 해당 여부를 조건으로 한다.

JOIN

결합연산 (JOIN)
: 테이블을 가로 방향으로 붙이는 연산
1. LEFT OUTER JOIN: 왼쪽 테이블을 기준으로 합쳐짐 (왼쪽 테이블에 존재하는 row만 보여짐)
2. RIGHT OUTER JOIN: 오른쪽 테이블을 기준으로 합쳐짐 (오른쪽 테이블에 존재하는 row만 보여짐)
3. INNER JOIN: 왼쪽, 오른쪽 테이블 모두에 존재하는 row만 추려서 합쳐짐 (교집합 개념)
MySQL에서는 JOIN, INNER JOIN, CROSS JOIN이 모두 같은 의미로 사용

ON

JOIN을 하기 전 필터링을 한다.
: ON 조건으로 필터링이 된 레코들간 JOIN이 이루어진다.
cf. WHERE : JOIN을 한 후 필터링을 한다. (JOIN을 한 결과에서 WHERE 조건절로 필터링이 이루어진다)