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

1. SELECT - [평균 일일 대여 요금 구하기] _ LEVEL 1

CAR_RENTAL_COMPANY_CAR 테이블에서 자동차 종류가 'SUV'인 자동차들의 평균 일일 대여 요금을 출력하는 SQL문을 작성해주세요. 이때 평균 일일 대여 요금은 소수 첫 번째 자리에서 반올림하고, 컬럼명은 AVERAGE_FEE 로 지정해주세요.

 

[ 정답 쿼리 ]

SELECT ROUND(AVG(daily_fee),0) average_fee
FROM car_rental_company_car
WHERE car_type = 'SUV';

 

[ 풀이 ]

• SELECT절에서 GROUP BY 절 없이 쓰는 집계함수는 WHERE절에서 필터링되고 남은 ROW 전체에 대해서 작동
• 반올림 표현 : ROUND 함수
  • 소수 첫 번째자리에서 반올림 : ROUND(~,0)
  • 소수 첫 번째자리까지 반올림 : ROUND(~,1)

---

2. SUM, MAX, MIN - [최댓값 구하기] _ LEVEL 1

ANIMAL_INS 테이블은 동물 보호소에 들어온 동물의 정보를 담은 테이블입니다. 가장 최근에 들어온 동물은 언제 들어왔는지 조회하는 SQL 문을 작성해주세요.

※ 컬럼 이름(위 예제에서는 "시간")은 일치하지 않아도 됩니다.

 

[ 정답 쿼리 ]

SELECT MAX(datetime) as `최근 보호소 입소 날짜`
FROM animal_ins;

 

[ 풀이 ]

• 가장 최근에 들어온 날짜 = MAX(날짜)

---

3. GROUP BY - [자동차 종류 별 특정 옵션이 포함된 자동차 수 구하기] _ LEVEL 2

CAR_RENTAL_COMPANY_CAR 테이블에서 '통풍시트', '열선시트', '가죽시트' 중 하나 이상의 옵션이 포함된 자동차가 자동차 종류 별로 몇 대인지 출력하는 SQL문을 작성해주세요. 이때 자동차 수에 대한 컬럼명은 CARS로 지정하고, 결과는 자동차 종류를 기준으로 오름차순 정렬해주세요.

※ 키워드 종류는 '주차감지센서', '스마트키', '네비게이션', '통풍시트', '열선시트', '후방카메라', '가죽시트' 가 있습니다.

 

[ 정답 쿼리 ]

-- 풀이 1. LIKE
SELECT car_type, COUNT(*) AS cars
FROM car_rental_company_car
WHERE options LIKE '%통풍시트%' OR options LIKE '%열선시트%' OR options LIKE '%가죽시트%'
GROUP BY car_type
ORDER BY 1;

-- 풀이 1-2. 간단한 LIKE 활용
SELECT car_type, COUNT(*) AS cars
FROM car_rental_company_car
WHERE options LIKE '%시트%'
GROUP BY car_type
ORDER BY 1;

-- 풀이 2. 정규식
SELECT car_type, COUNT(*) AS cars
FROM car_rental_company_car
WHERE options REGEXP '(통풍시트|열선시트|가죽시트)'
GROUP BY car_type
ORDER BY 1;

 

[ 풀이 ]

• WHERE절 : 자동차 옵션(options)에서 통풍시트, 열선시트, 가죽시트 중 하나 이상의 키워드를 포함한 row 필터링
  1. LIKE 키워드 이용 : WHERE options LIKE '%통풍시트%' OR options LIKE '%열선시트%' OR options LIKE '%가죽시트%'  LIKE 키워드와 OR로 3가지 시트 조건을 나열하여 조건을 걸면 됨. 다만, 해당 문제에서 존재하는 시트 옵션이 3가지가 전부이기 때문에 WHERE options LIKE '%시트%' 와 같이 간단한 조건으로도 구현 가능!
  2. 정규식(REGEXP) : '(통풍시트|열선시트|가죽시트)' 의 정규식으로 해당 조건 구현 가능. 초반에 () 대신 []를 써서 오답처리 되었는데, []로 쓰게 되면 대괄호 안의 character들을 하나씩 낱개로 찾게 됨
• GROUP BY절 : 자동차 종류를 기준으로 집계를 해야 하므로 GROUP BY car_type
• SELECT절 : COUNT(*)로 조건에 적합한 자동차 종류별 차 대수를 집계

---

4. IS NULL - [이름이 있는 동물의 아이디] _ LEVEL 1

동물 보호소에 들어온 동물 중, 이름이 있는 동물의 ID를 조회하는 SQL 문을 작성해주세요. 단, ID는 오름차순 정렬되어야 합니다.

 

[ 정답 쿼리 ]

