[MySQL] Programmers SQL 고득점 Kit 풀기

database

[MySQL] Programmers SQL 고득점 Kit 풀기 - JOIN

fladi 2025. 2. 14. 16:12

728x90

https://fladi.tistory.com/449

서론

드디어 조인이다! 어려운 문제들이 많을 것 같아서 기대가 된다.

문제풀이 사이트: https://school.programmers.co.kr/learn/courses/30/parts/17046

1. 특정 기간동안 대여 가능한 자동차들의 대여비용 구하기 - 레벨4

SELECT 
    C.CAR_ID,
    C.CAR_TYPE,
    ROUND(C.RAW_FEE * (100 - IFNULL(P.DISCOUNT_RATE, 0)) / 100) AS FEE
FROM (
    SELECT CAR_ID, CAR_TYPE
        DAILY_FEE * 30 AS RAW_FEE,
        '30일 이상' AS DURATION_TYPE 
    FROM CAR_RENTAL_COMPANY_CAR 
    WHERE 1=1
        AND CAR_TYPE IN ('세단', 'SUV')
        AND CAR_ID NOT IN (
            SELECT CAR_ID
            FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY 
            WHERE START_DATE <= '2022-11-30' AND END_DATE >= '2022-11-01'
        )
) C
LEFT JOIN CAR_RENTAL_COMPANY_DISCOUNT_PLAN P
ON C.CAR_TYPE = P.CAR_TYPE AND C.DURATION_TYPE = P.DURATION_TYPE
WHERE 1=1
    AND (C.DAILY_FEE * 30 * (100 - IFNULL(P.DISCOUNT_RATE, 0)) / 100) >= 500000 
    AND (C.DAILY_FEE * 30 * (100 - IFNULL(P.DISCOUNT_RATE, 0)) / 100) < 2000000
ORDER BY FEE DESC, CAR_TYPE ASC, CAR_ID DESC

굉장히 더럽게 짰지만 맞긴했다.

HISTORY에서 2022-11-01 ~ 2022-11-30 사이에 대여내역이 있는 CAR_ID를 조회한다
(1) 세단과 SUV이면서, (2)2022-11-01 ~ 2022-11-30 사이에 대여내역이 있는 CAR_ID가 아닌 것을 CAR에서 조회한다
1. 이 때 30일 탑승 가격을 미리 구한다
2. PLAN과 JOIN을 위해 '30일 이상'이 포함되는 컬럼도 추가해줬다
PLAN과 조인한다. 기준은 '30일 이상'이면서 CAR_TYPE이 같은 것들이다.
1. 해당하는 할인이 없으면 NULL로 나오도록 LEFT JOIN을 수행하였고, IFNULL로 처리해줬다

WHERE절에 FEE를 사용할 수 없어서 저렇게 적었는데, 이유는 WHERE절이 SELECT의 별칭이 만들어지기 전 미리 실행되기 때문이었다. (SQL 실행 순서는 FROM -> WHERE -> GROUP BY -> HAVING -> SELECT -> ORDER BY이다)

FEE라는 alias를 사용하고 싶다면 Common Table Expression(CTE)를 이용할 수 있다.

CTE를 사용하여 FEE를 먼저 계산하고, 이후 WHERE절로 사용할 수 있다.

WITH TMP AS (
    SELECT 
        C.CAR_ID,
        C.CAR_TYPE,
        ROUND(C.RAW_FEE * (100 - IFNULL(P.DISCOUNT_RATE, 0)) / 100) AS FEE
    FROM (
        SELECT CAR_ID, CAR_TYPE,
            DAILY_FEE * 30 AS RAW_FEE,
            '30일 이상' AS DURATION_TYPE 
        FROM CAR_RENTAL_COMPANY_CAR 
        WHERE 1=1
            AND CAR_TYPE IN ('세단', 'SUV')
            AND CAR_ID NOT IN (
                SELECT CAR_ID
                FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY 
                WHERE START_DATE <= '2022-11-30' AND END_DATE >= '2022-11-01'
            )
    ) C
    LEFT JOIN CAR_RENTAL_COMPANY_DISCOUNT_PLAN P
    ON C.CAR_TYPE = P.CAR_TYPE AND C.DURATION_TYPE = P.DURATION_TYPE
)

