[프로그래머스] SQL 고득점 키트 풀이 및 정리 - 4편 String, Date

Bini by Bini·2023년 4월 27일
sql
0

코테

목록 보기
24/24

4편 - String, Date
[출제 빈도 : 낮음, 평균 점수 : 낮음, 문제세트 : 16]

1편 String, Date

🎼카테고리 별 상품 개수 구하기

[조건]
1. PRODUCT_CODE의 앞 2자리 자르고 GROUP BY
2. GROUP 별 개수 구하기
3. 카테고리 코드 기준 오름차순 정렬

[답안]

SELECT LEFT(PRODUCT_CODE, 2) AS CATEGORY, COUNT(*) AS PRODUCTS
FROM PRODUCT
GROUP BY LEFT(PRODUCT_CODE, 2)
ORDER BY CATEGORY

SELECT SUBSTR(PRODUCT_CODE, 1, 2) AS CATEGORY, COUNT(PRODUCT_ID) AS PRODUCTS
FROM PRODUCT
GROUP BY SUBSTR(PRODUCT_CODE, 1, 2)
ORDER BY CATEGORY

1. 문자열 자르기 : LEFT(컬럼명, 개수) & SUBSTR(컬럼명, 시작, 끝)

LEFT(컬럼명, 왼쪽부터 가져올 문자 개수)

SUBSTR(컬럼명, 시작인덱스, 끝인덱스)

🎼오랜 기간 보호한 동물(2)

SELECT I.ANIMAL_ID, I.NAME
FROM ANIMAL_INS AS I
    JOIN ANIMAL_OUTS AS O
    ON I.ANIMAL_ID = O.ANIMAL_ID
ORDER BY O.DATETIME - I.DATETIME DESC
LIMIT 2

입양간 날짜에서 보호시작된 날을 뺀 값을 내림차순 정렬한 뒤, limit 2를 통해 최상위 2개만 보여준다.

두 날짜의 차이 구하기 : DATEDIFF(구분자, 큰날짜1, 작은날짜2)

DATEDIFF(구분자, 날짜1, 날짜2)

날짜1-날짜2
2018-10-13 - 2018-10-15 하면 2가 나옴. 처음날짜부터 끝까지 세려면 +1 해야함. (아래의 자동차 문제에서 필요..)

그냥 두 날짜를 빼도 되지만, DATEDIFF 함수를 이용해도 된다. (다른 사람 풀이에 있길래.. 가져와봤슴ㄴㄷ.)

🎼중성화된 동물


-- 1. CASE WHEN 이용
SELECT ANIMAL_ID,
        NAME,
        CASE
            WHEN SEX_UPON_INTAKE LIKE 'Neutered%' THEN 'O'
            WHEN SEX_UPON_INTAKE LIKE 'Spayed%' THEN 'O'
            ELSE 'X'
        END '중성화'
FROM ANIMAL_INS
ORDER BY ANIMAL_ID

-- 조건 간략히
SELECT ANIMAL_ID,
        NAME,
        CASE
            WHEN SEX_UPON_INTAKE LIKE 'Neutered%'
                OR SEX_UPON_INTAKE LIKE 'Spayed%' THEN 'O'
            ELSE 'X'
        END '중성화'
FROM ANIMAL_INS
ORDER BY ANIMAL_ID


--2. IF 이용
SELECT ANIMAL_ID, NAME,
    IF(SEX_UPON_INTAKE LIKE 'Neutered%' OR SEX_UPON_INTAKE LIKE 'Spayed%', 'O', 'X') AS '중성화'
FROM ANIMAL_INS
ORDER BY ANIMAL_ID

CASE WHEN

SELECT
	CASE
    	WHEN 조건1 THEN '조건1 반환값'
        WHEN 조건2 THEN '조건2 반환값'
        ELSE '충족조건 없을 때 반환값'
    END 컬럼명 지정
FROM 테이블명

IF(조건문, 참일 때 값, 거짓일 때 값)

SELECT IF(required, '필수', '선택') AS '필수여부'
FROM 테이블명

🎼루시와 엘라 찾기

SELECT ANIMAL_ID, NAME, SEX_UPON_INTAKE
FROM ANIMAL_INS
WHERE NAME IN ('Lucy', 'Ella', 'Pickle', 'Rogan', 'Sabrina', 'Mitty')
ORDER BY ANIMAL_ID

