MSSQL 그룹별 최상위 데이터 또는 최신 데이터 하나씩만 가져오는 방법(OVER 사용)

MS-SQL에서 데이터를 가져올 때 그룹 별 최상위 데이터 또는 최신 데이터를 하나씩만 가져와야 하는 경우가 있습니다.

예를 들어 거래처별 판매 리스트에서 각 거래처 별로 가장 최근에 판매한 데이터만 가져오거나 또는 여러 품목에 대한 구매 리스트에서 각 품목별 가장 최근에 구매한 데이터만 하나씩 가져와야 한다고 했을 때 쿼리 문을 어떻게 작성하면 한번에 처리가 가능할지 고민하게 됩니다.

MSSQL의 순위 함수와 OVER 절을 사용하면 간단하게 구현이 가능합니다.

이번 글에서는 MSSQL 그룹별 최상위 데이터 또는 최신 데이터 하나씩만 가져오는 방법(OVER 사용)에 알아보겠습니다.

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

▶ 아래 SQL 문은 Sales 테이블의 데이터를 가져오는 구문입니다.

  • SalesDate가 ‘2024-01-01’ 이후 데이터만 가져오고, 각 고객에 대해 판매 일자가 최신 순으로 정렬해서 가져오고 있습니다.
SELECT Customer, SalesDate , Price 
FROM Sales 
WHERE SalesDate >=‘2024-01-01’
ORDER BY Customer, SalesDate DESC;

▼ 아래 화면은 위 SQL 쿼리 문의 실행 결과 입니다.

  • 실행 결과 화면에서 빨간 화살표로 표시한 데이터가 각 고객 별로 가장 최근에 판매한 데이터입니다.
MSSQL 그룹별 최상위 데이터 또는 최신 데이터 하나씩만 가져오는 방법(OVER 사용) 1


▶ SQL 쿼리 문으로 빨간 화살표로 표시한 데이터만 가져오도록 쿼리 문을 작성해 보겠습니다.
즉, 고객별 최근 판매한 데이터 1건씩만 가져오는 쿼리문입니다.

  • 쿼리 문에서 눈 여겨 볼 부분은 ROW_NUMBER() 순위 함수와 OVER 절의 사용 부분입니다.
  • 행 집합(그룹별)의 순위를 정하고, 그렇게 정해진 행 집합의 순위에서 첫 번째(RankNo = 1)인 행만 가져오도록 구현한 쿼리 문입니다.
SELECT * FROM (
    SELECT 
        Customer, SalesDate, Price,
        ROW_NUMBER() OVER (PARTITION BY Customer ORDER BY SalesDate DESC) AS RankNo
    FROM Sales
) TBL
WHERE RankNo = 1

▼ 아래 화면은 위 쿼리 문의 실행 결과 입니다.

MSSQL 그룹별 최상위 데이터 또는 최신 데이터 하나씩만 가져오는 방법(OVER 사용) 2

MS SQL 순위함수

MSSQL에는 다양한 순위함수를 내장하고 있습니다. 가장 대표적으로 많이 사용하는 몇 가지에 대해 알아보겠습니다.

  • RANK() : RANK 함수는 중복 값들에 대해서 동일 순위를 표시하고, 중복 다음 순위의 값은 중복 개수만큼 떨어진 순위를 표시합니다.
    순위가 1, 2, 2, 4, 5,… 표시되게 됩니다.
  • DENSE_RANK() : DENSE_RANK 함수는 RANK 함수와 동일한 방법으로 순위를 표시하지만 중복 다음 순위도 순차적으로 순위를 표시합니다.
    순위가 1, 2, 2, 3, 4,… 표시되게 됩니다.
  • ROW_NUMBER() : ROW_NUMBER 함수는 중복 값들에 대해서도 순차적으로 순위가 표시됩니다.
    두 번째, 세 번째 값이 동일하더라도 순위가 1, 2, 3, 4, 5,… 표시되게 됩니다.

MSSQL SELECT – OVER 절

OVER 절은 쿼리 결과 집합 내의 창 또는 사용자 지정 행 집합을 정의합니다. 관련 창 함수를 적용하기 전에 행 집합의 분할과 순서를 결정할 때 사용합니다.

▼ OVER 절에 다음 인수를 사용할 수 있습니다.

  • PARTITION BY – 쿼리 결과 집합을 파티션으로 분할합니다.
  • ORDER BY – 결과 집합의 각 파티션 내에서 행의 논리적 순서를 정의합니다.
  • ROWS / RANGE – 파티션 내의 시작점 및 끝점을 지정하여 파티션 내의 행을 제한합니다.
    ORDER BY 인수가 필요하며, ORDER BY 인수가 지정된 경우 기본값은 파티션 시작부터 현재 요소까지 입니다.

OVER 절에 대한 좀 더 상세한 설명은 https://learn.microsoft.com/ko-kr/sql/t-sql/queries/select-over-clause-transact-sql?view=sql-server-2017 MSDN 설명을 참고하시기 바랍니다.

마무리

데이터 테이블에서 그룹별로 최신 데이터 또는 최초 데이터 또는 최상위, 최하위 데이터만 가져오는 로직을 구현해야 할 때 여기에서 설명한 쿼리 문을 활용하면 간단하게 구현이 가능합니다.

각 그룹의 마지막(최신) 데이터를 가져오는 구문은 비교적 자주 사용하는 구문이니 꼭 기억해 주고 활용해 보시기 바랍니다.





♥ 이 글이 도움이 되었기를 바랍니다. ^-^
댓글로 흔적을 남겨 주세요.

답글 남기기