SELECT * 
FROM TMP
WHERE 1=1
    AND FEE >= 500000 
    AND FEE < 2000000
ORDER BY FEE DESC, CAR_TYPE ASC, CAR_ID DESC

조금 더 개선한 코드는 아래와 같다.

WITH TMP AS (
    SELECT 
        C.CAR_ID,
        C.CAR_TYPE,
        ROUND(C.DAILY_FEE * 30 * (100 - IFNULL(P.DISCOUNT_RATE, 0)) / 100) AS FEE
    FROM CAR_RENTAL_COMPANY_CAR C
    LEFT JOIN (
        SELECT *
        FROM CAR_RENTAL_COMPANY_DISCOUNT_PLAN
        WHERE DURATION_TYPE = '30일 이상'
    ) P ON C.CAR_TYPE = P.CAR_TYPE
    WHERE C.CAR_TYPE IN ('세단', 'SUV')
        AND C.CAR_ID NOT IN (
            SELECT CAR_ID
            FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY 
            WHERE START_DATE <= '2022-11-30' AND END_DATE >= '2022-11-01'
        )
)

SELECT * 
FROM TMP
WHERE 1=1
    AND FEE >= 500000 
    AND FEE < 2000000
ORDER BY FEE DESC, CAR_TYPE ASC, CAR_ID DESC

30일이상인 PLAN만 미리 조회하는 로직만 추가했다. 나머지는 비슷하다.

2. 5월 식품들의 총매출 조회하기 - 레벨4

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

GROUP BY를 잘 안다면 어렵지 않게 풀 수 있다.

3. 주문량이 많은 아이스크림들 조회하기 - 레벨4

SELECT F.FLAVOR
FROM FIRST_HALF F
    LEFT JOIN (SELECT FLAVOR, sum(TOTAL_ORDER) AS JULY_TOTAL_ORDER
                FROM JULY
                GROUP BY FLAVOR) J
        ON F.FLAVOR = J.FLAVOR
ORDER BY TOTAL_ORDER + IFNULL(JULY_TOTAL_ORDER, 0) DESC
LIMIT 3;

문제 이해가 안돼서 조금 고생했다.

FIRST_HALF 테이블은 FLAVOR를 PK로 가지고 있고, FLAVOR당 총 주문량이 저장되어 있다.

JULY는 SHIPMENT_ID를 PK로 가지기 때문에 FLAVOR가 여러 개 나올 수 있다.

목표는 JULY에서 FLAVOR별로 주문량을 구하고, FIRST_HALF값이랑 더하는 것이다

JULY 테이블에서 FLAVOR당 총 주문량을 GROUP BY로 구해준다
FIRST_HALF 테이블에는 FLAVOR가 PK로 저장되어있기 때문에 FLAVOR당 총 주문량이 이미 저장되어 있다
FLAVOR를 기준으로 JOIN을 수행하고, ORDER BY 한 결과 상위 3개를 출력한다.

하지만 JULY 테이블에 있는 flavor가 FIRST_HALF 테이블의 flavor로 없을 수 있는 경우도 고려해야한다는 생각이 들었다. 둘 다 NULL이 나올 수 있도록 처리하기 위해 FULL OUTER JOIN을 사용하는 게 맞는 것 같았다. 하지만 mysql 에서는 FULL OUTER JOIN을 지원하지 않았고, UNION을 사용하여 이를 구현하였다.

SELECT FLAVOR
FROM (
    SELECT F.FLAVOR, (IFNULL(F.TOTAL_ORDER, 0) + IFNULL(J.JULY_TOTAL_ORDER, 0)) AS TOTAL
    FROM FIRST_HALF F
        LEFT JOIN (SELECT FLAVOR, SUM(TOTAL_ORDER) AS JULY_TOTAL_ORDER
                  FROM JULY
                  GROUP BY FLAVOR) J
        ON F.FLAVOR = J.FLAVOR
    UNION
    SELECT J.FLAVOR, (IFNULL(F.TOTAL_ORDER, 0) + IFNULL(J.JULY_TOTAL_ORDER, 0)) AS TOTAL
    FROM FIRST_HALF F
        RIGHT JOIN (SELECT FLAVOR, SUM(TOTAL_ORDER) AS JULY_TOTAL_ORDER
                   FROM JULY
                   GROUP BY FLAVOR) J
        ON F.FLAVOR = J.FLAVOR
) AS COMBINED
ORDER BY TOTAL DESC
LIMIT 3;

