MySQL 실습 | 자동차 대여 기록에서 장기/단기 대여 구분, 자동차 평균 대여 기간, 헤비 유저가 소유한 장소, 우유와 요거트가 담긴 장바구니

문제 1.  자동차 대여 기록에서 장기/단기 대여 구분하기

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

 

프로그래머스

---

💡 문제 풀이 

SELECT HISTORY_ID, 
    CAR_ID, 
    DATE_FORMAT(START_DATE,'%Y-%m-%d') AS START_DATE, 
    DATE_FORMAT(END_DATE,'%Y-%m-%d') AS END_DATE,
    CASE WHEN DATEDIFF(END_DATE,START_DATE)+1 >= 30 THEN '장기 대여'
        ELSE '단기 대여' 
    END AS RENT_TYPE
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
WHERE SUBSTR(START_DATE,1,7) = '2022-09'
ORDER BY HISTORY_ID DESC;

 

`오답노트`

• '장기 대여'를 '장기대여'라고 띄어쓰기 안 써서 1시간동안 헤맸다.. 프로그래머스 문제 풀 때는 alias를 문제에서 최대한 복붙해와야겠음. 아까운 내 시간 🤦‍♀️
• 처음에 END_DATE - START_DATE 했는데 날짜 차이 구할 땐 `DATEDIFF`함수 사용해야 함 (DATE_DIFF아님)
• 빌린 날 바로 반납했어도 대여 기간은 1일이기 때문에 DATE_DIFF에서 1 더한 숫자가 실제 대여기간 

 

---

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

CAR_RENTAL_COMPANY_RENTAL_HISTORY 테이블에서 평균 대여 기간이 7일 이상인 자동차들의 자동차 ID와 평균 대여 기간(컬럼명: AVERAGE_DURATION) 리스트를 출력하는 SQL문을 작성해주세요. 평균 대여 기간은 소수점 두번째 자리에서 반올림하고, 결과는 평균 대여 기간을 기준으로 내림차순 정렬해주시고, 평균 대여 기간이 같으면 자동차 ID를 기준으로 내림차순 정렬해주세요.

 

프로그래머스

---

💡 문제 풀이 

SELECT CAR_ID, 
    ROUND(AVG(DATEDIFF(END_DATE,START_DATE)+1),1) AS AVERAGE_DURATION
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
GROUP BY CAR_ID
HAVING AVG(DATEDIFF(END_DATE,START_DATE)+1) >=7
ORDER BY 2 DESC, 1 DESC;

 

`오답노트`

• ROUND(AVG(DATEDIFF))) 중첩 가능

 

---

문제 3.  헤비 유저가 소유한 장소

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

프로그래머스

---

💡 문제 풀이 

 

SELECT ID, NAME, HOST_ID
FROM (SELECT ID, NAME, HOST_ID, COUNT(*) OVER(PARTITION BY HOST_ID) AS CNT
      FROM PLACES) t
WHERE CNT >=2
ORDER BY ID ;

 

`오답노트`

• 그냥 group by host_id를 하면 host_id별로 정보가 하나로 묶여서 나와서 개별 정보가 안 나오기 때문에 원하는 결과가 아님. 그래서 윈도우 함수로 count 개수와 내용을 같이 구하고, 집계함수 count에 대한 조건을 걸어야 하니까 서브쿼리를 이용. 

 

---

문제 4.  우유와 요거트가 담긴 장바구니

데이터 분석 팀에서는 우유(Milk)와 요거트(Yogurt)를 동시에 구입한 장바구니가 있는지 알아보려 합니다. 우유와 요거트를 동시에 구입한 장바구니의 아이디를 조회하는 SQL 문을 작성해주세요. 이때 결과는 장바구니의 아이디 순으로 나와야 합니다.

 

프로그래머스

---

💡 문제 풀이 

-- 각 CART_ID 별로 2행 이상인 경우(=milk, yogurt 다 있는 경우)만 CART_ID 출력
SELECT CART_ID
FROM 
    -- Yogurt나 Milk가 하나 이상 들어있는 모든 CART_ID 정보 테이블
    (SELECT CART_ID, NAME, COUNT(NAME) AS CNT_NAME  
    FROM CART_PRODUCTS
    GROUP BY CART_ID, NAME  -- cart_id별, name별로 그룹
    HAVING NAME IN ('Milk','Yogurt') AND CNT_NAME >= 1) T1
GROUP BY CART_ID
HAVING COUNT(*) >= 2
ORDER BY CART_ID;

 

`더 쉬운 풀이`

# Yogurt가 있는 CART_ID를 구하고, 거기서 Milk도 가지고 있는 CART_ID 구하면 됨  
SELECT
    CART_ID
FROM
    CART_PRODUCTS
WHERE
    NAME = 'Milk'
    AND CART_ID IN (
    SELECT
        CART_ID
    FROM
        CART_PRODUCTS
    WHERE NAME = 'Yogurt'
    )
ORDER BY
    CART_ID;

 

`또 다른 풀이`

# GROUP_CONCAT을 사용하는 방법
SELECT CART_ID
FROM (
    SELECT CART_ID, GROUP_CONCAT(NAME) AS CART
    FROM CART_PRODUCTS 
    GROUP BY CART_ID
    ) T1 
WHERE CART LIKE '%Milk%' AND CART LIKE '%Yogurt%'

 

`오답노트`

• 복잡하게 생각해서 엄청 오래 걸렸다. 쉽게 쉽게 생각하자..
• GROUP_CONCAT 함수도 알아두면 유용할 듯

코드는 엉망진창이지만 작동하는 뭐 그런 상태~

 

참고) 문제와는 관련 없지만 헷갈렸던 윈도우함수 범위 지정 관련 내용 ex. 부서별 누적 매출합계 구하기

• RANGE UNBOUNDED PRECEDING : 현재 행을 기준으로 해서 파티션 내의 첫번째 행까지 범위를 지정
• ROWS BETWEEN 1 PRECEDING AND 1  FOLLOWING : 현재 행을 기준으로 파티션내 앞뒤 한건씩을 범위로 지정
• RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING : 같은 파티션내 현재행부터 마지막행까지

그룹 내 집계함수 : 집계함수(대상 컬럼) OVER (PARTITION BY ~ ) 원하는 조건별로,자신 행의 바로 위의