SELECT animal_id
FROM animal_ins
WHERE name IS NOT NULL
ORDER BY animal_id;

 

[ 풀이 ]

• WHERE 절에서 이름(NAME)이 있는 동물에 대한 조건을 IS NOT NULL 로 걸어줌

---

🌟🌟🌟5. JOIN - [5월 식품들의 총매출 조회하기] _ LEVEL 4

FOOD_PRODUCT와 FOOD_ORDER 테이블에서 생산일자가 2022년 5월인 식품들의 식품 ID, 식품 이름, 총매출을 조회하는 SQL문을 작성해주세요. 이때 결과는 총매출을 기준으로 내림차순 정렬해주시고 총매출이 같다면 식품 ID를 기준으로 오름차순 정렬해주세요.

 

[ 정답 쿼리 ]

SELECT p.product_id
    , MAX(product_name) AS product_name
    , SUM(amount * price) AS total_sales
FROM food_product p 
JOIN food_order o ON p.product_id = o.product_id
WHERE DATE_FORMAT(produce_date,'%Y-%m') = '2022-05' -- 2022년 5월 생산 식품 필터링
GROUP BY p.product_id
ORDER BY total_sales DESC, p.product_id;

 

[ 풀이 ]

• 이 문제의 핵심은 문제에는 드러나지 않은 GROUP BY절을 캐치하는 것!
• 식품ID(product_id) 별로 총매출을 계산하기 위해 FOOD_PRODUCT와 FOOD_ORDER 테이블을 JOIN해야 하는데, 두 테이블에서 product_id의 특성이 조금 다름

FOOD_PRODUCT 테이블의 구조 /  FOOD_ORDER 테이블의 구조

 

• FOOD_PRODUCT 내의 product_id는 식품을 구분하는 PK로, 데이터 무결성 조건에 따르면 UNIQUE 조건을 만족
• FOOD_ORDER 테이블은 주문 정보를 담고 있는 테이블로, product_id가 아닌 order_id가 해당 테이블의 PK. → product_id는 unique value임을 보장할 수 없고, 실제로 확인했을 때도 중복값이 존재함
• 따라서 두 테이블을 product_id을 기준으로 JOIN하게 되면, FOOD_ORDER 테이블 때문에 중복 product_id를 가지는 row들이 생성됨
• 문제의 목적에 맞게 제품별 총매출을 출력하기 위해서는 product_id로 GROUP BY가 필요함
• SELECT 절에서는 GROUP BY 절에 명시된 product_id 를 제외한 컬럼들에 대해서 집계함수로 표현해야 함

 

• 데이터 무결성 조건(Data Integrity Constraint)

---

🌟🌟🌟6. String, Date - [자동차 대여 기록 별 대여 금액 구하기] _ LEVEL 4

CAR_RENTAL_COMPANY_CAR 테이블과 CAR_RENTAL_COMPANY_RENTAL_HISTORY 테이블과 CAR_RENTAL_COMPANY_DISCOUNT_PLAN 테이블에서 자동차 종류가 '트럭'인 자동차의 대여 기록에 대해서 대여 기록 별로 대여 금액(컬럼명: FEE)을 구하여 대여 기록 ID와 대여 금액 리스트를 출력하는 SQL문을 작성해주세요. 결과는 대여 금액을 기준으로 내림차순 정렬하고, 대여 금액이 같은 경우 대여 기록 ID를 기준으로 내림차순 정렬해주세요.

※ FEE의 경우 예시처럼 정수부분만 출력되어야 합니다.

 

[ 정답 쿼리 ]

WITH rental_history AS (
SELECT H.history_id, H.car_id, C.daily_fee
    , DATEDIFF(end_date, start_date)+1 AS duration -- 대여기록 별 대여기간 계산
    , CASE WHEN DATEDIFF(end_date, start_date)+1 < 7 THEN '7일 미만'
        WHEN DATEDIFF(end_date, start_date)+1 < 30 THEN '7일 이상'
        WHEN DATEDIFF(end_date, start_date)+1 < 90 THEN '30일 이상'
        WHEN DATEDIFF(end_date, start_date)+1 >= 90 THEN '90일 이상'
        END AS duration_type -- 대여기간별 dicount_rate을 적용하기 위한 범주형 변수(CASE WHEN 문 사용)
FROM car_rental_company_rental_history H
JOIN car_rental_company_car C ON H.car_id = C.car_id AND car_type = '트럭' 
    -- 자동차 정보 테이블에서 필요한 daily_fee 정보를 가져오기 위해 JOIN
)-- WITH 구문으로 해당 정보를 하나의 임시 테이블로 저장