이렇게 하면 FLAVOR가 서로 없는 값을 가져도 집계에 포함이 가능하다.

(왜 그냥 INNER JOIN으로도 정답처리가 되는지 잘 모르겠다)

<더 좋은 풀이>

SELECT FLAVOR
FROM (
    SELECT *
    FROM FIRST_HALF 
    UNION
    SELECT *
    FROM JULY
) A
GROUP BY FLAVOR
ORDER BY SUM(TOTAL_ORDER) DESC
LIMIT 3

이 풀이가 딱 의도대로 푼 풀이가 아닐까 싶다.

UNION으로 합치고, GROUP BY로 정렬하면 쉽게 풀린다.

4. 조건에 맞는 도서와 저자 리스트 출력하기 - 레벨2

SELECT BOOK_ID, AUTHOR_NAME, DATE_FORMAT(PUBLISHED_DATE, '%Y-%m-%d') AS PUBLISHED_DATE
FROM BOOK B
JOIN AUTHOR A
ON B.AUTHOR_ID = A.AUTHOR_ID
WHERE B.CATEGORY = '경제'
ORDER BY PUBLISHED_DATE ASC

어렵지 않다

5. 그룹별 조건에 맞는 식당 목록 출력하기 - 레벨4

SELECT M.MEMBER_NAME, R.REVIEW_TEXT, 
    DATE_FORMAT(R.REVIEW_DATE, "%Y-%m-%d") as REVIEW_DATE
FROM REST_REVIEW R
JOIN MEMBER_PROFILE M
ON R.MEMBER_ID = M.MEMBER_ID
WHERE R.MEMBER_ID IN
(
    SELECT MEMBER_ID
    FROM REST_REVIEW
    GROUP BY MEMBER_ID
    HAVING COUNT(*) = (
        SELECT COUNT(*) cnt
        FROM REST_REVIEW
        GROUP BY MEMBER_ID
        ORDER BY cnt DESC
        LIMIT 1
    )
)
ORDER BY REVIEW_DATE ASC, REVIEW_TEXT ASC

더 좋은 방법이 있을 거라고 생각했지만 떠오르지 않아서 아는 범위 내에서 열심히 답을 출력했다.

가장 많은 리뷰 수 구하기: REST_REVIEW에서 MEMBER_ID 별로 GROUP BY를 수행하여 사람 당 쓴 리뷰 수를 구한다. 이 때 리뷰를 쓴 개수가 많은 순으로 내림차순 정렬 후 하나만 뽑았다.
가장 많은 리뷰 수를 가지는 MEMBER_ID를 구했다. 가장 많은 리뷰 수를 가지는 멤버는 여러 명이 될 수 있다
해당 MEMBER_ID의 리뷰를 출력한다.

IN이나 서브쿼리보다는 JOIN을 사용하는 게 성능에 도움이 된다고 한다. (옵티마이저가 최적화하기 용이함) 내 풀이는 서브쿼리를 3개나 넣어서 조금 부끄러웠다. 분명 더 좋은 풀이가 있을 거라고 생각해 찾아봤다.

WITH ReviewCounts AS (
    SELECT MEMBER_ID, COUNT(*) as review_count
    FROM REST_REVIEW
    GROUP BY MEMBER_ID
),
MaxReviewer AS (
    SELECT MAX(review_count) as max_count
    FROM ReviewCounts
)

SELECT 
    M.MEMBER_NAME, 
    R.REVIEW_TEXT, 
    DATE_FORMAT(R.REVIEW_DATE, '%Y-%m-%d') as REVIEW_DATE
FROM REST_REVIEW R
JOIN MEMBER_PROFILE M ON R.MEMBER_ID = M.MEMBER_ID
JOIN ReviewCounts RC ON R.MEMBER_ID = RC.MEMBER_ID
JOIN MaxReviewer MR ON RC.review_count = MR.max_count
ORDER BY R.REVIEW_DATE ASC, R.REVIEW_TEXT ASC;

