SQL로 쿼리를 짤 땐 대부분 '최대', '최소'인 값을 구하는 경우가 많다.
예를 들면 '최다 구매 고객을 추출하라' 거나, '최초의 구매 일자를 추출하라' 같은 것들이다.
그런데 가끔 이런 최대, 최소 값이 아닌 2번째로 큰 값/2번째로 작은 값 등, N번째의 값을 구해야 할 때가 있다.
이런 경우에는 윈도우 함수 중 순위 함수(RANK)를 활용하면 N번째 값을 구할 수 있다.
(윈도우 함수가 무엇인지 모르겠다면 아래 포스팅을 참고하세요!)
SQL 윈도우 함수 예제 - 순위함수(RANK), 누적 집계함수
오늘은 SQL에서 자주 사용하는 윈도우 함수(Window function)에 대해 포스팅합니다. 윈도우 함수란 행과 행 간의 관계를 정의하며, 윈도우 함수에는 순위함수와 누적 집계함수 2가지가 있습니다. 그
suy379.tistory.com
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번째 값은 무엇이든지 뽑을 수 있습니다. 도움이 되었길 바랍니다! :)
'Skillset > SQL' 카테고리의 다른 글
| [MySQL] 가장 짧은 글자와 가장 긴 글자에 해당하는 도시 구하기 (LENGTH) (0) | 2022.08.07 |
|---|---|
| [MySQL] RECURSIVE 구문 활용해 배열 생성하기 예제 (feat. 빅쿼리 UNNEST) (0) | 2022.06.23 |
| [MS-SQL] 항목별 비율값 집계하기 (CAST, ROUND, CASE WHEN 구문) (0) | 2022.06.13 |
| SQL CASE WHEN 구문 사용법 - 예제로 알아보기 (0) | 2022.06.07 |
| SQL 효율화 및 자동화 명령어 - VIEW & PROCEDURE (2) | 2022.06.04 |
