MSSQL 그룹별 최상위 데이터 가져오기

그룹별 최상위 데이터 가져오기

MSSQL에서 그룹별 최상위 데이터를 가져오는 방법은 JOIN을 이용한 방법, 서브쿼리를 이용한 방법 등 여러가지를 생각해볼 수 있겠습니다만, 여기서는 제가 가장 간단하고 이해하기 쉽다고 생각하는 순위함수와 OVER절을 이용한 방법에 대해 설명하겠습니다.

학습에 이용할 테이블과 데이터는 다음과 같습니다. 

SELECT Name, Month, SellCount FROM MonthlySales

• 테이블 전체 데이터 조회결과

• 최종목표/결과쿼리

먼저 최종적인 쿼리를 보여드리고 단계적으로 설명하도록 하겠습니다. 위의 테이블 데이터에서 각 과일명 별로 판매량이 제일 많은 달의 로우만 가져오는 쿼리입니다.

SELECT * FROM (
    SELECT 
        Name
        , Month
        , SellCount
        , ROW_NUMBER() OVER (PARTITION BY Name ORDER BY SellCount DESC) AS RankNo
    FROM MonthlySales
) T
WHERE RankNo = 1

• 쿼리결과

이제, 본격적인 설명을 들어가면서 우리가 가장 먼저 알아야 할 개념은 OVER절 입니다. MSDN에서는 OVER절에 대해 다음과 같이 설명하고 있습니다.(보러가기)

"OVER 절은 쿼리 결과 집합 내의 창 또는 사용자 지정 행 집합을 정의합니다. 그런 다음 창 함수가 창의 각 행에 대한 값을 계산합니다. OVER 절에 함수를 사용하여 이동 평균, 누적 집계, 누계 또는 그룹 결과당 상위 N개 결과 등의 집계된 값을 계산할 수 있습니다."

즉, OVER절을 사용하여 결과집합을 정의하고 ROW_NUMBER(), RANK()등 함수를 사용하여 각 행에 대한 값을 계산하라고 되어있습니다.

자, 그럼 단계별로 나누어 설명하도록 하겠습니다.

 

1. OVER절 이해하기

SELECT
    순위함수 OVER (PARTITION BY Name ORDER BY SellCount DESC) AS RankNo --테이블 내의 데이터를 과일이름 별로 나누어 판매갯수로 역순으로 정렬
FROM MonthlySales

순위매기기에 필요한 것은 우선 OVER절입니다. ROW_NUMBER(), RANK() 등 함수를 먼저 생각하기가 쉽지만 사실 OVER절을 사용하여 어떻게 집합을 정의할지 결정해야 합니다. 여기서는 OVER절을 사용해 테이블 내의 데이터를 과일이름 별로 나누어 판매갯수로 역순으로 정렬합니다. 

2. 순위함수의 사용

OVER절에서 사용할 수 있는 순위함수는 ROW_NUMBER(), RANK(), DENSE_RANK(), NTILE() 등이 있습니다. 각 함수를 사용했을때 결과를 보시면 쉽게 이해할 수 있습니다.

• ROW_NUMBER()

SELECT
    Name
    , Month
    , SellCount
    , ROW_NUMBER() OVER (ORDER BY SellCount DESC) AS RankNo --각 로우에 일련번호가 매겨집니다.
FROM MonthlySales

• RANK()

SELECT
    Name
    , Month
    , SellCount
    , RANK() OVER (ORDER BY SellCount DESC) AS RankNo -- 순위가 같은 6등이 2개 있고, 그 다음은 8등이 매겨집니다.
FROM MonthlySales

• DENSE_RANK()

SELECT
    Name
    , Month
    , SellCount
    , DENSE_RANK() OVER (ORDER BY SellCount DESC) AS RankNo -- 순위가 같은 6등이 2개 있고, 그 다음은 7등이 매겨집니다.
FROM MonthlySales

• NTILE()

SELECT
    Name
    , Month
    , SellCount
    , NTILE(4) OVER (ORDER BY SellCount DESC) AS RankNo -- 로우를 n(여기서는 4)개의 그룹으로 나누어 번호를 매기고 앞 그룹부터 번호를 추가합니다.  
FROM MonthlySales

프로젝트의 성공을 기원합니다.