내 풀이를 CTE로 분리하고, COUNT(*) 연산 수를 줄인 풀이이다.

MEMBER_ID별로 리뷰 수를 구하는 ReviewCounts 를 만든다
ReviewCounts CTE에서 가장 많은 리뷰 수를 구하는 MaxReviewer를 만든다
JOIN을 통해 MaxReviewer와 RevieweCounts에서 최대 리뷰 수를 가지는 MEBMER_ID를 뽑아낸다
3번과 REST_REVIEW, MEMBER_PROFILE 을 JOIN하여 리뷰를 구해낸다

설명보다 코드를 보면 쉽게 이해할 수 있을 거라고 생각한다.

(+ 인터넷에 검색해보면 가장 많은 수의 리뷰를 적은 사람이 1명이라고 가정하고 푼 경우가 많고, 프로그래머스는 해당 답을 정답처리 해주는 것 같다. 문제를 읽으면 가장 많은 수의 리뷰를 적은 사람이 무조건 1명이라는 제약조건이 없다. 실제 쿼리를 작성해야할 때는 이 경우를 꼭 고려해야하기 때문에 문제를 풀 때부터 엣지케이스를 잘 생각하는 습관을 들이면 좋을 것 같다)

6, 없어진 기록 찾기 - 레벨3

SELECT ANIMAL_ID, NAME
FROM ANIMAL_OUTS
WHERE ANIMAL_ID NOT IN 
(
    SELECT I.ANIMAL_ID
    FROM ANIMAL_INS I
    LEFT JOIN ANIMAL_OUTS O
    ON I.ANIMAL_ID = O.ANIMAL_ID
)
ORDER BY ANIMAL_ID ASC;

OUTS와 INS의 차집합을 구하면 되는 문제인데 어떻게 구현하면 좋을지 떠오르지 않아 서브쿼리를 사용하였다.

차집합은 아래와 같이 사용하면 더 쉽게 구할 수 있다.

SELECT O.ANIMAL_ID, O.NAME
FROM ANIMAL_OUTS O
LEFT JOIN ANIMAL_INS I ON O.ANIMAL_ID = I.ANIMAL_ID
WHERE I.ANIMAL_ID IS NULL
ORDER BY O.ANIMAL_ID ASC;

외워두면 좋을 것 같다.

7. 있었는데요 없었습니다 - 레벨3

-- 코드를 입력하세요
SELECT OUTS.ANIMAL_ID, OUTS.NAME
FROM ANIMAL_OUTS OUTS
JOIN ANIMAL_INS INS
ON OUTS.ANIMAL_ID = INS.ANIMAL_ID
WHERE INS.DATETIME > OUTS.DATETIME
ORDER BY INS.DATETIME

JOIN만 잘하면 되는 문제다. 어렵지 않다.

입양일이 존재해야하기 때문에 INNER JOIN을 사용하였고, 보호 시작일보다 입양일이 더 빠른 동물만 조회하였다.

8. 오랜 기간 보호한 동물(1) - 레벨3

SELECT I.NAME, I.DATETIME
FROM ANIMAL_INS I
LEFT JOIN ANIMAL_OUTS O
ON I.ANIMAL_ID = O.ANIMAL_ID
WHERE O.ANIMAL_ID IS NULL
ORDER BY I.DATETIME ASC
LIMIT 3

차집합을 구하기만 하면 되는 어렵지 않은 문제다. 6번 문제에서 사용한 차집합 구하는 방식을 사용하였다.

9. 보호소에서 중성화한 동물 - 레벨4

SELECT O.ANIMAL_ID, O.ANIMAL_TYPE, O.NAME
FROM (
    SELECT ANIMAL_ID, ANIMAL_TYPE, NAME
    FROM ANIMAL_OUTS
    WHERE SEX_UPON_OUTCOME like 'Spayed%' OR SEX_UPON_OUTCOME like 'Neutered%' 
) O
JOIN ANIMAL_INS I
ON O.ANIMAL_ID = I.ANIMAL_ID
WHERE I.ANIMAL_ID NOT IN (
    SELECT ANIMAL_ID
    FROM ANIMAL_INS
    WHERE SEX_UPON_INTAKE like 'Spayed%' OR SEX_UPON_INTAKE like 'Neutered%' 
)
ORDER BY O.ANIMAL_ID