WHERE 컬럼명 IN ('값1', '값2', '값3'...)

🎼조건별로 분류하여 주문상태 출력하기

SELECT ORDER_ID, PRODUCT_ID, DATE_FORMAT(OUT_DATE, '%Y-%m-%d'),
    CASE
        WHEN OUT_DATE <= '2022-05-01' THEN '출고완료'
        WHEN OUT_DATE > '2022-05-01' THEN '출고대기'
        ELSE '출고미정'
    END '출고여부'
FROM FOOD_ORDER
ORDER BY ORDER_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 AVERAGE_DURATION >= 7
ORDER BY AVERAGE_DURATION DESC, CAR_ID DESC

DATEDIFF(끝날짜, 시작날짜)를 하고 1을 더해주지 않으면 원하는 값을 산출할 수 없다.
뺀 후, +1을 해주어야 대여 기간을 구할 수 있다.

  1. CAR_ID로 GROUP BY
  2. HAVING으로 평균 대여 기간이 7일 이상인 거만 거른다.
  3. DATEDIFF를 통해 대여 기간을 구한다. 이때 1을 더해야 기간을 얻을 수 있다.
  4. AVG를 통해 GROUP BY 한 CAR_ID별 대여 기간의 평균을 구한다. -> CAR_ID별 평균 대여 기간
  5. ROUND를 통해 평균값의 소수 두번째 자리에서 반올림 한다.

🎼취소되지 않은 진료 예약 조회하기

SELECT A.APNT_NO, P.PT_NAME, P.PT_NO, A.MCDP_CD, D.DR_NAME, A.APNT_YMD
FROM APPOINTMENT AS A
    JOIN DOCTOR AS D
    ON A.MDDR_ID = D.DR_ID
    JOIN PATIENT AS P
    ON A.PT_NO = P.PT_NO    
WHERE A.APNT_YMD LIKE '2022-04-13%'
    AND A.APNT_CNCL_YN = 'N'
    AND A.MCDP_CD = 'CS'
ORDER BY A.APNT_YMD

3개 테이블 조인 : JOIN ON JOIN ON

🎼자동차 대여 기록에서 장기/단기 대여 구분하기

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,
        IF(DATEDIFF(END_DATE, START_DATE) + 1 >= 30, '장기 대여', '단기 대여') AS RENT_TYPE
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
WHERE START_DATE LIKE '2022-09%'
ORDER BY HISTORY_ID DESC

"대여 시작일이 2022년 9월에 속하는"을 못봐서 5분은 까먹었다.. !
구현해야 하는 조건을 명확하게 파악한 뒤 두들기자.

🎼조건에 맞는 사용자 정보 조회하기

SELECT USER_ID, 
        NICKNAME, 
        CONCAT(CITY, ' ', STREET_ADDRESS1, ' ', STREET_ADDRESS2) AS 전체주소, 
        CONCAT(SUBSTR(TLNO, 1, 3), '-', SUBSTR(TLNO, 4, 4), '-', SUBSTR(TLNO, 8, 4)) AS 전화번호
FROM USED_GOODS_BOARD AS B
    LEFT JOIN USED_GOODS_USER AS U
    ON B.WRITER_ID = U.USER_ID
GROUP BY B.WRITER_ID
HAVING COUNT(*) >= 3
ORDER BY U.USER_ID DESC
  1. 작성자 기준 group by
  2. 작성한 글이 3개 이상만 필터링 (group by에서 count했을 때 3개 이상)
  3. concat을 이용하여 주소 합치기
  4. substr을 이용해 전화번호 자릿수를 자르고, concat을 이용해 사이에 - 껴서 합치기
  5. 유저 아이디 기준 내림차순 정렬

문자열 합치기 : CONCAT(문자열1, 문자열2, 문자열3, ..)

CONCAT(CITY, ' ', STREET_ADDRESS1, ' ', STREET_ADDRESS2) AS 전체주소,

공백을 두고 합치기

CONCAT(SUBSTR(TLNO, 1, 3), '-', SUBSTR(TLNO, 4, 4), '-', SUBSTR(TLNO, 8, 4))

전화번호 사이에 하이픈(-) 추가

문자열 자르기 : SUBSTR(컬럼명, 시작인덱스, 길이)

