스크립트 변환으로 RowNumber 구현하기

스크립트 변환으로 RowNumber 구현하기 

한대성

 

 

 

질문 원본: http://www.sqlleader.com/mboard.asp?exec=view&strBoardID=SS2005QNA&intSeq=1223

SSIS 에서, 1. 컬럼에 랭크(값이 높은 순 혹은 낮은 순, 가나다라 순, ABCD순 등)를 주는 방법이 있습니까? 2. 정렬된 값에 시퀀스를 주는 방법이 있을까요? ->(이런 방법으로 랭크를......^^)   SQL Server 2005 에서, 3. 오라클에서는 Rownum 이라는 것이 있잖아요. SQL Server 에서 같거나 유사한 것이 있습니까?

다음 링크에 비슷한 예제가 있긴 하지만, 제차 소개하겠습니다.

 

다음과 같은 정렬된 데이터에 대해 시퀀스를 붙이는 경우를 예로 들겠습니다.

(데이터 원본의 쿼리에서 정렬을 하거나 정렬 변환을 이용해서 원하는 형태로 정렬을 수행해도 됩니다.)

스크립트 구성 요소-변환 을 추가한 후에 원본과 연결합니다.

스크립트 변환 편집기의 입/출력 탭에서 출력0을 확장한 후, 출력 열 부분에 열을 하나 추가합니다. 본 예제에서는 OrderSeq라는 4바이트 정수(DT_I4)형으로 추가하였습니다.

 

스크립트 탭에서 스크립트 디자인(S)를 클릭하여 VSA(Visual Studio for Applications)을 연 후, 다음과 같이 스크립트를 입력합니다. (굵은 글씨가 추가한 스크립트 입니다.)

Imports System Imports System.Data Imports System.Math Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper Imports Microsoft.SqlServer.Dts.Runtime.Wrapper   Public Class ScriptMain     Inherits UserComponent       Dim OrdSeq As Integer     Public Overrides Sub 입력_ProcessInputRow(ByVal Row As 입력Buffer)         Row.OrderSeq = OrdSeq         OrdSeq = OrdSeq + 1     End Sub       Public Sub New()         OrdSeq = 1     End Sub End Class

 

VSA를 닫고, 임의의 데이터 대상을 연결시킨 후, 데이터 뷰어를 통해 처리되는 결과를 확인해 보면 순번 열이 추가된 것을 확인하실 수 있습니다.

스크립트를 응용하면 누적 합 또는 n개 행 평균 등과 같은 다양한 연산들을 구현할 수 있습니다.

 

만약, 스크립트로 구현하기 싫다면 다음 링크에 있는 변환을 다운 받아서 사용하면 됩니다. 하지만, 제 개인적인 취향(^^)은 변환을 추가 설치하는 것을 좋아하지는 않습니다. 만약, 회사 프로젝트 단위로 중요한 기능인 경우에는 사용해야 하겠지만, 이와 같이 단순한 기능인 경우에 이용하려고 한다면 패키지를 실행하거나 편집할 서버 또는 PC에 해당 컴포넌트를 일일이 설치하고 등록해야 하며, MS 공식적인 컴포넌트가 아닌 경우에 안정성을 의심해야 하기 때문입니다.^^

참고 : http://www.sqlis.com/93.aspx

 

 

 

 

두 번째 질문으로, 오라클의 RowNum과 같은 기능이 MSSQL 2005에도 있습니다.

예제 쿼리 입니다. 온라인 도움말에서 RANK(), DENSE_RANK(), ROW_NUMBER(), NTITLE()을 참고하시기 바랍니다.

SELECT        RANK() OVER(ORDER BY ManagerID) AS RANK,        RANK() OVER(PARTITION BY ManagerID ORDER BY Title) AS PART_RANK,        DENSE_RANK() OVER(ORDER BY Title) AS DENSE_RANK,        ROW_NUMBER() OVER(ORDER BY Title) AS ROW_NUM,        NTILE(4) OVER(ORDER BY Title) AS NTILE_4,        NationalIDNumber, Title, ManagerID FROM HumanResources.Employee ORDER BY Title, ManagerID

 

 

참고하시기 바랍니다.

 

본 게시판에 실린 글은 누구나 복사하셔서 이용하셔도 되지만, 반드시 출처(SQLLeader.com) 및 링크를 밝혀주셔야 합니다.