251029_BootCamp Day 8_SQL 코딩테스트 문제

F1 드라이버 막스 베르스타펜의 시그니처 프레이즈. SIMPLY == LOVELY라면, 코딩에 필요한 자세라고 생각해 가져왔다.

1. 오늘 한 일

오늘은 강의가 없어서 자율학습을 진행하면서 프로그래머스의 MySQL 코딩테스트 문제를 풀어보았다.

오늘 TIL에서는 코딩테스트를 해결한 쿼리와 함께 해당 문제를 푼 논리구조와 사용한 함수에 대해 정리하려고 한다.

 

2. 코딩테스트 문제 1

SELECT CONCAT('/home/grep/src/', b.board_id, '/', f.file_id, f.file_name, f.file_ext) AS file_path
FROM used_goods_board b JOIN used_goods_file f
     ON b.board_id = f.board_id
WHERE b.views = (
                SELECT MAX(views)
                FROM used_goods_board b2 JOIN used_goods_file f2
                     ON b2.board_id = f2.board_id
               )
ORDER BY f.file_id DESC

쿼리 구조

서브쿼리 : used_goods_board 테이블과 used_goods_file 테이블을 JOIN해 최대 조회수 값을 추출한다.

FROM : used_goods_board와 used_goods_file의 공통 컬럼인 board_id를 통해 JOIN한다.

WHERE : b.views와 서브쿼리에서 구한 MAX(views)가 일치하는 경우만 필터링한다.

SELECT : CONCAT 함수를 이용해 원하는 문자열 포맷으로 변환한다.

ORDER BY : file_id를 기준으로 내림차순 정렬한다.

 

주의점

WHERE절에 사용되는 중첩 서브쿼리에서, 서브쿼리와 비교하는 조건이 =(일치)인 경우 서브쿼리는 무조건 하나의 행만을 반환해야 한다. 중첩 서브쿼리가 여러 행을 반환하는 경우, IN 조건을 이용해 비교할 수 있다.

 

나의 코딩테스트 조건 해결 생각 구조

1. 필터링해야 하는 조건을 파악한다. (ex. 조회수가 가장 높은)

2. 필터링해야 하는 조건이 서브쿼리가 필요한지 파악한다. (메인쿼리의 SELECT에 있는 컬럼들과 함께 있을 수 있는지?)

3. 조건에 따라 서브쿼리의 위치(WHERE, SELECT, FROM)를 결정한다.

4. 필요한 쿼리를 작성한다.

 

3. 코딩테스트 문제 2

SELECT COUNT(*) AS fish_count,
       MAX(length) AS max_length,
       fish_type
FROM fish_info
GROUP BY fish_type
HAVING AVG(COALESCE(length, 10)) >= 33
ORDER BY fish_type

쿼리 구조

FROM : fish_info 테이블을 불러온다.

GROUP BY : fish_type 컬럼 내용을 이용해 그룹화한다.

HAVING : COALESCE를 이용해 length가 NULL인 경우 10으로 대체한다.

SELECT : 수를 계산하는 COUNT(*), fish_type별 최대 길이를 계산하는 MAX(length), 그룹 컬럼인 fish_type을 불러온다.

ORDER BY : fish_type을 이용해 정렬한다.

 

주의점

HAVING에 있는 연산은 SELECT에 없어도 상관없다. GROUP BY를 이용해 계산 가능한 연산이어야 하고, 그 값이 문제풀이의 조건과 일치해야 한다.

 

4. 코딩테스트 문제 3

SELECT id,
       name,
       host_id
FROM places
WHERE host_id IN 
(
    SELECT p2.host_id
    FROM places p2
    GROUP BY p2.host_id
    HAVING count(*) >= 2
)

쿼리 구조

서브쿼리 : host_id의 수가 2인 host_id만 불러온다.

FROM : places 테이블을 불러온다.

WHERE : places 테이블의 host_id가 서브쿼리의 host_id 컬럼에 있는 경우만 필터링한다.

SELECT : places 테이블의 id, name, host_id 컬럼을 불러온다.

 

생각해볼 점

튜터님께서 중첩 서브쿼리를 쓰는 경우는 별로 없다고 했는데 이 코딩테스트가 이상한 건지, 아니면 내가 다른 쉬운 방법을 몰라서인지 중첩 서브쿼리를 자주 사용하고 있다. (내가 몰라서겠지요)

 

