[SQL] Product Price at a Given Date(LeetCode/Oracle)

안녕하세요!

 

이번 포스팅은 LeetCode에 있는 Product Price at a Given Date문제를 OracleDB로 풀어보려고 합니다!

 

(모든 문제는 Oracle로 풀이하겠습니다.)

 

1. 문제 링크 : https://leetcode.com/problems/product-price-at-a-given-date/description/

 

2. 문제

Column name	Type
product_id	int
new_price	int
change_date	date

[문제] Write a solution to find the prices of all products on 2019-08-16. Assume the price of all products before any change is 10.

2019-08-16일 모든 제품의 가격을 조회하는 문제입니다. 변경 전 모든 제품의 가격은 10으로 가정합니다.

 

3. 제출 쿼리 및 설명

 

1) 2019년 8월 16일의 가격을 보고 싶은 거니까, 가격을 바꾼 날짜가 2019년 8월 16일 이전인 데이터를 가져옵니다. 

SELECT * 
FROM PRODUCTS 
WHERE CHANGE_DATE <= '2019-08-16'

2) 2019년 8월 16일 이전에 가격을 바꾼 적이 없는 제품도 초기 가격(10원)으로 나와야 하기 때문에 원본 테이블과 LEFT JOIN 합니다.

FROM PRODUCTS P1, (SELECT * FROM PRODUCTS WHERE CHANGE_DATE <= '2019-08-16') P2
WHERE P1.PRODUCT_ID = P2.PRODUCT_ID(+)

3) 윈도우 순위 함수 ROW_NUMBER를 활용해서 각 제품 별로 2019년 8월 16일까지 최근에 바꾼 순서로 정렬한 순위 리스트를 생성합니다.

FROM (
    SELECT P1.PRODUCT_ID, P2.NEW_PRICE,
        ROW_NUMBER() OVER(PARTITION BY P1.PRODUCT_ID ORDER BY P2.CHANGE_DATE DESC) AS RN
    FROM PRODUCTS P1, (SELECT * FROM PRODUCTS WHERE CHANGE_DATE <= '2019-08-16') P2
    WHERE P1.PRODUCT_ID = P2.PRODUCT_ID(+)
    )

최종) 조건을 RN = 1로 줘서, 각 제품 별로 가장 최근에 바꾼 날짜의 가격을 조회합니다. 만약에 없으면 NVL 함수를 활용해서 초기 가격 10원으로 설정합니다.

SELECT PRODUCT_ID, NVL(NEW_PRICE, 10) AS PRICE
FROM (
    SELECT P1.PRODUCT_ID, P2.NEW_PRICE,
        ROW_NUMBER() OVER(PARTITION BY P1.PRODUCT_ID ORDER BY P2.CHANGE_DATE DESC) AS RN
    FROM PRODUCTS P1, (SELECT * FROM PRODUCTS WHERE CHANGE_DATE <= '2019-08-16') P2
    WHERE P1.PRODUCT_ID = P2.PRODUCT_ID(+)
    )
WHERE RN = 1
;