[프로그래머스] 5주차 MySQL 스터디

1. SELECT - [인기있는 아이스크림] _ LEVEL 1

상반기에 판매된 아이스크림의 맛을 총주문량을 기준으로 내림차순 정렬하고 총주문량이 같다면 출하 번호를 기준으로 오름차순 정렬하여 조회하는 SQL 문을 작성해주세요.

 

[ 정답 쿼리 ]

SELECT flavor
FROM first_half
ORDER BY total_order DESC, shipment_id;

---

2. SUM, MAX, MIN - [물고기 종류 별 대어 찾기] _ LEVEL 2

낚시앱에서 사용하는 FISH_INFO 테이블은 잡은 물고기들의 정보를 담고 있습니다. 단, 잡은 물고기의 길이가 10cm 이하일 경우에는 LENGTH 가 NULL 이며, LENGTH 에 NULL 만 있는 경우는 없습니다.

FISH_NAME_INFO 테이블은 물고기의 이름에 대한 정보를 담고 있습니다.

물고기 종류 별로 가장 큰 물고기의 ID, 물고기 이름, 길이를 출력하는 SQL 문을 작성해주세요. 물고기의 ID 컬럼명은 ID, 이름 컬럼명은 FISH_NAME, 길이 컬럼명은 LENGTH로 해주세요. 결과는 물고기의 ID에 대해 오름차순 정렬해주세요.

단, 물고기 종류별 가장 큰 물고기는 1마리만 있으며 10cm 이하의 물고기가 가장 큰 경우는 없습니다.

 

[ 정답 쿼리 ]

SELECT id, fish_name, length
FROM
(
    SELECT 
        id
        , fish_name
        , length
        , RANK() OVER (PARTITION BY F.fish_type ORDER BY length DESC) AS ranking
    FROM fish_info F
    JOIN fish_name_info N
    ON F.fish_type = N.fish_type
) SUB
WHERE ranking = 1
ORDER BY id;

 

[ 풀이 ]

• 이전에 풀었던 ‘즐겨찾기가 가장 많은 식당 정보 출력하기’ 문제와 유사!
• 여러가지 방법으로 풀 수 있지만, 개인적으로 가장 직관적이라고 느껴졌던 WINDOW 함수를 이용해서 해결
• 서브쿼리 내에서 PARTITION BY 키워드를 이용해, 물고기 종류 별 길이 순위를 매기고, 외부쿼리에서 순위로 필터링을 걸어서 물고기 종류 별 길이가 가장 긴 물고기의 정보만 출력할 수 있음

---

🌟🌟🌟 3. GROUP BY - [자동차 대여 기록에서 대여중 / 대여 가능 여부 구분하기] _ LEVEL 3

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

 

[ 정답 쿼리 ]

-- 풀이 1. DISTINCT, CASE WHEN문 사용
SELECT 
    DISTINCT car_id
    , CASE WHEN car_id IN 
    (
        SELECT DISTINCT car_id 
        FROM car_rental_company_rental_history 
        WHERE '2022-10-16' BETWEEN start_date AND end_date
    ) THEN '대여중'
    ELSE '대여 가능' END AS availablity
FROM car_rental_company_rental_history
ORDER BY car_id DESC;

-- 풀이 2. GROUP BY, IF문 사용
SELECT 
    car_id
    , IF(SUM(IF (start_date <= "2022-10-16" AND end_date >= "2022-10-16", 1, 0)), "대여중", "대여 가능") 
        AS availablity
FROM car_rental_company_rental_history
GROUP BY car_id
ORDER BY car_id DESC;

-- 풀이 3. GROUP BY, MAX(문자열), ASCII code 사용
SELECT car_id
    , MAX(CASE
        WHEN '2022-10-16' BETWEEN start_date AND end_date THEN '대여중'
        ELSE '대여 가능'
        END) AS availablity
FROM car_rental_company_rental_history
GROUP BY car_id
ORDER BY car_id DESC;

 

 

---

[ 풀이 ]

풀이 1) DISTINCT, CASE WHEN문 사용

