윈도우 함수 활용 ( ROW_NUMBER() )

 

SQL 문제를 풀다보니 특정 그룹 별로 최근 값들만 뽑아내야하는 상황에서 자유롭게 답안을 작성하지 못했다.

예를 들면 이 문제.

https://leetcode.com/problems/product-price-at-a-given-date/?envType=study-plan-v2&envId=top-sql-50

 

 

 

8월16일 이전 데이터 중에서 상품번호 별로 가장 최근에 변경된 값을 조회해야했다. 최고값을 구하는 것이 아니라 최근 값이다. change_date 기준으로 1번 상품은 16일에 35로 변경되었었고, 2는 14일에 50으로 변경되었었다. 16일 전까지 변경 이력이 없던 3번 상품은 기본값 10으로 세팅해야하는 상황이었다.

 

도저히 해결 방안이 안보이던 찰나에 sqld를 공부하다가 랭킹을 세우던 함수가 생각이 났다. 상품 아이디로 파티셔닝을 진행하고 change_date를 내림차순으로 정렬한 row_number()에서 1번 rn을 가져오면 될 것 같았다.

 

SELECT
    product_id,
    new_price,
    change_date,
    ROW_NUMBER() OVER(PARTITION BY product_id ORDER BY change_date DESC) as rn
FROM
    Products
WHERE
    change_date <= '2019-08-16';

 

결과값으로 다음과 같이 나왔다.

 

즉 rn이 1번이 가장 최근에 변경된 데이터라는 것이다. 

이를 통해 sql 작성을 이어나갔다.

 

WITH Ranking AS (
SELECT
    product_id,
    new_price,
    change_date,
    ROW_NUMBER() OVER(PARTITION BY product_id ORDER BY change_date DESC) as rn
FROM
    Products
WHERE
    change_date <= '2019-08-16'
), RECENT AS (
    SELECT *
    FROM Ranking
    WHERE rn = 1
), ALL_PRODUCT AS (
    SELECT PRODUCT_ID FROM PRODUCTS GROUP BY PRODUCT_ID
)
SELECT A.PRODUCT_ID product_id, COALESCE(R.new_price, 10) price
FROM ALL_PRODUCT A
LEFT JOIN RECENT R
ON A.PRODUCT_ID = R.PRODUCT_ID;

rn이 alias가 안되서 with을 하나 더 사용했다.