Programmers SQL 고득점 Kit 풀기 - String, Date(1)

 

 

이전 글

https://fladi.tistory.com/447

 

 

서론

이전에 IS NULL을 풀어봤다. 이번에는 String, Date를 빠르게 풀고 Group by와 JOIN으로 넘어가려고 한다. 사이트는 다음과 같다. 

https://school.programmers.co.kr/learn/courses/30/parts/17047

 

 

1. 자동차 평균 대여 기간 구하기 - 레벨2

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 AVERAGE_DURATION >= 7
ORDER BY AVERAGE_DURATION DESC, CAR_ID DESC

• DATEDIFF라는 함수를 알아야한다. DATEDIFF는 차이 일수를 반환하는 함수다
  • 대여일의 경우, 당일을 포함해야하기 때문에 (날짜2 - 날짜1 + 1) 로 계산해야한다. 그래서 +1을 해줬다
• GROUP BY와 AVG 집계함수를 통해 CAR_ID 그룹에 해당하는 평균점수를 구할 수 있다. 
• WHERE -> GROUP BY -> HAVING -> ORDER BY 순으로 쿼리가 실행되기 때문에, AVG 결과는 WHERE 조건절에 들어갈 수 없다. 그래서 HAVING에 AVERAGE_DURATION에 대한 조건을 적어줘야한다.
  • 집계함수의 결과를 필터링하기 위해 HAVING을 사용한다는 걸 꼭 기억하자!

 

 

2. 조회수가 가장 많은 중고거래 게시판의 첨부파일 조회하기 - 레벨3

SELECT CONCAT('/home/grep/src/', BOARD_ID, '/', FILE_ID, FILE_NAME, FILE_EXT) AS FILE_PATH
FROM USED_GOODS_FILE A
WHERE BOARD_ID = (
    SELECT BOARD_ID
    FROM USED_GOODS_BOARD
    ORDER BY VIEWS DESC
    LIMIT 1
)
ORDER BY FILE_ID DESC

CONCAT만 알면 어렵지 않다.

 

SELECT CONCAT('/home/grep/src/', A.BOARD_ID, '/', FILE_ID, FILE_NAME, FILE_EXT) AS FILE_PATH
FROM USED_GOODS_FILE A
JOIN (
    SELECT BOARD_ID
    FROM USED_GOODS_BOARD
    ORDER BY VIEWS DESC
    LIMIT 1
) B 
ON A.BOARD_ID = B.BOARD_ID
ORDER BY FILE_ID DESC

JOIN을 사용할 수도 있따

 

 

3. 조건에 부합하는 중고거래 상태 조회하기 - 레벨2

SELECT BOARD_ID, WRITER_ID, TITLE, PRICE, 
    CASE 
        WHEN STATUS = 'SALE' THEN '판매중'
        WHEN STATUS = 'RESERVED' THEN '예약중'
        ELSE '거래완료'
    END AS STATUS
FROM USED_GOODS_BOARD 
WHERE CREATED_DATE = '2022-10-05'
ORDER BY BOARD_ID DESC

CASE WHEN THEN ELSE END 5개만 알면 풀 수 있다

 

 

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

SELECT 
    CAR_HISTORY.HISTORY_ID, 
    ROUND(CAR_HISTORY.DAILY_FEE * CAR_HISTORY.DURATION * ((100 - IFNULL(PLAN.DISCOUNT_RATE, 0)) / 100))
    AS FEE
FROM 
(
    SELECT CAR.CAR_TYPE, CAR.DAILY_FEE, HISTORY.HISTORY_ID, DATEDIFF(END_DATE, START_DATE) + 1 as DURATION, 
        CASE
            WHEN DATEDIFF(END_DATE, START_DATE) + 1 >= 90 THEN '90일 이상'
            WHEN DATEDIFF(END_DATE, START_DATE) + 1 >= 30 THEN '30일 이상'
            WHEN DATEDIFF(END_DATE, START_DATE) + 1 >= 7 THEN '7일 이상'
            ELSE 'NONE'
        END AS DURATION_TYPE
    FROM CAR_RENTAL_COMPANY_CAR CAR
    JOIN CAR_RENTAL_COMPANY_RENTAL_HISTORY HISTORY
    ON CAR.CAR_ID = HISTORY.CAR_ID
    WHERE CAR.CAR_TYPE = '트럭'
) AS CAR_HISTORY
LEFT JOIN CAR_RENTAL_COMPANY_DISCOUNT_PLAN AS PLAN
ON CAR_HISTORY.CAR_TYPE = PLAN.CAR_TYPE
AND CAR_HISTORY.DURATION_TYPE = PLAN.DURATION_TYPE
ORDER BY FEE DESC, HISTORY_ID DESC

좀 복잡해서 어려웠다. 고민하다가 다른 사람의 풀이를 참고하였다.

1. car_id를 조건으로 car_rental_company_rental_history 테이블과 car_rental_company_car 테이블을 JOIN한다.  
   1. 이 과정에서 DATEDIFF를 사용하여 사용한 기간을 미리 구한다
   2. DATEDIFF가 7일 이상인지, 30일 이상인지, 90일 이상인지 구해서 문자열로 저장해둔다
   3. type이 트럭인 애들만 나오도록 WHERE 조건을 추가한다