• 대여중인 car_id : '2022-10-16’이라는 날짜가 대여 시작일(start_date), 대여 종료일(end_date) 사이에 존재하는 car_id 리스트
• CAR_RENTAL_COMPANY_RENTAL_HISTORY 테이블의 PK는 car_id가 아니라 history_id이므로 원하는 결과 출력을 위해서는 DISTINCT 키워드 필요

---

풀이 2) GROUP BY, IF문 사용

• 풀이 1의 DISTINCT 사용 대신, GROUP BY car_id 와 집계함수를 이용

SELECT 
	car_id
	, IF(
		SUM(
			IF (start_date <= "2022-10-16" AND end_date >= "2022-10-16", 1, 0)
		)
	, "대여중", "대여 가능") AS availablity
FROM ~
GROUP BY car_id

1. '2022-10-16’이라는 날짜가 대여 시작일(start_date), 대여 종료일(end_date) 사이에 존재하는 record에 대해서 1이라는 값을 부여(IF()함수 사용)
2. car_id 별로 SUM()
3. True(0이 아닌 정수)값을 띄는 car_id에 대해서 ‘대여중’ 부여, 그 외에는 ‘대여 가능’ 부여

---

 

풀이 3) GROUP BY, MAX(문자열), ASCII code 사용

• 풀이 2와 마찬가지로 GROUP BY car_id 사용
• 집계함수로 MAX()함수와 ASCII code 성질 활용

MAX(CASE
        WHEN '2022-10-16' BETWEEN start_date AND end_date THEN '대여중'
        ELSE '대여 가능'
        END) AS availablity

 

먼저 말로 풀어서 생각해보자. car_id에 대해서 하나의 record라도 '2022-10-16’일에 대여중이라고 판단된다면 그 car_id는 ‘대여중’으로 처리. 모든 record에 대해서 대여 가능이라고 판단되어야 그 car_id는 ‘대여 가능’

1. 일단 CASE WHEN 문을 사용해 모든 record 각각에 대해서 대여가능여부를 판단
2. 1번의 CASE WHEN 구문 자체에 MAX() 함수를 씌우면 car_id 별로(GROUP BY car_id) ‘대여 가능’, ‘대여중’ 이라는 문자열에 대해서 MAX()함수가 작동
3. 이 때 MAX(문자열)은 문자열의 ASCII code에 따라 작동하는데, ‘ ‘(SP)과 ‘중’의 아스키 코드는 각각 32, 236으로 MAX() 함수를 적용했을 시 ‘대여 가능’과 ‘대여중’이 혼재해 있다면 ‘대여중’이 출력될 것
   

   

4. car_id별로 MAX(‘대여중’만 존재) → ’대여중’
   car_id별로 MAX(‘대여 가능’과 ‘대여중’이 혼재) → ‘대여중’
   car_id별로 MAX(‘대여 가능’만 존재) → ’대여 가능’
   

   

   아스키코드 테이블.  출처 : https://blog.naver.com/mankeys/221086930851

---

---

4. IS NULL - [잡은 물고기의 평균 길이 구하기] _ LEVEL 1

잡은 물고기의 평균 길이를 출력하는 SQL문을 작성해주세요.

평균 길이를 나타내는 컬럼 명은 AVERAGE_LENGTH로 해주세요. 평균 길이는 소수점 3째자리에서 반올림하며, 10cm 이하의 물고기들은 10cm 로 취급하여 평균 길이를 구해주세요.

 

[ 정답 쿼리 ]

SELECT ROUND(AVG(length),2) AS average_length
FROM 
(
    SELECT COALESCE(length,10) AS length
    FROM fish_info
) SUB;

 

[ 풀이 ]

• 10cm 이하의 물고기들을 10cm로 취급 → 해당 테이블에서 10cm 이하 물고기들의 length가 NULL로 처리되어 있으므로 먼저 서브쿼리에서 COALESCE 함수를 활용하여 10으로 값 대체

---

🌟🌟🌟(쿼리의 작동 순서 주의) 5. JOIN - [오랜 기간 보호한 동물(1)] _ LEVEL 3

ANIMAL_INS 테이블은 동물 보호소에 들어온 동물의 정보를 담은 테이블입니다. ANIMAL_OUTS 테이블은 동물 보호소에서 입양 보낸 동물의 정보를 담은 테이블입니다.

