12/13 having 및 서브쿼리, datediff, 그룹화

문제 63 자동차 평균 대여기간 구하기

어제 공부한 datediff를 활용하는 문제. 새로 배운 함수를 활용해서 어려울 건 없지만,

어제와 마찬가지로 평균 대여기간(날짜)를 구할 때 +1을 해주는 것이 중요하다 싶어서 기록.

SELECT car_id,
       round(avg(datediff(end_date, start_date)+1), 1) average_duration
from car_rental_company_rental_history
group by 1
having average_duration>=7
order by average_duration desc, car_id desc

 

 

문제  64 : 헤비 유저가 소유한 장소

having 및 서브쿼리 활용

이 서비스에서는 공간을 둘 이상 등록한 사람을 "헤비 유저"라고 부릅니다. 헤비 유저가 등록한 공간의 정보를 아이디 순으로 조회하는 SQL문을 작성해주세요.

 

having과 서브쿼리를 동시에 활용해야하는 문제. 비슷한 유형을 몇 차례 풀어보긴 했으나, 아직 생각회로가 제대로 잡히지 않아서 그런지 문제를 보자마자 'having만 사용하면 되겠지'라고 생각하고 아래와 같이 코드를 작성했다. 

SELECT *
from places
group by host_id      <---- ① : host_id로 그룹화
having count(*)>=2    <---- ② : ①의 그룹화된 것을 기준으로 두 개 이상인 것
order by ID

하지만 문제의 요지는 '헤비 유저'를 물어보는 것이 아니라 '헤비 유저'가 등록한 '공간'의 정보를 물어보는 것이었고, 위의 코드는 틀렸다. 즉, 공간을 2개 이상 소유한 '헤비 유저'가 코딩의 중심의 된 것이라 해당 조건에 속하는 케이스 두 건만 출력되었다.

그렇기에 관건은 해당 조건을 서브쿼리로 만들어 where절로 갖고 가는 것이었다. 즉, 서브쿼리로 테이블에서 2번 이상 등장하는 host_id를 먼저 출력하고, 이렇게 서브쿼리로 출력된(중복된) host_id가 있는 모든 칼럼을 서브가 아닌 쿼리에서 조건으로 걸어주는 것이 문제의 관건이었다.

그렇게 해서 작성한 답변은 아래와 같다.

SELECT *
from places
where host_id in                   <----'서브쿼리로 출력된 host_id가 있는 모든 칼럼'으로 조건화
             (select host_id        
              from places
              group by host_id     <----서브쿼리 : 2번 이상 조회된 host_id만 출력
              having count(*)>=2)
order by ID

 

 

 

문제 66 : 조회수가 가장 많은 중고거래 게시판의 첨부파일 조회하기

그룹화 및 having

USED_GOODS_BOARD와 USED_GOODS_FILE 테이블에서 조회수가 가장 높은 중고거래 게시물에 대한 첨부파일 경로를 조회하는 SQL문을 작성해주세요. 첨부파일 경로는 FILE ID를 기준으로 내림차순 정렬해주세요. 기본적인 파일경로는 /home/grep/src/ 이며, 게시글 ID를 기준으로 디렉토리가 구분되고, 파일이름은 파일 ID, 파일 이름, 파일 확장자로 구성되도록 출력해주세요. 조회수가 가장 높은 게시물은 하나만 존재합니다.

SELECT concat('/home/grep/src/', f.board_id, '/', f.file_id, f.file_name, f.file_ext) file_path
from used_goods_file f left join used_goods_board b on f.board_id=b.board_id
order by b.views desc
limit 1

 

그룹화 및 복수의 답을 늘 생각할것!

 

SELECT concat('/home/grep/src/', f.board_id, '/', f.file_id, f.file_name, f.file_ext) file_path
from used_goods_file f right join used_goods_board b on f.board_id=b.board_id
where b.views in (select max(views) from used_goods_board)
group by f.file_id

 

 

 

 

문제 69 : 대여 횟수가 많은 자동차들의 월별 대여 횟수 구하기 

서브쿼리 및 날짜 조건 두 번 작성하기

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

 

서브쿼리 자체는 크게 어렵지 않았지만, 서브쿼리를 사용할 때 서브쿼리랑 서브가 아닌 쿼리 둘 다에 날짜 조건을 적어줘야한다는 점을 깨닫게 해준 문제.

일단 별 생각 없이(ㅎㅎ) 첫 번째 경우처럼 썼다가 금방 두 번째처럼 고쳐 적었는데 계속해서 답이 아니라길래 한참 생각하고 고민했지만 답이 나오질 않았다.

select month,
       car_id,
       records
from
(
SELECT month(start_date) month,
       car_id,
       count(*) records
from car_rental_company_rental_history
where start_date between '2022-08-01' and'2022-10-31'
group by 1, 2
having count(car_id)>=5
order by month asc, car_id desc
) a
where records !=0

 

SELECT month(start_date) month,
       car_id,
       count(*) records
from car_rental_company_rental_history
where start_date between '2022-08-01' and'2022-10-31'
      and car_id in (select car_id
                     from car_rental_company_rental_history
                     group by car_id having count(car_id)>=5)
group by 1, 2
order by month asc, car_id desc

결국 '질문하기'로 들어가서 보니 서브쿼리에도, 일반 쿼리에도 날짜 조건이 있었는데 한쪽에만 있으면 되지 않나 하는 궁금증을 해소해주는 해설은 없었다. 결국 구글링을 해보니 명쾌한 답이 등장.

이유인 즉슨, 서브쿼리에만 날짜 조건이 있으면 서브쿼리로 조건지어진(8월~10월 동안 5회 이상 대여된) car_id의 전체 기간까지 계산이 되고,

일반쿼리에만 있으면 8~10월뿐만 아니라 전체 기간에 5회 이상 대여된 모든 차량의 8~10월 대여 기록이 조회되는 것이었다.

 

해설을 보고 나니 크게 어려운 이유는 아니었는데 아직 논리구조가 제대로 잡히질 않은 모양...ㅠ

SELECT month(start_date) month,
       car_id,
       count(car_id) records
from car_rental_company_rental_history
where car_id in (select car_id
                 from car_rental_company_rental_history
                 where start_date between '2022-08-01' and'2022-10-31'
                 group by car_id
                 having count(car_id)>=5)
                 and start_date between '2022-08-01' and'2022-10-31'
group by 1, 2
having count(car_id)>0
order by month asc, car_id desc