LIKE의 반대를 쓰고싶었는데 어떻게 쓰는지 몰라 서브쿼리에 넣었다. 더 좋은 방법이 있을 것 같아 다른 풀이를 찾아봤다.

알고보니 INTACT가 포함되면 중성화되지 않은 거였다...문제를 읽지 않아서 좀 돌아간 것 같다. 그래도 NOT LIKE를 알아둬서 나쁠 것이 없으니 다음과 같이 풀이를 개선하였다.

SELECT O.ANIMAL_ID, O.ANIMAL_TYPE, O.NAME
FROM ANIMAL_OUTS O
JOIN ANIMAL_INS I ON O.ANIMAL_ID = I.ANIMAL_ID
WHERE (O.SEX_UPON_OUTCOME LIKE 'Spayed%' OR O.SEX_UPON_OUTCOME LIKE 'Neutered%')
AND (I.SEX_UPON_INTAKE NOT LIKE 'Spayed%' AND I.SEX_UPON_INTAKE NOT LIKE 'Neutered%')
ORDER BY O.ANIMAL_ID;

LIKE의 반대를 원하면 NOT LIKE 로 쓰면 된다.

10. 상품 별 오프라인 매출 구하기 - 레벨2

SELECT P.PRODUCT_CODE,
    SUM(O.SALES_AMOUNT * P.PRICE) AS SALES
FROM OFFLINE_SALE O
JOIN PRODUCT P
ON O.PRODUCT_ID = P.PRODUCT_ID
GROUP BY P.PRODUCT_CODE
ORDER BY SALES DESC, PRODUCT_CODE ASC

SUM과 GROUP BY만 알면 쉽게 풀리는 문제다

11. 상품을 구매한 회원 비율 구하기 - 레벨5

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

열심히 풀고자했지만 GROUP BY에 익숙하지 않아 잘 안풀렸다. 그래서 답을 참고했다.

JOIN후에 YEAR, MONTH 로 GROUP BY를 수행해준다
물건을 구매한 USER의 JOINED 년도는 2021년도여야 한다
DISTINCT로 중복 USER를 제외한 사람의 수를 COUNT해준다(한 달에 2번 주문이 가능하니까)
비율에서 분모는 가입한 날짜가 2021년도인 USER들의 수를 SELECT하는 쿼리로 구한 사람 수로 정해준다.

하지만 이 풀이는 조회되는 모든 튜플마다 SELECT COUNT(*) FROM USER_INFO WHERE YEAR(joined) = 2021 이 쿼리가 수행되기 때문에 비효율적이다. 이를 CTE로 빼면 한 번의 계산에 구할 수 있게 된다. 개선한 풀이는 아래와 같다.

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

CTE가 정말 효자인 것 같다

12. FrontEnd 개발자 찾기 - 레벨4

SELECT ID, EMAIL, FIRST_NAME, LAST_NAME
FROM DEVELOPERS 
WHERE SKILL_CODE & (    
        SELECT SUM(CODE)
        FROM SKILLCODES 
        WHERE CATEGORY = 'Front End'
    ) > 0
ORDER BY ID ASC

카테고리가 Front End인 모든 스킬코드를 더한 값과 & 비트연산을 수행했을 때 0이 아닌 값이 나오면 프론트엔드 스택이 존재하는 개발자이다. 비트연산에 익숙하다면 어렵지 않게 풀 수 있다.

WITH절로 조금 보기좋게 만들면 다음과 같이 풀 수 있다.

WITH FrontEndSkills AS (
    SELECT SUM(CODE) as total_code 
    FROM SKILLCODES 
    WHERE CATEGORY = 'Front End'
)

SELECT 
    d.ID,
    d.EMAIL,
    d.FIRST_NAME,
    d.LAST_NAME
FROM DEVELOPERS d
JOIN FrontEndSkills s
WHERE d.SKILL_CODE & s.total_code > 0
ORDER BY d.ID ASC;

728x90

티스토리