🎼조회수가 가장 많은 중고거래 게시판의 첨부파일 조회하기

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

"file_id로 내림차순 정렬"..

  1. WEHRE 구문에 서브쿼리를 통해 가장 높은 조회수인 board 행을 가져온다.

🎼대여 기록이 존재하는 자동차 리스트 구하기

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


SELECT H.CAR_ID
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY AS H
    LEFT JOIN CAR_RENTAL_COMPANY_CAR AS C
    ON H.CAR_ID = C.CAR_ID
WHERE C.CAR_TYPE = '세단'
    AND H.START_DATE LIKE '2022-10%'
GROUP BY H.CAR_ID
ORDER BY H.CAR_ID DESC

자동차 ID 리스트는 중복이 없어야 하며 -> distinct 또는 group by로 구현.

DISTINCT(컬럼명)

테이블에 카테고리라는 컬럼이 존재할 때, 이 카테고리 값이 몇 종류가 있는지 알고 싶은 경우!
카테고리를 조회할 때 이 값이 중복되면 안 되기 때문에 DISTINCT를 사용한다.
-> COUNT(DISTINCT(컬럼명))을 통해 한 테이블 내에 카테고리가 몇 종류 있는지 알 수 있다.

🎼조건에 부합하는 중고거래 상태 조회하기

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

🎼🎨자동차 대여 기록 별 대여 금액 구하기

🎨🎭🎪 복습이 필요한 문제 이렇게 긴 쿼리는 처음이라 ..

SELECT T.HISTORY_ID,
    T.PERIOD * ROUND(T.DAILY_FEE * (IF(P.DISCOUNT_RATE IS NULL, 1, (1-P.DISCOUNT_RATE/100)))) AS FEE
FROM (SELECT C.CAR_ID, C.CAR_TYPE, C.DAILY_FEE, H.HISTORY_ID, H.START_DATE, H.END_DATE,
      DATEDIFF(H.END_DATE, H.START_DATE)+1 AS PERIOD,
      CASE
        WHEN DATEDIFF(H.END_DATE, H.START_DATE)+1 >= 90 THEN '90일 이상'
        WHEN DATEDIFF(H.END_DATE, H.START_DATE)+1 >= 30 THEN '30일 이상'
        WHEN DATEDIFF(H.END_DATE, H.START_DATE)+1 >= 7 THEN '7일 이상'
        ELSE '7일 미만'
     END 'DURATION_TYPE'
     FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY AS H
     JOIN CAR_RENTAL_COMPANY_CAR AS C
     ON H.CAR_ID = C.CAR_ID
    ) AS T
    LEFT JOIN CAR_RENTAL_COMPANY_DISCOUNT_PLAN AS P
    ON P.CAR_TYPE = T.CAR_TYPE
        AND P.DURATION_TYPE = T.DURATION_TYPE
WHERE T.CAR_TYPE = '트럭'
ORDER BY FEE DESC, T.HISTORY_ID DESC

from절에서 car_type과 대여기간타입 컬럼을 포함한 새로운 테이블을 만든다. (CAR_RENTAL_COMPANY_RENTAL_HISTORY 테이블 + CAR_RENTAL_COMPANY_CAR + 대여기간타입)
새로운 테이블에 CAR_TYPE과 DURATION_TYPE(대여기간타입/ 7일미만, 7일이상, 30일이상, 90일이상)으로 CAR_RENTAL_COMPANY_DISCOUNT_PLAN 을 LEFT JOIN한다.

7일미만도 따로 타입으로 추가해야 한다. 우리가 찾는, 즉 7일이상, 30일이상, 90일이상의 경우 테이블에 있어 할인율을 적용하지만. 7일미만의 경우 할인율 적용없이 원가로 계산하기 때문이다.
7일미만의 경우 조인했을 때 NULL값으로 처리되므로, 마지막에 SELECT할 때 NULL처리를 해주어야 한다.

두가지 조건으로 JOIN 하기 : ON 조건1 AND 조건2

FROM절에서 서브쿼리를 통해 새로운 테이블 정의

이때 무조건 alias 해줘야 한다!

profile
Bini by Bini
My Precious Records
이전 포스트

[프로그래머스] SQL 고득점 키트 풀이 및 정리 - 3편 JOIN

0개의 댓글