(다 푼 후 사진 추가 예정)
Level 3이상인 문제, 특정 함수가 처음 사용되는 문제, 잘 풀지 못했던 문제 위주로 작성하겠다.
자동차 평균 대여 기간 구하기
자동차 대여 기록 정보 테이블(CAR_RENTAL_COMPANY_RENTAL_HISTORY)이 주어진다.
평균 대여 기간이 7일 이상인 자동차들의 자동차 ID와 평균 대여 기간을 구하여라. 이때 평균 대여 기간은 소수점 둘째 자리에서 반올림한다.
결과는 평균 대여 기간의 내림차순, 자동차 ID의 내림차순으로 나타낸다.
나의 풀이
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- 자동차 ID로 그룹화하여 평균 대여 기간을 구하고, having 절을 이용해 조건에 맞는 경우를 구하였다.
- 이 문제에서 고민한 것은 크게 3가지인데, 두 날짜 간의 간격을 구하는 것, 대여 기간은 간격에 1을 더해야 한다는 점, 그리고 평균을 구할 때 avg함수를 사용하면 된다는 점이다.
- DATE 타입의 날짜 사이의 기간을 구할 때는 단순히-로 수행하면 안되고,DATEDIFF함수를 사용해야 한다. 인자의 순서도 중요하다.- 대여 기간은 datediff로 구한 기간에 1을 더해야 한다. 이는 문제의 예시에서도 확인할 수 있는데, 당일 대여하고 당일 반납하는 경우에 대여 기간이 1일로 계산되기 때문이다.
- 평균을 구할 때는
AVG함수를 사용하면 된다. 잠시 이를 까먹고 있어서SUM/COUNT로 계산하고 있었는데, 여기서는 하필 1을 또 더해줘야 하기 때문에 틀리기 쉽다. 그냥 avg를 사용하자.
조회수가 가장 많은 중고거래 게시판의 첨부파일 조회하기
중고거리 게시판 정보 테이블(USED_GOODS_BOARD)과 중고거래 게시판 첨부파일 정보 테이블(USED_GOODS_FILE)이 주어진다.
조회수가 가장 높은 게시물의 첨부파일 경로를 구하여라. 이때 첨부파일 경로는 다음과 같은 규칙으로 얻을 수 있다.
- 기본적인 파일경로는 /home/grep/src/ 이다.
- 게시글 ID를 기준으로 디렉토리가 구분된다.
- 파일이름은 파일 ID, 파일 이름, 파일 확장자로 구성된다.
결과는 첨부파일 경로의 내림차순으로 나타낸다.
나의 풀이
select concat("/home/grep/src/", BOARD_ID, "/", FILE_ID, FILE_NAME, FILE_EXT) as FILE_PATH
from USED_GOODS_FILE join USED_GOODS_BOARD using(BOARD_ID)
where VIEWS in (select max(VIEWS)
from USED_GOODS_BOARD)
order by FILE_ID desc- 조회수가 가장 높은 게시물의 조회수로 어떤 게시물이 해당되는지 찾는다. 이후 주어진 문자열을 합쳐 첨부파일 경로를 얻는다.
CONCAT함수를 사용하면 문자열을 합칠 수 있다. 해보니까 인자의 개수에도 제한이 없고, 계속 덧붙일 수 있다.
자동차 대여 기록 별 대여 금액 구하기
대여 중인 자동차들의 정보 테이블(CAR_RENTAL_COMPANY_CAR), 자동차 대여 기록 정보 테이블(CAR_RENTAL_COMPANY_RENTAL_HISTORY), 자동차 및 대여 기간 별 할인 정보 테이블(CAR_RENTAL_COMPANY_DISCOUNT_PLAN)이 주어진다.
이때 자동차 종류가 트럭인 자동차의 대여 기록 별 대여 금액을 계산해 대여 기록 ID와 대여 금액을 구하여라.
결과는 대여 금액의 내림차순, 대여 기록 ID의 내림차순으로 나타낸다.
나의 풀이
select HISTORY_ID, round(DAILY_FEE*RENTAL_DURATION*(100-DISCOUNT_RATE)/100, 0) as FEE
from (select A.HISTORY_ID, B.DAILY_FEE, A.RENTAL_DURATION, ifnull(C.DISCOUNT_RATE, 0) as DISCOUNT_RATE
from (select HISTORY_ID, CAR_ID, datediff(END_DATE, START_DATE)+1 as RENTAL_DURATION,
case when datediff(END_DATE, START_DATE)+1 >= 90 then "90일 이상"
when datediff(END_DATE, START_DATE)+1 >= 30 then "30일 이상"
when datediff(END_DATE, START_DATE)+1 >= 7 then "7일 이상"
else "해당 없음"
end as DURATION_TYPE
from CAR_RENTAL_COMPANY_RENTAL_HISTORY) A join CAR_RENTAL_COMPANY_CAR B using(CAR_ID)
left join CAR_RENTAL_COMPANY_DISCOUNT_PLAN C on(A.DURATION_TYPE=C.DURATION_TYPE and B.CAR_TYPE=C.CAR_TYPE)
where B.CAR_TYPE like "트럭") A
order by FEE desc, HISTORY_ID desc- 각 대여 기록의 기간을 얻고, 각 기간이 해당되는 기간 타입을 구한다. 이후 세 테이블을 조인하여 필요한 정보를 얻어 대여 금액을 계산한다. 이때 할인에 해당되지 않는 대여 기록을 위해 left join을 사용했다.
- 생각난대로 풀이한 건데, 지금 보니까 가장 먼저 트럭인 경우의 대여 기록을 얻은 다음에 대여 기간을 계산하는게 더 효율적일 것 같다.
(추가 예정)
