SQL N번째 순위 추출하기(2번째로 큰 값, 상위 N개, 하위 N개 구하기)

SQL로 쿼리를 짤 땐 대부분 '최대', '최소'인 값을 구하는 경우가 많다.

예를 들면 '최다 구매 고객을 추출하라' 거나, '최초의 구매 일자를 추출하라' 같은 것들이다.

 

그런데 가끔 이런 최대, 최소 값이 아닌 2번째로 큰 값/2번째로 작은 값 등, N번째의 값을 구해야 할 때가 있다. 

이런 경우에는 윈도우 함수 중 순위 함수(RANK)를 활용하면 N번째 값을 구할 수 있다.

(윈도우 함수가 무엇인지 모르겠다면 아래 포스팅을 참고하세요!)

SQL 윈도우 함수 예제 - 순위함수(RANK), 누적 집계함수

 

SELECT * FROM #PRODUCT_GROWTH_BASE

 

오늘 활용할 가상의 테이블 [#PRODUCT_GROWTH_BASE] 이다.

이 테이블엔 자동차 브랜드(brand), 모델명(model)과 함께, 2020년도의 매출(amt_20), 2021년도의 매출(amt_21) 이 나와 있다.

먼저 성장률을 구하고, 성장률 값의 N번째 순위를 뽑아보자.

 

Q. 브랜드 및 모델별 (전년 대비) 성장률을 구하여라.

 

이는 어렵지 않게 구할 수 있다. 브랜드,모델명을 GROUP BY로 걸어주고, 21년의 매출(amt_21)을 20년의 매출(amt_20)로 나눈 후 1을 빼는 식을 사용하였다.

 

SELECT brand, model, (SUM(amt_21)/SUM(amt_20))-1 AS growth
FROM #PRODUCT_GROWTH_BASE 
GROUP BY brand , model

 

위와 같이 성장률(growth) 값이 구해졌다.

이렇게 구해진 테이블을 밑바탕으로 하여 (=즉, 서브 쿼리로 하여) 성장률을 필터링하자!

이 때 서브쿼리는 FROM절 서브쿼리를 사용한다.

 

✔Table of Contents

 

Q1. 각 브랜드별 성장률의 순위를 구하여라.

앞서 구해진 쿼리를 서브쿼리로 하여, 순위 열을 추가해봅시다.

순위는 윈도우 함수인 RANK()를 사용하며, '브랜드'로 나눌 것이므로 PARTITON BY를 사용하면 됩니다.

 

SELECT sub.*, 
	RANK() OVER (PARTITION BY sub.brand ORDER BY sub.growth desc) AS rnk
FROM (SELECT brand, model, (SUM(amt_21)/SUM(amt_20))-1 AS growth
	FROM #PRODUCT_GROWTH_BASE 
	GROUP BY brand , model ) sub

 

앞서 구한 쿼리를 FROM절 서브쿼리 'sub' 로 만들었습니다.

브랜드별로 성장률(growth)이 큰 것부터 차례대로 순위가 매겨졌습니다 (rnk 열).

 

---

Q2. 각 브랜드별 성장률 TOP 2 모델만 추출하시오.

앞서 구한 서브쿼리를 다시 서브쿼리로 만들어 필터링해줍니다.

즉, 순위 열(rnk)가 2보다 작거나 같은 값(순위가 1,2)만 필터링하면 TOP 2 모델로 볼 수 있겠죠?

 

SELECT sub2.*
FROM (SELECT sub.*, 
		RANK() OVER (PARTITION BY sub.brand ORDER BY sub.growth desc) AS rnk
	FROM (SELECT brand, model, (SUM(amt_21)/SUM(amt_20))-1 AS growth
			FROM #PRODUCT_GROWTH_BASE 
			GROUP BY brand , model ) sub ) sub2
WHERE sub2.rnk <= 2

 

Q1에서 만든 서브쿼리를 'sub2'로 하여, sub2의 rnk가 2보다 작거나 같은 것만 뽑으면

각 브랜드별 Top 2 모델만 뽑히게 됩니다.

 

---

Q3. 각 브랜드별 성장률이 3번째로 큰 모델만 추출하시오.

이것도 앞의 Q2 문제와 비슷하게, 서브쿼리 sub2에서 순위(rnk)가 3인 것만 추출하면 되겠죠?

 

SELECT sub2.*
FROM (SELECT sub.*, 
		RANK() OVER (PARTITION BY sub.brand ORDER BY sub.growth desc) AS rnk
	FROM (SELECT brand, model, (SUM(amt_21)/SUM(amt_20))-1 AS growth
		FROM #PRODUCT_GROWTH_BASE 
		GROUP BY brand , model ) sub ) sub2
WHERE sub2.rnk = 3

 

---

Q4. 각 브랜드별 성장률이 가장 낮은 2개 모델만 추출하시오.

하위 2개라면 성장률을 작은 것부터 정렬(ASC 정렬)하여 순위를 매긴 후, 순위가 1,2위인 것만 필터링하면 됩니다.

즉, 앞의 Q2 문제에서 썼던 쿼리와 똑같은데, RANK 함수로 growth(성장률)의 순위를 매길 때 성장률이 작은 값부터 1,2,3,.. 순위로 설정하면 됩니다.

 

SELECT sub2.*
FROM (SELECT sub.*, 
		RANK() OVER (PARTITION BY sub.brand ORDER BY sub.growth ASC) AS rnk
	FROM (SELECT brand, model, (SUM(amt_21)/SUM(amt_20))-1 AS growth
		FROM #PRODUCT_GROWTH_BASE 
		GROUP BY brand , model ) sub ) sub2
WHERE sub2.rnk <= 2

 

이렇게 이중 서브쿼리와 윈도우 순위 함수를 활용하면,

SQL 상에서 원하는 N번째 값은 무엇이든지 뽑을 수 있습니다. 도움이 되었길 바랍니다! :)