12/29 SQL 서브쿼리 : 인라인뷰, LEFT OUTER JOIN, 스칼라쿼리, WITH 함수

SQL 코드카타

문제58. 자동차 대여 기록에서 대여중 / 대여 가능 여부 구분하기

서브쿼리 : 인라인뷰, LEFT OUTER JOIN, 스칼라쿼리, WITH 함수

CAR_RENTAL_COMPANY_RENTAL_HISTORY 테이블에서 2022년 10월 16일에 대여 중인 자동차인 경우 '대여중' 이라고 표시하고, 대여 중이지 않은 자동차인 경우 '대여 가능'을 표시하는 컬럼(컬럼명: AVAILABILITY)을 추가하여 자동차 ID와 AVAILABILITY 리스트를 출력하는 SQL문을 작성해주세요. 이때 반납 날짜가 2022년 10월 16일인 경우에도 '대여중'으로 표시해주시고 결과는 자동차 ID를 기준으로 내림차순 정렬해주세요.

 

2주 전쯤 시도했다가 아예 처음부터 막혀서 패스했다가, 다시 보니 드디어 풀려서 기록하는 문제.

날짜를 비교할 때 애초에 SQL이 'CHAR'이 아닌 'DATE'로 따로 인식하기에 2022-10-16와 같은 식으로 날짜를 그대로 입력만 해도 되는 줄 알고 전부터 몇 차례를 시도하다가 도통 되질 않아서 이미 수 차례를 포기했었다.

그러다가 '질문하기'의 답을 보니 공통적으로 따옴표가 필요하다는 것을 알게되고서 2주만에야 제대로된 문제풀이를 시도함....ㅠ

그래서 일단 직감대로 틀리든 말든 문제를 시도해보기로 했다.

 

 

오답

select distinct(car_id),
    case when start_date <= '2022-10-16' and end_date>='2022-10-16' then '대여중'
            else '대여가능' end availability
from car_rental_company_rental_history
order by car_id desc

우선 보자마자 마음 가는 대로 쓴 답변과 그에 따른 결과. 이미지에서 알 수 있듯, CAR_ID가 중복되어 나오고, 각 중복값마다 '대여 가능'과 '대여중'이 다르게 출력되었다는 것을 알 수 있다.

즉, 대여 기간 칼럼도 따로 SELECT 했다면, 각 CAR_ID의 모든 대여기록이 나왔을 것이다.

 

하지만 문제에서 원하는 것은 2022-10-16일을 기준으로 대여 가능 여부를 체크하는 것이니, 각 CAR_ID의 여러 대여 기록 중 해당 날짜에 겹치는 일정이 하나라도 있으면 '대여중', 그렇지 않으면  '대여 가능'을 출력하게 하는 것이 문제의 핵심이다.

 

'대여중'이 하나라도 있으면 그냥 '대여중'인 걸로 체크하는게 관건! 이를 위해 서브쿼리를 사용해야 한다.

 

정답

1)인라인뷰, LEFT OUTER JOIN 사용

select distinct(a.car_id),
       if(b.car_id is not null, '대여중', '대여 가능') as availability
from car_rental_company_rental_history a
left outer Join
(select car_id
from car_rental_company_rental_history
where start_date <= '2022-10-16'
 and end_date>='2022-10-16') b
on a.car_id = b.car_id
order by 1 desc

 

 

2)스칼라쿼리 사용

select distinct(car_id),
       if(car_id in(select car_id
from car_rental_company_rental_history
where start_date <= '2022-10-16'
 and end_date>='2022-10-16'), '대여중', '대여 가능') as availability
from car_rental_company_rental_history 
order by 1 desc


--에러표시 나는 경우
select distinct(car_id),
       if((select car_id
from car_rental_company_rental_history
where start_date <= '2022-10-16'
 and end_date>='2022-10-16') is not null, '대여중', '대여 가능') as availability
from car_rental_company_rental_history 
order by 1 desc

-->'Subquery returns more than 1 row'라는 에러 메시지 뜸

 

3)WITH 함수 사용

with temp as (
    select car_id
    from car_rental_company_rental_history
    where start_date <= '2022-10-16'
    and end_date>='2022-10-16'
    )


select distinct(c.car_id),
       if(t.car_id is null, '대여 가능', '대여중') as availability
from car_rental_company_rental_history c left outer join temp t on c.car_id=t.car_id
order by 1 desc

 

 

---

 

SQLD 강의

 

14주

옵티마이저

-DBMS의 두뇌

-어떤 방식으로 SQL 쿼리 처리하고 데이터 검색할 지 결정에 도움을 주는 도구 : 최적화 및 실행속도에 도움

 

실행계획

1)SQL에서 요구한 사항 처리 위한 절차오 방법. 옵티마이저가 수립.

2)실행계획 읽기 : 

  (1)위아래 스캔하며 젤 먼저 읽을 곳 찾기

  (2)같은 들여쓰기일 경우 '위->아래' 순서

  (3)가장 들여쓰기 많이 된 스텝부터 상위로 한 단계씩

3)구성 : 조인순서, 조인기법, 액세스 기법, 최적화정보, 연산 등

4)SQL처리 흐름도 : 조인순서, 조인방법, 액세스 기법, 액세스 건수, 성공건수 등 기입

 

인덱스

-검색 성능 최적화 목적

-데이터 수정시 인덱스 같이 수정해야 함 =>시스템 부하 및 속도 저하

 *SQL서버 데이터 레코드 아무런 순서 없이 HEAP에 저장 => 인덱스 없으면 풀스캔(전체테이블스캔)수행 =>처리성능저하

1)종류 : 트리기반 인덱스, 클러스터형(SQL 서버)

2)전체 테이블 스캔과 인덱스 스캔

 

조인 수행 원리

1)NL JOIN(Nested Loops Join)

  -랜덤 엑세스

2)SORT MERGE JOIN :

  -스캔 방식, 정렬 뒤 수행

 -비동등 조인도 가능

3)해시조인(Hash Join) :

 -순서 : 조인 수행할 테이블의 조인 칼럼 기준 해시 함수 수행 -> 동일 해시값 가지면 실제 값 같은지 비교하며 조인.

 -동등조인만 가능.

 -두 개의 테이블 빠르게 조인함. 대량데이터 처리에 우수(넷플릭스 등 사용)

 -키 중복, 데이터 분포 따라 성능 달라질 수 있음

 -해시 테이블 크기 및 메모리 관리에 주의