메타코드의 SQL 부트캠프 강의 정리 무려 10탄째다.
강의는 메타코드에서 구매 가능함 https://mcode.co.kr/kor/
이 글에서는 WINDOW 함수 중 많이들 헷갈려 하는 순위함수를 간단히 다뤄 보려고 한다. 사실은 8편에서 간략하게 다룬 내용이지만, 그래도 예시와 함께 좀 더 자세히 다루면 좋지 않을까? 나중을 위해...
🔥 ROW_NUMBER, RANK, DENSE_RANK
RANK, DENSE_RANK, ROW_NUMBER 모두 순위를 알고 싶을 때 사용하는 함수이다. 차이점만 확실히 알면 쉽게 구분할 수 있다.
SELECT customername, creditlimit,
ROW_NUMBER() OVER (ORDER BY creditlimit DESC) AS row_number_,
RANK() OVER (ORDER BY creditlimit DESC) AS rank_,
DENSE_RANK() OVER (ORDER BY creditlimit DESC) AS dense_rank_
FROM customers
ORDER BY creditlimit DESC
이 코드는, 커스터머 테이블에서 creditlimit의 내림차순으로 ROW_NUMBER, RANK, DENSE_RANK를 구하라는 의미이다. 결과를 살펴보자.
먼저, creditlimit 의 내림차순으로 정렬이니 creditlimit 컬럼을 살펴보자.
보다보면 숫자가 작은 것에서 큰 순서대로 나온다. 그러면 1위, 2위, 3위, 이렇게 순차적으로 순위를 매기면 되니 어렵지 않다... 그러나... 20번째 행부터 이야기가 좀 달라진다.
105,000.0이라는 같은 숫자가 2번 연속 나오는데 이 경우엔 어떻게 처리해야 할까?
📌 ROW_NUMBER
먼저, ROW_NUMBER는 동일한 값이 있든 말든 상관 없이 그냥 순위를 매긴다. 말 그대로 row 순서대로 숫자를 붙였을 뿐이라 생각하면 이해하기 편하다. 그래서 2번 연속으로 나오는 105,000에 대해 20위, 21위로 나타내고 있다. 그다음 104,600에 대해서는 22위를 부여한다.
📌 RANK
RANK, DENSE RANK는 동순위를 고려해서 순위를 매긴다. 따라서 20행의 105,000과 21행의 105,000은 모두 20위, 20위로 책정되고 동순위를 매긴 만큼 순위를 건너뛰어 22행의 104,600의 경우 22위가 된다.
📌 DENSE_RANK
동순위를 매긴 후 다음 숫자를 건너 뛰지 않고 순위를 매기고 싶다면, DENSE_RANK를 사용하면 된다. DENSE_RANK는 RANK와 동일하게 20행과 21행의 105,000에 각각 20위라는 동일한 순위를 부여하고, 그다음 행(22행)인 104,600의 순위는 21위가 된다.
🔥 FIRST_VALUE, LAST_VALUE
처음과 끝 값을 출력하는 FIST_VALUE, LAST_VALUE를 알아보자. 이름만 들으면 그냥 단순하고 쉬운 코드일 것 같지만 조금 까다로운 부분이 있다. 예제로 살펴보면...
위 예제의 답을 구하기 위해 아래와 같은 코드를 쓴다면 틀린 코드를 적은 것이다.
#문제 23: "products" 테이블에서 각 제품 라인별로 가장 비싼 제품의 이름과 가장 싼 제품의 이름을 조회하세요.
-- 오답: last value가 누적됨;
SELECT productline, productName, buyPrice,
FIRST_VALUE(productName) OVER(PARTITION BY productline ORDER BY buyPrice ASC) AS cheap_product,
LAST_VALUE(productName) OVER(PARTITION BY productline ORDER BY buyPrice ASC) AS expensive_product
FROM products
ORDER BY buyPrice desc;
오답: LAST VALUE가 누적되어 나타남. 하나여야 함
다음으로 정답 코드를 살펴보면, 바르게 나오는 것을 알 수 있다.
-- 정답1. orderby 오름차순이 젤 싼거 내림차순이 젤 비싼거!
SELECT productline, productName, buyPrice,
FIRST_VALUE(productName) OVER(PARTITION BY productline ORDER BY buyPrice ASC) AS cheap_product,
first_VALUE(productName) OVER(PARTITION BY productline ORDER BY buyPrice DESC) AS expensive_product
FROM products
ORDER BY buyPrice desc;
굳이 복잡하지만 오름차순 상태에서 코드를 쓰고 싶다면 이렇게 쓸 수도 있다.
-- 정답2. 굳이 last value를 쓰려면 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING 넣어줘야함
-- 위로 전부, 아래로 전부를 between으로 구간을 다시 설정하고 요 구간에서 마지막 값을 가져오라고 하는 명령어
SELECT productline, productName, buyPrice,
FIRST_VALUE(productName) OVER(PARTITION BY productline ORDER BY buyPrice ASC) AS cheap_product,
LAST_VALUE(productName) OVER(PARTITION BY productline ORDER BY buyPrice ASC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS expensive_product
FROM products
ORDER BY buyPrice DESC;
사실 BETWEEN UNBOUNDED PRECENDING AND UNBOUNDED FOLLOWING 부분은 아직 블로그에 소개하지 않은 내용이다. (다음 글에서 소개할 예정)
