[SQL] 프로그래머스 예제 풀이 (231219)
66. USED_GOODS_BOARD와 USED_GOODS_FILE 테이블에서 조회수가 가장 높은 중고거래 게시물에 대한 첨부파일 경로를 조회하는 SQL문을 작성해주세요. 첨부파일 경로는 FILE ID를 기준으로 내림차순 정렬해주세요. 기본적인 파일경로는 /home/grep/src/ 이며, 게시글 ID를 기준으로 디렉토리가 구분되고, 파일이름은 파일ID, 파일 이름, 파일 확장자로 구성되도록 출력해주세요. 조회수가 가장 높은 게시물은 하나만 존재합니다.
게시물은 하나인데 파일 확장자가 달라서 출력 결과가 두 개 나오길래 결과가 하나만 나와야 하는 줄 알고 group by로 묶었다가 오답처리돼서 서브쿼리도 써보고 이것저것 해보다가 코드도 복잡해지고 시간을 많이 썼다.
select concat_ws('/', '/home/grep/src', board_id, file) FILE_PATH
from (select f.file_id, f.file_name, f.file_ext, b.board_id, concat(file_id, file_name, file_ext) file
from used_goods_board b inner join used_goods_file f on b.board_id=f.board_id
where views in (select max(views)
from used_goods_board)) a
order by file_id desc
서브쿼리를 한 번만 사용하는 방법으로 조금 간결한 코드를 다시 만들었다.
where in 서브쿼리를 사용해 조회수가 가장 높은 게시물을 찾고 문제에서 요구하는 파일경로를 concat_ws를 사용해 출력했다.
select concat_ws('/', '/home/grep/src', b.board_id, concat(f.file_id, f.file_name, f.file_ext)) FILE_PATH
from used_goods_board b inner join used_goods_file f on b.board_id=f.board_id
where b.views in (select max(views)
from used_goods_board)
order by f.file_id desc
67. 7월에는 아이스크림 주문량이 많아 같은 아이스크림에 대하여 서로 다른 두 공장에서 아이스크림 가게로 출하를 진행하는 경우가 있습니다. 이 경우 같은 맛의 아이스크림이라도 다른 출하 번호를 갖게 됩니다. 7월 아이스크림 총 주문량과 상반기의 아이스크림 총 주문량을 더한 값이 큰 순서대로 상위 3개의 맛을 조회하는 SQL문을 작성해주세요.
7월의 총 주문량과 상반기 총 주문량 테이블을 합쳐준 뒤 두 테이블의 총주문량을 더하고 값이 큰 순서대로 정렬한 뒤 limit을 이용해 상위 3개의 맛을 출력했다. 서브 쿼리를 너무 많이 써서 복잡해 보인다.
select flavor
from (select flavor, sum(sum_order)
from (select flavor, sum(total_order) sum_order
from first_half
group by 1
union
select flavor, sum(total_order) sum_order
from july
group by 1) a
group by 1
order by 2 desc) b
limit 3
union으로 테이블을 합쳐줄 때 sum 함수를 빼고 order by에서 바로 sum을 넣어주면 더 간결한 코드가 완성된다.
select flavor
from (select * from FIRST_HALF
union
select * from JULY) a
group by 1
order by sum(total_order) desc
limit 3
rank 함수를 사용하는 코드도 만들어봤다. rank 함수는 조건절에 바로 사용할 수 없어서 서브쿼리로 한번 묶어줘야 한다.
select flavor
from (select flavor, rank() over(order by sum(total_order) desc) rnk
from (select * from july
union
select * from first_half) a
group by 1) b
where rnk<=3
69. CAR_RENTAL_COMPANY_RENTAL_HISTORY 테이블에서 대여 시작일을 기준으로 2022년 8월부터 2022년 10월까지 총 대여 횟수가 5회 이상인 자동차들에 대해서 해당 기간 동안의 월별 자동차 ID 별 총 대여 횟수(컬럼명:RECORDS) 리스트를 출력하는 SQL문을 작성해주세요. 결과는 월을 기준으로 오름차순 정렬하고, 월이 같다면 자동차 ID를 기준으로 내림차순 정렬해주세요. 특정 월의 총 대여 횟수가 0인 경우에는 결과에서 제외해주세요.
먼저 8~10월까지 총 대여 횟수가 5회 이상인 자동차들을 서브쿼리로 묶어주고 결괏값을 월별, 자동차 ID별로 그룹화해 준 뒤 정렬했다.
해당 문제에서는 없어도 통과는 됐지만 특정 월의 총 대여 횟수가 0인 경우를 제외하기 위해 count가 1 이상인 것만 having 조건으로 필터링해 주었다. '특정 월의 총 대여 횟수가 0인 경우 결과에서 제외'가 8, 9, 10월 중 하나라도 대여 횟수가 0인 자동차는 제외하고 출력하는 거라고 생각했는데 정답 처리된 결과를 보니 5월은 10월 대여 횟수가 0이라 출력되지 않았다. 즉, 그냥 총 대여 횟수가 1 이상인 결과만 출력하라는 의미였다. 그리고 month를 서브쿼리 말고 한번 더 필터해 줘야 정답처리가 됐다.
select month(start_date) MONTH, CAR_ID, count(*) RECORDS
from car_rental_company_rental_history
where car_id in (select car_id
from car_rental_company_rental_history
where month(start_date) in (8, 9, 10)
group by 1
having count(*)>=5)
and month(start_date) in (8, 9, 10)
group by 1, 2
having count(*)>=1
order by 1, 2 desc
70. MEMBER_PROFILE과 REST_REVIEW 테이블에서 리뷰를 가장 많이 작성한 회원의 리뷰들을 조회하는 SQL문을 작성해주세요. 회원 이름, 리뷰 텍스트, 리뷰 작성일이 출력되도록 작성해주시고, 결과는 리뷰 작성일을 기준으로 오름차순, 리뷰 작성일이 같다면 리뷰 텍스트를 기준으로 오름차순 정렬해주세요.
리뷰 작성 수대로 RANK를 매겨주고 작성 수 1위인 회원 ID가 작성한 리뷰를 정렬하고 출력했다.
리뷰를 가장 많이 작성한 회원의 수가 한명이 아니라 3명이라서 COUNT와 LIMIT을 사용하는 대신 RANK 함수로 필터링했다.
select m.member_name, r.review_text, date_format(r.review_date, '%Y-%m-%d') REVIEW_DATE
from member_profile m inner join rest_review r on m.member_id=r.member_id
where m.member_id in (select member_id
from (select member_id, rank() over(order by count(*) desc) rnk
from rest_review
group by 1) rk
where rnk=1)
order by 3, 2