[MySQL] SQL 문제풀이 (JOIN)

고득점 Kit - JOIN

풀지 않은 JOIN 6문제를 풀고자 합니다

JOIN은 SQL의 꽃 같은 느낌이죠.. 암요

---

1) 주문량이 많은 아이스크림들 조회하기

평범하게 조인하고, 맛을 기준으로 그룹으로 분류해서 SUM 하고 정렬했다.

SELECT H.FLAVOR
FROM FIRST_HALF AS H JOIN JULY AS J
ON H.FLAVOR = J.FLAVOR
GROUP BY H.FLAVOR
ORDER BY SUM(H.TOTAL_ORDER + J.TOTAL_ORDER) DESC
LIMIT 3;

그런데 이해가 안되는 부분이 생겼다.. 뜨아..

 

JOIN을 하면 아래와 같이 되고 SUM을 해버리면..

# JOIN 결과

+------------+---------------+--------------+
|  H.FLAVOR  | H.TOTAL_ORDER | J.TOTAL_ORDER |
+------------+---------------+--------------+
| strawberry | 3100          | 520           |
| strawberry | 3100          | 220           |
+------------+---------------+--------------+

우리가 원하는 값은 3100 + 520 + 220인데,

(3100+ 3100) + (520 + 220) 으로 6940 이라는 값이 나올 것만 같은 것이다..

아니나 다를까 SUM(H.TOTAL_ORDER + J.TOTAL_ORDER) 값을 출력해보니 6940이 나온다..

 

즉, 위의 코드로 제출을 하면 정답입니다! 가 뜨지만...

사실은 틀린 코드였던 것이다..

프로그래머스 이 새럼들아~

왜 자꾸 틀린 코드보고 맞다고 하는 것이냐!! ㅠㅠ

 

결과론적으로 맞는 코드를 다시 구현해보자..

SELECT H.FLAVOR
FROM FIRST_HALF AS H JOIN (
    SELECT FLAVOR, SUM(TOTAL_ORDER) AS TOTAL_ORDER
    FROM JULY
    GROUP BY FLAVOR
) AS J
ON H.FLAVOR = J.FLAVOR
GROUP BY H.FLAVOR
ORDER BY (H.TOTAL_ORDER + J.TOTAL_ORDER) DESC
LIMIT 3;

정말 다양한 풀이법이 있을 것 같은데, 나는 서브쿼리를 사용하였다.

# JOIN 결과

+------------+---------------+--------------+
|  H.FLAVOR  | H.TOTAL_ORDER | J.TOTAL_ORDER |
+------------+---------------+--------------+
| strawberry | 3100          | (520 + 220)   |
+------------+---------------+--------------+

아까 JULY 테이블의 strawberry 값이 520, 220이 있었다면,

서브쿼리에서 그룹바이를 통해 520+220 을 미리 해 준 다음에,

FIRST_HALF 테이블에 있던 3100과 더해주는 것이다.

그러면 우리가 원하는 값인 3100 + (520 + 220)이 계산된다!

---

2) 특정 기간동안 대여 가능한 자동차들의 대여비용 구하기

문제가 정말 길다.. ㅠ

우선 대여기록 테이블이랑 자동차 정보 테이블을 조인시킨다.

그리고, 조인된 테이블이랑 할인 정보 테이블을 조인시킨다.

이때, 우리는 30일 대여하는 경우의 값을 계산하고 싶기 때문에 '30일 이상' 이라는 값을 가진 행만 조인에 참여시킨다.

# 코드 일부
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY AS H LEFT JOIN CAR_RENTAL_COMPANY_CAR AS C
ON H.CAR_ID = C.CAR_ID
LEFT JOIN CAR_RENTAL_COMPANY_DISCOUNT_PLAN AS D
ON C.CAR_TYPE = D.CAR_TYPE AND D.DURATION_TYPE = '30일 이상'

 

그 다음은 WHERE 절을 보자.

세단과 SUV의 데이터만 필요하며,

30일 대여 요금의 합이 50만원 이상, 200만원 미만 이어야한다.

이 요금 조건을 HAVING 절로 쓸 수도 있는데, 여기서는 WHERE절에 썼다.

# 코드 일부
WHERE (C.CAR_TYPE = '세단' OR C.CAR_TYPE = 'SUV') AND
    500000 <= C.DAILY_FEE * (100 - D.DISCOUNT_RATE) / 100 * 30 AND
    C.DAILY_FEE * (100 - D.DISCOUNT_RATE) / 100 * 30 < 2000000 AND

 

여담으로, SELECT절에서 정의한 alias가 WHERE 절에 적용이 안돼서 FEE 라는 별칭을 사용하지 못했다.

HAVING 절에는 alias 사용이 가능하기 때문에 HAVING에 요금 조건을 쓴다면 더 간결한 코드가 될 것이다.

( 테이블 전체가 하나의 그룹이 되는 경우 GROUP BY 없이 HAVING절 사용이 가능하다고 함 )

 

그리고 날짜 조건을 신경써야 한다.

날짜 조건은 은근 까다로운데..

 

1. 아래에서 5번째 코드에 IN을 사용하고, 조건문에 11월달이 아닌 것! 이라고 명시 안된다.

왜냐하면, 1번 자동차의 대여기록 중 어떤 기록은 11월이 아닌 다른 날에 대여한 기록이고, 어떤 것은 11월에 대여한 기록일 것이다.