SELECT history_id
    , ROUND(duration * daily_fee * (1 - IFNULL(discount_rate,0)/100),0) AS fee 
    -- 대여 기간이 7일 미만이라 LEFT JOIN 시 discount_rate가 NULL로 들어온 경우는 discount_rate = 0으로 처리되어야 하므로 IFNULL 이용
FROM rental_history RH
LEFT JOIN car_rental_company_discount_plan P ON RH.duration_type = P.duration_type AND P.car_type = '트럭' 
-- 할인 정보 테이블에서 대여 기간에 따른 할인율을 가져오기 위해 조인. 단, 대여 기간이 7일 미만이라 할인이 적용되지 않는 대여기록도 누락되면 안되므로, INNER JOIN이 아닌 LEFT JOIN
ORDER BY fee DESC, history_id DESC;

 

[ 풀이 ]

• 대여기록 별 대여금액을 계산하기 위해선, 먼저 대여기록 별 대여기간을 계산하고, 해당 대여기간이 할인 정보 테이블에서 어떤 할인율이 적용되는지를 찾아서 연결하는 작업이 필요!

---

1. WITH 구문으로 구성한 rental_history라는 임시 테이블
   •  

WITH rental_history AS (
SELECT H.history_id, H.car_id, C.daily_fee
    , DATEDIFF(end_date, start_date)+1 AS duration -- 대여기록 별 대여기간 계산
    , CASE WHEN DATEDIFF(end_date, start_date)+1 < 7 THEN '7일 미만'
        WHEN DATEDIFF(end_date, start_date)+1 < 30 THEN '7일 이상'
        WHEN DATEDIFF(end_date, start_date)+1 < 90 THEN '30일 이상'
        WHEN DATEDIFF(end_date, start_date)+1 >= 90 THEN '90일 이상'
        END AS duration_type -- 대여기간별 dicount_rate을 적용하기 위한 범주형 변수(CASE WHEN 문 사용)
FROM car_rental_company_rental_history H
JOIN car_rental_company_car C ON H.car_id = C.car_id AND car_type = '트럭' 
    -- 자동차 정보 테이블에서 필요한 daily_fee 정보를 가져오기 위해 JOIN
)-- WITH 구문으로 해당 정보를 하나의 임시 테이블로 저장

• 먼저 car_rental_company_rental_history 를 중심으로 대여기록 별 대여기간을 계산

DATEDIFF(end_date, start_date)+1 AS duration

• 후에 대여기간별 dicount_rate을 적용하기 위한 범주형 변수 생성(CASE WHEN 문 사용)

CASE WHEN DATEDIFF(end_date, start_date)+1 < 7 THEN '7일 미만'
        WHEN DATEDIFF(end_date, start_date)+1 < 30 THEN '7일 이상'
        WHEN DATEDIFF(end_date, start_date)+1 < 90 THEN '30일 이상'
        WHEN DATEDIFF(end_date, start_date)+1 >= 90 THEN '90일 이상'
        END AS duration_type

• car_rental_company_car에서 필요한 daily_fee 정보를 가져오기 위해 JOIN

FROM car_rental_company_rental_history H
JOIN car_rental_company_car C ON H.car_id = C.car_id AND car_type = '트럭'

---

  2. 임시 테이블 rental_history와 car_rental_company_discount_plan 테이블을 LEFT JOIN

SELECT history_id
    , ROUND(duration * daily_fee * (1 - IFNULL(discount_rate,0)/100),0) AS fee 
    -- 대여 기간이 7일 미만이라 LEFT JOIN 시 discount_rate가 NULL로 들어온 경우는 discount_rate = 0으로 처리되어야 하므로 IFNULL 이용
FROM rental_history RH
LEFT JOIN car_rental_company_discount_plan P ON RH.duration_type = P.duration_type AND P.car_type = '트럭' 
-- 할인 정보 테이블에서 대여 기간에 따른 할인율을 가져오기 위해 조인. 단, 대여 기간이 7일 미만이라 할인이 적용되지 않는 대여기록도 누락되면 안되므로, INNER JOIN이 아닌 LEFT JOIN
ORDER BY fee DESC, history_id DESC;

• 할인 정보 테이블에서 대여 기간에 따른 할인율을 가져오기 위해 조인. 단, 대여 기간이 7일 미만이라 할인이 적용되지 않는 대여기록도 누락되면 안되므로, INNER JOIN이 아닌 LEFT JOIN
• SELECT 절 : 대여 기간이 7일 미만이라 LEFT JOIN 시 discount_rate가 NULL로 들어온 경우는 discount_rate = 0으로 처리되어야 하므로 IFNULL 이용

SELECT history_id
    , ROUND(duration * daily_fee * (1 - IFNULL(discount_rate,0)/100),0) AS fee

---