[SQL 코드카타] 문제 71~75번 풀이

문제 출처 : 프로그래머스

 

(71번) 온라인/오프라인 판매데이터 통합하기

SELECT TO_CHAR(SALES_DATE,'YYYY-MM-DD'),
	   PRODUCT_ID, 
       USER_ID, 
       SALES_AMOUNT
FROM (
      SELECT *
      FROM ONLINE_SALE
      UNION
      SELECT OFFLINE_SALE_ID, 
      		 NULL AS USER_ID, 
             PRODUCT_ID, 
             SALES_AMOUNT, 
             SALES_DATE
      FROM OFFLINE_SALE)
WHERE 1=1
	  TO_CHAR(SALES_DATE,'YYYYMM') = '202203'
ORDER BY SALES_DATE ASC, 
		 PRODUCT_ID ASC,
         USER_ID ASC

+) UNION (중복제거) / UNION ALL (중복제거 X) : 합집합

 

(72번) 조건에 부합하는 중고거래 댓글 구하기 

WITH RE_A AS (SELECT BOARD_ID, TITLE
FROM USED_GOODS_BOARD
WHERE TO_CHAR(CREATED_DATE,'YYYYMM') = '202210')

SELECT A.TITLE,
	   B.BOARD_ID, 
       B.REPLY_ID, 
       B.WRITER_ID, 
       B.CONTENTS, 
       TO_CHAR(B.CREATED_DATE,'YYYY-MM-DD')
FROM RE_A A,
	 USED_GOODS_REPLY B
WHERE 1=1
      AND A.BOARD_ID = B.BOARD_ID
ORDER BY CREATED_DATE ASC,
		 TITLE ASC

(73번) 입양 시간 구하기 (2) 

- 24시간 칼럼 만들기 : CONNECT BY LEVEL : 연속된 숫자 조회할 때 사용

- 외부조인 이후 NVL(칼럼명, 대체값) 활용하여 널 값 0 채우기

- TO_CHAR  복습

> YYYY : 년 / MM : 월 / DD : 일 / HH24 : 24시간 / HH12 : 12시 / MI : 분 / SS :  초

SELECT A.HOUR, NVL(B.COUNT,0) COUNT
FROM (SELECT LEVEL-1 AS HOUR
    FROM DUAL CONNECT BY level <25) A
    LEFT JOIN 
    (SELECT TO_NUMBER(TO_CHAR(datetime, 'hh24')) AS HOUR, COUNT(*) AS COUNT
    FROM ANIMAL_OUTS
    GROUP BY TO_CHAR(datetime, 'hh24')) B ON A.HOUR = B.HOUR
ORDER BY A.HOUR

> SET함수 활용

 

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

> 어려웠던 점 : 왜 NOT IN ( ~~~ WHERE NOT( A OR B)) 부정의 부정을 써야 하는 부분

-- 1) 11월 대여 가능한 CAR_ID 추출 후
-- 2) CAR_TYPE이 '세단'과 'SUV'인 ID 추출
WITH CAR_TYPE_O AS (SELECT CAR_ID, CAR_TYPE, DAILY_FEE
            FROM CAR_RENTAL_COMPANY_CAR
            WHERE 1=1
                    AND CAR_ID NOT IN (SELECT DISTINCT CAR_ID
                                FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
                                WHERE 1=1
                                      AND NOT (TO_CHAR(END_DATE, 'YYYYMM') < '20221101'
                                      OR TO_CHAR(START_DATE, 'YYYYMM') > '20221130'
                                ))      
                  AND CAR_TYPE IN ('세단', 'SUV')
                    )
-- 3) 그 후 대여 기간이 30일 일 때 할인률을 적용하여 계산
SELECT A.CAR_ID, A.CAR_TYPE, ROUND((DAILY_FEE * (100-DISCOUNT_RATE)/100)*30) AS FEE
FROM CAR_TYPE_O A, CAR_RENTAL_COMPANY_DISCOUNT_PLAN B
WHERE 1=1 
      AND A.CAR_TYPE = B.CAR_TYPE
      AND B.DURATION_TYPE = '30일 이상'
      AND (30 * DAILY_FEE * (100-DISCOUNT_RATE)/100) >= 500000 
      AND (30 * DAILY_FEE * (100-DISCOUNT_RATE)/100) < 2000000
ORDER BY FEE DESC, CAR_TYPE ASC, CAR_ID DESC

 

(75번) 자동차 대여 기록별 대여 금액 구하기

> CASE 절 사용할 때,   < 7 THEN X / < 30 THEN '7일 이상' --- 적용이 안 된다...

- 기간을 구해, 조건에 따른 칼럼 추가
WITH RENTAL_DURATION AS
(SELECT C.*, CASE WHEN C.DURATION < 7 THEN '할인 대상 아님'
                   WHEN C.DURATION < 30 THEN '7일 이상'
                   WHEN C.DURATION < 90 THEN '30일 이상'
              ELSE '90일 이상'
              END DURATION_TYPE
 FROM (SELECT B.HISTORY_ID,
              A.CAR_TYPE,
              A.DAILY_FEE,
              (B.END_DATE - B.START_DATE + 1) AS DURATION
 FROM CAR_RENTAL_COMPANY_CAR A JOIN CAR_RENTAL_COMPANY_RENTAL_HISTORY B 
      ON A.CAR_ID = B.CAR_ID
 WHERE CAR_TYPE IN ('트럭')) C
 )
 -- 그 후 LEFT JOIN 활용해 합친 후 해당 할인 적용
SELECT A.HISTORY_ID, 
       CASE WHEN DURATION < 7 THEN A.DAILY_FEE*A.DURATION
            WHEN DURATION < 30 THEN (100-B.DISCOUNT_RATE)/100*A.DAILY_FEE*A.DURATION
            WHEN DURATION < 90 THEN (100-B.DISCOUNT_RATE)/100*A.DAILY_FEE*A.DURATION
            ELSE (100-B.DISCOUNT_RATE)/100*A.DAILY_FEE*A.DURATION END FEE
FROM RENTAL_DURATION A LEFT JOIN CAR_RENTAL_COMPANY_DISCOUNT_PLAN B
     ON 1=1 
        AND A.CAR_TYPE = B.CAR_TYPE
        AND A.DURATION_TYPE = B.DURATION_TYPE 
ORDER BY FEE DESC, A.HISTORY_ID DESC

[ 복습 ]
-- 행의 수 제한

WHERE ROWNUM 비교연산자 n -- 1.
FETCH FRIST n ROWS ONLY -- 2. (마지막 행에)

-- NULL 값 처리

SELECT NVL(COL1, NULL 대체값) FRRM TABLE

* 참고) NULL과의 연산 결과값은 NULL
-- NULLIF는 특정 값을 NULL 처리할 때 사용

SELECT NAME, NULLIF(attr, 'normal') FROM POKEMON

-- COALESCE함수 : 여러 값 중에서 NULL이 아닌 첫 번째 값을 찾을 때 사용.

- DECODE 함수 LIKE CASE문

DECODE(EXPRESSION, VALUE1, RESULT1, VALUE2, RESULT2, ..., DEFAULT_RESULT)