5. 코딩테스트 문제 4 

SELECT MONTH(start_date) AS month,
       car_id,
       COUNT(*) AS records
FROM car_rental_company_rental_history
WHERE car_id IN
      (
      SELECT c2.car_id
      FROM car_rental_company_rental_history c2
      WHERE (c2.start_date >= '2022-08-01') AND (c2.start_date <= '2022-10-31')
      GROUP BY c2.car_id
      HAVING count(*) >= 5
      )
      AND
      ((start_date >= '2022-08-01') AND (start_date <= '2022-10-31'))
GROUP BY MONTH(start_date),
         car_id
HAVING COUNT(*) > 0
ORDER BY MONTH(start_date),
         car_id DESC

쿼리 구조

서브쿼리 : start_date가 2022년 8월부터 10월 사이인 car_id 중 그 수가 5 이상인 car_id만 SELECT한다.

FROM : car_rental_company_rental_history 테이블을 불러온다.

WHERE : car_id가 서브쿼리의 car_id에 있는 경우 AND start_date가 2022년 8월부터 10월 사이인 값만 필터링한다.

GROUP BY : MONTH(start_date) (start_date의 월)와 car_id로 그룹화한다.

HAVING : 그룹화한 결과가 0인 경우는 제외한다. (개수가 0보다 작을 수는 없으니 0 초과로 조건 설정)

SELECT : MONTH(start_date), car_id, COUNT(*)를 SELECT

ORDER BY : MONTH(start_date)로 먼저 정렬 후, 동일한 경우 car_id로 내림차순 정렬

 

주의점

쿼리가 길어질수록 어디서 틀렸는지 알기가 어렵고, 집중력이 떨어져 실수 (특히 마지막 ORDER BY를 빼먹는다던가)를 하지 않도록 주의해야 한다.

WHERE 절의 마지막 AND 뒤의 조건을 생각하지 못했는데, '이미 서브쿼리에서 한 번 걸렀는데 안 해도 되지 않나?'라고 생각했다가 생각해보니 이 서브쿼리는 인라인 뷰가 아니라서, 한 번 더 설정해줘야 되는 거였다.

조금 난이도가 있었지만 지금까지 배운 모든 SQL 문법을 사용할 수 있어서 재밌는 문제였다.

 

6. 코딩테스트 문제 5

SELECT i.item_id,
       i.item_name,
       i.rarity
FROM item_info i LEFT JOIN item_tree t
     ON i.item_id = t.parent_item_id
WHERE t.parent_item_id IS NULL
ORDER BY i.item_id DESC

쿼리 구조

FROM : item_info와 item_tree 테이블을 LEFT JOIN한다. JOIN(INNER JOIN)이 아닌 LEFT JOIN을 사용한 이유는, t.parent_item_id가 NULL인 경우가 문제의 조건에 부합하기 때문에, NULL을 제외시키는 JOIN을 사용할 수 없었다.

WHERE : item_tree 테이블의 parent_item_id가 NULL 인 경우만 필터링한다.

SELECT : item_id, item_name, rarity를 SELECT한다.

ORDER BY : item_id를 기준으로 내림차순 정렬한다.

 

주의점

JOIN(INNER JOIN)은 ON 조건이 일치하지 않고 NULL이 존재하는 경우는 모두 제거하기 때문에, JOIN 연산 이후 NULL을 이용해야 한다면 LEFT JOIN 등 OUTER JOIN을 사용해 NULL을 살려야 한다.

 

7. 오늘 느낀 점

• 문제를 보고 메모지에 논리 구조를 적으면서 풀면 쿼리 작성이 쉽다.
• 제발 ORDER BY를 적어라. (뭐 틀렸는지 한참 찾다가 ORDER BY 안 썼네를 찾은 기분이란..)
• 중첩 서브쿼리의 결과가 여러 개인 경우 IN을 이용해 비교할 수 있다. (문제의 논리구조에 따라 안 될 수도 있음)
• 중첩 서브쿼리의 결과는 메인쿼리에 반영되지 않기 때문에, 중첩 서브쿼리에서 사용한 필터링은 메인쿼리에서 또 사용해야 할 수도 있다.
• JOIN이 이루어지는 ON 구문이 어떻게 동작하는 것인지 보다 명확히 이해할 필요가 있다.