[프로그래머스]SQL쿼리테스트 Lv4(3)

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

https://school.programmers.co.kr/learn/courses/30/lessons/131124

프로그래머스

WITH MANY_REVIEW AS (
    SELECT MEMBER_ID
    FROM (
        SELECT MEMBER_ID, COUNT(*) AS CNT
        FROM REST_REVIEW
        GROUP BY MEMBER_ID
        ORDER BY CNT DESC
        LIMIT 1)   
        AS MAX_REVIEWER
)
SELECT 
    M.MEMBER_NAME,
    R.REVIEW_TEXT,
    DATE_FORMAT(R.REVIEW_DATE, '%Y-%m-%d') AS REVIEW_DATE 
FROM REST_REVIEW AS R
JOIN MEMBER_PROFILE AS M 
ON R.MEMBER_ID = M.MEMBER_ID
WHERE R.MEMBER_ID = (SELECT MEMBER_ID FROM MANY_REVIEW)
ORDER BY REVIEW_DATE ASC, R.REVIEW_TEXT ASC;

POINT!

문제 풀이의 핵심은 리뷰를 가장 많이 남긴 고객 한명을 구하고

그것을 메인 쿼리의 WHERE절에 조건으로 넣어주는 거죠.

알아야하는 건 WITH문에 있는 값이 테이블이기 때문에 

메인 쿼리에 조건으로 들어갈때는 서브쿼리를 통해 값을 특정지어서 작성을 해줘야한다는 것입니다!

 

2. 오프라인/온라인 판매 데이터 통합하기

https://school.programmers.co.kr/learn/courses/30/lessons/131537

프로그래머스

WITH JOIN_TABLE AS (
    SELECT USER_ID, PRODUCT_ID, SALES_DATE, SALES_AMOUNT
    FROM ONLINE_SALE

    UNION ALL

    SELECT NULL AS USER_ID, PRODUCT_ID, SALES_DATE, SALES_AMOUNT
    FROM OFFLINE_SALE
)

SELECT 
    DATE_FORMAT(SALES_DATE,'%Y-%m-%d') AS SALES_DATE,
    PRODUCT_ID,
    USER_ID,
    SALES_AMOUNT
FROM JOIN_TABLE
WHERE DATE_FORMAT(SALES_DATE,'%Y-%m') = '2022-03'
ORDER BY SALES_DATE, PRODUCT_ID, USER_ID;

POINT!

왜 JOIN이 아닌 UNION ALL을 사용했는지 설명드리겠습니다.

 

ONLINE_SALE 테이블은 USER_ID를 가지고 있지만, OFFLINE_SALE 테이블은 그렇지 않습니다.
따라서 단순히 JOIN을 사용할 경우, USER_ID가 기준이 되는 온라인 데이터를 중심으로 병합이 이루어지게 됩니다.

                          

문제는, MySQL에서는 FULL OUTER JOIN을 지원하지 않는다는 점입니다.
이로 인해 LEFT JOIN이나 RIGHT JOIN을 사용할 경우,
오프라인에서만 존재하는 데이터는 누락되거나 USER_ID가 잘못 병합될 수 있습니다.

 

하지만 문제 조건에서는 오프라인 판매 데이터도 포함하되, USER_ID는 NULL로 표시하라고 되어 있습니다.

이러한 조건을 만족시키기 위해, 온라인 판매와 오프라인 판매 데이터를 수직으로 결합할 수 있는 UNION ALL을 사용하였습니다.

 

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

https://school.programmers.co.kr/learn/courses/30/lessons/157339

프로그래머스

WITH CAR_INFO_30 AS (
    SELECT 
        CAR.CAR_ID,
        CAR.CAR_TYPE,
        CAR.DAILY_FEE,
        PLAN.DISCOUNT_RATE
    FROM CAR_RENTAL_COMPANY_CAR AS CAR
        JOIN CAR_RENTAL_COMPANY_DISCOUNT_PLAN AS PLAN
        ON CAR.CAR_TYPE = PLAN.CAR_TYPE
    WHERE PLAN.DURATION_TYPE = '30일 이상' 
        AND CAR.CAR_TYPE IN ('SUV','세단')
)
SELECT 
    CAR_ID,
    CAR_TYPE,
    FLOOR(DAILY_FEE * (1 - (DISCOUNT_RATE/100)) * 30) AS FEE
FROM 
	CAR_INFO_30
WHERE 
	DAILY_FEE * (1 - (DISCOUNT_RATE/100)) * 30 BETWEEN 500000 AND 1999999
	AND CAR_ID NOT IN (
    	SELECT CAR_ID
        FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY 
        WHERE NOT(END_DATE < '2022-11-01' OR START_DATE > '2022-11-30'))
ORDER BY FEE DESC, CAR_TYPE ASC, CAR_ID DESC

문제 주의 해야할 포인트 

 1. NOT IN (...) 사용 이유

 

• WHERE NOT (...) 안쪽:
  → START_DATE > 11월 30일 또는 END_DATE < 11월 1일이 아닌 11월과 겹치는 대여만 추출
• 그런 CAR_ID들을 NOT IN으로 제외
  →  11월에 한 번이라도 대여된 차량은 전부 제외됩니다

2. NOT (...) 안 쓰고 OR로만 쓰면?