아직 입양을 못 간 동물 중, 가장 오래 보호소에 있었던 동물 3마리의 이름과 보호 시작일을 조회하는 SQL문을 작성해주세요. 이때 결과는 보호 시작일 순으로 조회해야 합니다.

 

[ 정답 쿼리 ]

-- 내가 틀린 쿼리문
SELECT INS.name, INS.datetime
FROM animal_ins INS
LEFT JOIN animal_outs OUTS
ON INS.animal_id = OUTS.animal_id
    AND OUTS.animal_id IS NULL -- 차집합 조건
ORDER BY INS.datetime
LIMIT 3;

-- 올바른 쿼리문
SELECT INS.name, INS.datetime
FROM animal_ins INS
LEFT JOIN animal_outs OUTS
ON INS.animal_id = OUTS.animal_id
WHERE OUTS.animal_id IS NULL -- 차집합 조건
ORDER BY INS.datetime
LIMIT 3;

 

[ 풀이 ]

틀린 쿼리문의 조인 결과, INS 테이블 전체 rows에 OUT 테이블의 필드값이 전부 NULL로 처리되어 붙었음

• LEFT JOIN : ON vs WHERE
  • 두 쿼리의 결과가 차이나는 이유는 이는 SQL의 LEFT JOIN 처리 방식 때문( 이래서 쿼리의 작동 순서를 알아야 함 )
  • LEFT JOIN은 왼쪽 테이블의 모든 행을 결과에 포함시키고, ON 절의 조건에 따라 오른쪽 테이블에서 매칭되는 행을 결합. 만약 매칭되는 행이 없다면, 오른쪽 테이블의 컬럼은 모두 NULL로 채워짐. 여기서 중요한 것은 OUTS.animal_id IS NULL 조건은 매칭 후에 확인되는 것이 아니라, 매칭을 시도할 때 이미 NULL로 된 OUTS의 컬럼들과의 비교가 이루어지므로, 이 조건이 특별히 작동하지 않는 것처럼 보이는 것.
  • 따라서 올바른 방식은 WHERE 절에서 OUTS.animal_id IS NULL 조건을 확인하는 것. 이렇게 하면 LEFT JOIN으로 이미 결합된 결과 중에서 ANIMAL_OUTS 테이블의 animal_id가 NULL인 행, 즉 입양되지 않은 동물들만 필터링하여 결과로 반환. 이 방식이 실제로 원하는 데이터를 정확히 필터링하는 방법.

---

6. String, Date - [자동차 대여 기록에서 장기/단기 대여 구분하기] _ LEVEL 1

다음은 어느 자동차 대여 회사의 자동차 대여 기록 정보를 담은 CAR_RENTAL_COMPANY_RENTAL_HISTORY 테이블입니다. CAR_RENTAL_COMPANY_RENTAL_HISTORY 테이블에서 대여 시작일이 2022년 9월에 속하는 대여 기록에 대해서 대여 기간이 30일 이상이면 '장기 대여' 그렇지 않으면 '단기 대여' 로 표시하는 컬럼(컬럼명: RENT_TYPE)을 추가하여 대여기록을 출력하는 SQL문을 작성해주세요. 결과는 대여 기록 ID를 기준으로 내림차순 정렬해주세요.

※ START_DATE와 END_DATE의 경우 예시의 데이트 포맷과 동일해야 정답처리 됩니다.

 

[ 정답 쿼리 ]

SELECT 
    history_id
    , car_id
    , DATE_FORMAT(start_date,'%Y-%m-%d') start_date
    , DATE_FORMAT(end_date,'%Y-%m-%d') end_date
    , IF(DATEDIFF(end_date, start_date) + 1 >=30, '장기 대여', '단기 대여') AS rent_type
FROM car_rental_company_rental_history
WHERE start_date LIKE '2022-09%'
ORDER BY history_id DESC;

 

[ 풀이 ]

• 대여 시작일과 대여 종료일의 차이로 대여 기간을 계산할 때, +1 놓치지 말기
  • 예를 들어 2022-09-12 대여 시작, 2022-09-12 대여 종료라면, DATEDIFF 함수로 계산 시 0이 나오지만 의미상으로는 1일 대여이기 때문