2. car_rental_company_discount_plan 테이블을 조인한다. 
   1. 조건은 미리 구해둔 duration_type과 car_type을 사용한다. (JOIN의 경우 AND로 두 가지 조건을 사용할 수 있다)
   2. FEE는 미리 구한 사용한 기간과 하루 가격, 조건별 할인율을 포함하여 구한다
   3. 조건별 할인율은 NULL일 수 있기 때문에 IFNULL을 사용하여 할인가격이 0이 되도록 처리한다
   4. (%가 붙어있어도 빼기가 돼서 신기했다. 안된다면 TRIM이나 SUBSTRING을 사용해야했겠다)
3. ORDER BY를 사용하여 정렬하면 된다

 

차근차근 풀면 어느정도 로직이 떠오르긴한다. 이런 복잡한 문제는 종이에 차근차근 쓰는 연습이 필요할 것 같다. 

 

 

5. 특정 옵션이 포함된 자동차 리스트 구하기 - 레벨1

SELECT CAR_ID, CAR_TYPE, DAILY_FEE, OPTIONS
FROM CAR_RENTAL_COMPANY_CAR
WHERE OPTIONS like '%네비게이션%'
ORDER BY CAR_ID DESC

별 거 없다

 

 

6. 자동차 대여 기록에서 장기/단기 대여 구분하기 - 레벨1

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

DATE_FORMAT 형식을 까먹어서 찍어가면서 맞췄다.

Y m d를 기억하자!

 

 

7. 조건별로 분류하여 주문상태 출력하기 - 레벨3

SELECT ORDER_ID, PRODUCT_ID, 
    DATE_FORMAT(OUT_DATE, "%Y-%m-%d"), 
    CASE
        WHEN OUT_DATE IS NULL THEN '출고미정'
        WHEN DATEDIFF('2022-05-01', OUT_DATE) >= 0 THEN '출고완료'
        ELSE '출고대기'
    END AS 출고여부
FROM FOOD_ORDER
ORDER BY ORDER_ID ASC

DATE_FORMAT과 DATEDIFF만 알면 어렵지 않다!

 

 

8. 대여 기록이 존재하는 자동차 리스트 구하기 - 레벨3

SELECT DISTINCT H.CAR_ID AS CAR_ID
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY H
JOIN CAR_RENTAL_COMPANY_CAR C
ON H.CAR_ID = C.CAR_ID
WHERE MONTH(H.START_DATE) = 10 AND C.CAR_TYPE = '세단'
ORDER BY CAR_ID DESC

MONTH 함수를 알면 어렵지 않다

like를 써도 풀릴 것 같긴하다.

 

 

9. 조건에 맞는 사용자 정보 조회하기 - 레벨3

SELECT USER_ID, NICKNAME, 
    CONCAT(CITY, ' ', 	STREET_ADDRESS1, ' ', STREET_ADDRESS2) AS 전체주소,
    CONCAT(SUBSTRING(TLNO, 1, 3), '-', SUBSTRING(TLNO, 4, 4), '-', SUBSTRING(TLNO, 8, 4))
    AS 전화번호
FROM USED_GOODS_USER U
WHERE USER_ID IN (
    SELECT WRITER_ID
    FROM USED_GOODS_BOARD 
    GROUP BY WRITER_ID
    HAVING COUNT(WRITER_ID) >= 3
)
ORDER BY USER_ID DESC

LENGTH 함수를 찍어서 맞췄다. 꼭 잘 외우고 있어야겠다. 

SUBSTRING의 3번째 매개변수는 개수인 것 같았다. 프로그래밍 언어들과 조금 달라서 사용법을 외워둬야겠다고 생각했다. 1부터 시작하는 것도 유의해야할 사항이다.

 

JOIN과 GROUP BY를 써도 되지만 GROUP BY가 익숙하지 않아서 서브쿼리를 사용하였다. JOIN을 사용한 풀이는 다음과 같다.

SELECT USER_ID, NICKNAME, 
    CONCAT(CITY, ' ', 	STREET_ADDRESS1, ' ', STREET_ADDRESS2) AS 전체주소,
    CONCAT(SUBSTRING(TLNO, 1, 3), '-', SUBSTRING(TLNO, 4, 4), '-', SUBSTRING(TLNO, 8, 4))
    AS 전화번호
FROM USED_GOODS_USER U
JOIN USED_GOODS_BOARD B
ON U.USER_ID = B.WRITER_ID
GROUP BY USER_ID
HAVING COUNT(WRITER_ID) >= 3
ORDER BY USER_ID DESC

 

 

10. 취소되지 않은 진료 예약 조회하기 - 레벨4

SELECT AD.APNT_NO, P.PT_NAME, P.PT_NO, AD.MCDP_CD, AD.DR_NAME, AD.APNT_YMD as APNT_YMD
FROM (
    SELECT A.APNT_NO, D.MCDP_CD, D.DR_NAME, A.APNT_YMD, A.PT_NO
    FROM APPOINTMENT A
    LEFT JOIN DOCTOR D
    ON A.MDDR_ID = D.DR_ID
    WHERE A.MCDP_CD = 'CS'
        AND APNT_CNCL_YN = 'N'
        AND YEAR(APNT_YMD) = 2022
        AND MONTH(APNT_YMD) = 4
        AND DAY(APNT_YMD) = 13
) AD
LEFT JOIN PATIENT P
ON AD.PT_NO = P.PT_NO
ORDER BY APNT_YMD;

APPOINTMENT와 DOCTOR를 먼저 조인하면서 필요조건을 걸러내고, PATIENT를 JOIN해줬다.

2022-04-13에 해당하는 것을 like로 찾으려다가 YEAR, MONTH, DAY 함수를 사용해봤다.

 

나는 서브쿼리를 사용했는데, 3조인으로 해도 충분히 가능한 문제다.