따라서 IN을 사용하게 되면, 11월에 누군가 대여한 자동차임에도 불구하고, 11월이 아닌 다른 달에 대여된 기록과 같이 있다면, 해당 자동차는 대여할 수 있다고 잘못 판단될 수 있다.

그러므로 NOT IN을 사용하고, 조건문에 11월에 대여된 것! 이라고 명시해야 한다.

 

2. 처음에는 '2022-11-01' <= START_DATE AND '2022-11-30' >= END_DATE) 이라고 했다.

하지만, 11월에 빌리고 12월에 반납할 수도 있는 것이므로

'2022-11-30' >= START_DATE AND '2022-11-01' <= END_DATE 로 표기해야한다.

SELECT DISTINCT(H.CAR_ID), C.CAR_TYPE, CEILING(C.DAILY_FEE * (100 - D.DISCOUNT_RATE) / 100 * 30) AS FEE
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY AS H LEFT JOIN CAR_RENTAL_COMPANY_CAR AS C
ON H.CAR_ID = C.CAR_ID
LEFT JOIN CAR_RENTAL_COMPANY_DISCOUNT_PLAN AS D
ON C.CAR_TYPE = D.CAR_TYPE AND D.DURATION_TYPE = '30일 이상'
WHERE (C.CAR_TYPE = '세단' OR C.CAR_TYPE = 'SUV') AND
    500000 <= C.DAILY_FEE * (100 - D.DISCOUNT_RATE) / 100 * 30 AND
    C.DAILY_FEE * (100 - D.DISCOUNT_RATE) / 100 * 30 < 2000000 AND
    H.CAR_ID NOT IN
        (SELECT CAR_ID
         FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
         WHERE '2022-11-30' >= START_DATE AND '2022-11-01' <= END_DATE)
ORDER BY FEE DESC, C.CAR_TYPE, H.CAR_ID DESC;

---

3) 5월 식품들의 총매출 조회하기

두 테이블을 join한 뒤, id별로 group by를 하고, 총량을 구해서 조회하면 된다.

SELECT P.PRODUCT_ID, P.PRODUCT_NAME, SUM(P.PRICE * O.AMOUNT) AS TOTAL_SALES
FROM FOOD_ORDER AS O LEFT JOIN FOOD_PRODUCT AS P
ON P.PRODUCT_ID = O.PRODUCT_ID
WHERE O.PRODUCE_DATE LIKE '2022-05-%'
GROUP BY P.PRODUCT_ID
ORDER BY TOTAL_SALES DESC, P.PRODUCT_ID;

---

4) 조건에 맞는 도서와 저자 리스트 출력하기

join을 한 뒤, 조건에 맞춰 조회하면 된다.

SELECT B.BOOK_ID, A.AUTHOR_NAME, LEFT(B.PUBLISHED_DATE, 12) AS PUBLISHED_DATE
FROM BOOK AS B LEFT JOIN AUTHOR AS A
ON B.AUTHOR_ID = A.AUTHOR_ID
WHERE B.CATEGORY = '경제'
ORDER BY B.PUBLISHED_DATE;

---

5) 그룹별 조건에 맞는 식당 목록 출력하기

우선 두 테이블을 조인한 뒤, 원하는 데이터 (이름, 리뷰, 리뷰 날짜) 를 조회한다.

그런데 가장 리뷰를 많이 작성한 사람의 리뷰만 조회해야 하므로 서브쿼리를 사용했다.

서브쿼리에서는, 사용자 별로 그룹화를 하고, 사용자 별 리뷰 수를 계산한 다음, 내림차순 하며 가장 상단에 있는 데이터 1개만 가져온다.

그리고 그 데이터와 같은 메일 주소를 가진 사람의 리뷰만 가져오면 된다.

SELECT M.MEMBER_NAME, R.REVIEW_TEXT, LEFT(R.REVIEW_DATE, 12) AS REVIEW_DATE
FROM REST_REVIEW AS R LEFT JOIN MEMBER_PROFILE AS M
ON R.MEMBER_ID = M.MEMBER_ID
WHERE R.MEMBER_ID = (SELECT MEMBER_ID
                     FROM REST_REVIEW
                     GROUP BY MEMBER_ID
                     ORDER BY COUNT(MEMBER_ID) DESC
                     LIMIT 1)
ORDER BY R.REVIEW_DATE, R.REVIEW_TEXT;

---

6) 상품을 구매한 회원 비율 구하기

조건에 맞춰 구현하면 되는데, 같은 유저가 여러번 구매했을 수도 있기 때문에 DISTINCT를 사용했다.

그리고 2021년도에 가입한 유저의 수를 구하기 위해 select 절에서 서브쿼리를 이용했다.

SELECT YEAR(O.SALES_DATE) AS YEAR, MONTH(O.SALES_DATE) AS MONTH,
    COUNT(DISTINCT(O.USER_ID)) AS PUCHASED_USERS,
    ROUND(COUNT(DISTINCT(O.USER_ID)) / (SELECT COUNT(DISTINCT(USER_ID)) FROM USER_INFO WHERE YEAR(JOINED) = '2021'), 1) AS PUCHASED_RATIO
FROM ONLINE_SALE AS O LEFT JOIN USER_INFO AS U
ON O.USER_ID = U.USER_ID
WHERE YEAR(U.JOINED) = '2021'
GROUP BY YEAR(O.SALES_DATE), MONTH(O.SALES_DATE)
ORDER BY YEAR, MONTH;

---

 

JOIN 문제풀이 끝!