• 이건 11월과 전혀 겹치지 않는 대여만 남깁니다
• 하지만 대여 이력이 여러 개 있는 경우, 한 이력은 겹치지 않지만 다른 이력이 겹치면 그 차량이 잘못 포함될 수 있습니다. 그래서 NOT IN (11월과 겹치는 CAR_ID)가 더 안전합니다

4. 자동차 대여 기록 별 대여 금액 구하기

https://school.programmers.co.kr/learn/courses/30/lessons/151141

프로그래머스

SELECT 
    H.HISTORY_ID, 
    FLOOR(R.DAILY_FEE * H.RENT_DAY * (1 - IFNULL(MAX(D.DISCOUNT_RATE), 0) / 100)) AS FEE
FROM 
    CAR_RENTAL_COMPANY_CAR AS R
JOIN (
    SELECT 
        HISTORY_ID, 
        CAR_ID, 
        DATEDIFF(END_DATE, START_DATE) + 1 AS RENT_DAY
    FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
) AS H ON R.CAR_ID = H.CAR_ID
LEFT JOIN (
    SELECT 
        CAR_TYPE,
        CAST(SUBSTRING_INDEX(DURATION_TYPE, '일', 1) AS UNSIGNED) AS DURATION_DAYS,
        CAST(SUBSTRING_INDEX(DISCOUNT_RATE, '%', 1) AS UNSIGNED) AS DISCOUNT_RATE
    FROM CAR_RENTAL_COMPANY_DISCOUNT_PLAN
) AS D ON R.CAR_TYPE = D.CAR_TYPE AND H.RENT_DAY > D.DURATION_DAYS
WHERE R.CAR_TYPE = '트럭'
GROUP BY H.HISTORY_ID, R.DAILY_FEE, H.RENT_DAY
ORDER BY FEE DESC, H.HISTORY_ID DESC;

 

자동차 정보를 담은 CAR_RENTAL_COMPANY_CAR을 기준으로 JOIN을 해줄겁니다. 

 

1. [첫번째 JOIN] : 자동자 대여기록을  가져오는 서브 쿼리 

대여기간을 종료일을 포함해서 계산 해야하기 때문에 +1을 해줬습니다.

그리고 CAR_ID를 기준으로  병합을 합니다.

 SELECT 
        HISTORY_ID, 
        CAR_ID, 
        DATEDIFF(END_DATE, START_DATE) + 1 AS RENT_DAY
    FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY

 

---

2. [두번째 JOIN] : 할인율 정보를 갖고 있는 CAR_RENTAL_COMPANY_DISCOUNT_PLAN을 사용했습니다.

SELECT 
        CAR_TYPE,
        CAST(SUBSTRING_INDEX(DURATION_TYPE, '일', 1) AS UNSIGNED) AS DURATION_DAYS,
        CAST(SUBSTRING_INDEX(DISCOUNT_RATE, '%', 1) AS UNSIGNED) AS DISCOUNT_RATE
    FROM CAR_RENTAL_COMPANY_DISCOUNT_PLAN
) AS D ON R.CAR_TYPE = D.CAR_TYPE AND H.RENT_DAY > D.DURATION_DAYS

① 문자열로 저장된 할인율 값을 숫자로 바꿔줍니다.

• SUBSTRING_INDEX(..., '일', 1)은 '90일 이상' → '90'
• CAST(... AS UNSIGNED)로 숫자형 변환

② ON절 뒤에 AND(조건 연산자)사용

우리가 찾아야하는건 대여일수에 맞는 할인 조건이죠

예를 들어 사용자가 31일 동안 차량을 대여했다면 '7일 이상','30일 이상' 두 할인 조건에 모두 만족하게 됩니다

하지만 둘중에 더 높은 할인율이 적용되어야하죠

 

그래서 일단은 조건을 만족하는 모든 할인 정책을 JOIN하고,

마지막 SELECT절에서 MAX(DISCOUNT_RATE)를 써서 가장 높은 할인율을 선택하는 겁니다!

 

해당 쿼리에서는 

ON R.CAR_TYPE = D.CAR_TYPE          -- 조건 1: 차량 종류가 같고
AND H.RENT_DAY > D.DURATION_DAYS    -- 조건 2: 대여일수가 할인 기준일수보다 많을 때

이렇게 조건을 걸어서 해당 대여일이 만족하는  할인 조건을 모두 붙이는 겁니다.

 

여기서 POINT!

ON조건은 서브쿼리가 실행된 이후의 영역이므로, 서브쿼리에서 이미 만들어진 D.DURAION_DAYS 를 사용할 수 있습니다.

---

3. 조건에 따라 CAR_TYPE = '트럭' 만 필터링

4. 하나의 HSTORY_ID에 여러 할인율이 붙을 수 있으므로 GROUP BY가 필요

5. 최종 원하는 데이터 추출하기 (SELECT절)

SELECT 
    H.HISTORY_ID, 
    FLOOR(R.DAILY_FEE * H.RENT_DAY * (1 - IFNULL(MAX(D.DISCOUNT_RATE), 0) / 100)) AS FEE

• 마지막에 FEE를 구할 때는  할인율이 없을 경우를 고려해줘야 합니다. 그래서 IFNULL을 사용해서 NULL인 경우에는 0으로  적용을 해주는 거죠
• MAX(D.DISCOUNT_RATE)는 위에서 말씀드렸다시피 조건을 만족하는 모든 할인 정책에서 가장 높은 할인 정책을  적용해주기 위해서 MAX를 사용했습니다.