컴퓨토피아

MS-SQL 2005에서는 순위를 쉽게 구할수 있도록 몇개의 함수를 추가로 제공한다.

이미 Oracle에서는 제공되는 함수들 이므로 간략하게 비교해 볼까 한다~

DB는 ADVENTUREWORKS의 테이블을 이용할 것이다.

USE ADVENTUREWORKS
GO

를 꼭 하자~

☞ 구문

<FUNCTION_NAME>() OVER(

      [PARTITION BY <PARTITION_BY_LIST>] 

      ORDER BY <ORDER_BY_LIST> )

EX) ROW_NUMBER() OVER( PARTITION BY COL3 ORDER BY COL1 )

1. OVER ( ORDER BY <ORDER_BY_LIST> )는 순위를 결정하는 정렬 조건으로 필수이다.

2. PARTITION BY를 지정하지 않으면 순위값은 모든 테이블 행을 대상으로 순위가 계산되지만

지정하면 행 그룹내에서 개별적으로 계산될 수 있다.

☞ 순위 함수

◑ ROW_NUMBER

결과행에 순차적으로 값을 지정할 수 있다. (페이징할때 유용하다.. 이젠 고생할 필요없다 ㅋㅋㅋ)

1. PARTITION BY를 사용할때와 하지 않을때의 차이 비교

SELECT
     CONVERT(VARCHAR(10), ORDERDATE, 121) AS ORDERDATE,
     ROW_NUMBER()OVER (ORDER BY ORDERDATE DESC) AS ROWNUM,
     ROW_NUMBER()OVER (PARTITION BY CONVERT(VARCHAR(10), ORDERDATE, 121) ORDER BY ORDERDATE DESC) AS SUBROWNUM,
     CUSTOMERID,
     TOTALDUE
FROM SALES.SALESORDERHEADER
WHERE TOTALDUE > 100
ORDER BY ROWNUM, SUBROWNUM

빨간 박스의 ROWNUM과 SUBROWNUM을 비교하여 보자.

SUBROWNUM은 ORDERDATE내에서 다시 1부터 순차적으로 값이 지정되는걸 확인 할 수 있다.

2. 페이징에 적용하여 볼까?

4. 오라클에서 ROWNUM를 이용하여 페이징 기능 구현

◑ RANK 와 DENSE_RANK

순차적으로 값을 제공하는점에서는 ROW_NUMBER과 유사하지만 동률이 적용된다는점에서는 다르다.

1. ROW_NUMBER, RANK, DENSE_RANK 비교

SELECT
    CONVERT(VARCHAR(10), ORDERDATE, 121) AS ORDERDATE,
    ROW_NUMBER()OVER (ORDER BY TOTALDUE DESC) AS ROWNUM,
    RANK() OVER (ORDER BY TOTALDUE DESC)AS RANK,

    DENSE_RANK()OVER (ORDER BY TOTALDUE DESC) AS DENSE_RANK,
    DENSE_RANK()OVER (PARTITION BY CONVERT(VARCHAR(10), ORDERDATE, 121) ORDER BY TOTALDUE DESC) AS SUB_DENSE_RANK,

    TOTALDUE
FROM SALES.SALESORDERHEADER
WHERE TOTALDUE > 100
ORDER BY DENSE_RANK, SUB_DENSE_RANK

결과의 1694와 1695 ROW는 TOTALDUE가 같으므로 동률이다.

ROWNUM은 무조건 순차적으로 값을 제공하지만 RANK와 DENSE_RANK는 동률이 적용된다.

1696 ROW를 보면 RANK는 1694순위가 2개였으므로 다음순위는 1696순위가 되지만

DENSE_RANK는 1694순위가 2개였더라도 다음 순위는 1695가 된다.

PARTITION BY는 날짜별로 순위를 제공한다.

2. 오라클에서도 똑같다

SELECT
    MGR,
    ROW_NUMBER() OVER(ORDER BY SAL DESC) AS INX,
    RANK() OVER(ORDER BY SAL DESC) AS RANK,
    DENSE_RANK()OVER(ORDER BY SAL DESC) AS DENSE_RANK,
    DENSE_RANK()OVER(PARTITION BY MGR ORDER BY SAL DESC) AS SUB_DENSE_RANK,
    SAL
FROM EMP

◑ NTILE

데이터를 지정된 그룹수로 분리할 수 있다.

SELECT
    CONVERT(VARCHAR(10), ORDERDATE, 121) AS ORDERDATE,    
    NTILE(20) OVER(ORDER BY ORDERDATE) AS GRPNO,
    NTILE(5) OVER(PARTITION BY CONVERT(VARCHAR(10), ORDERDATE, 121) ORDER BY ORDERDATE) AS SUB_GRPNO,
    TOTALDUE
FROM SALES.SALESORDERHEADER
WHERE MODIFIEDDATE > CONVERT(DATETIME, '2004-08-01', 121)
ORDER BY ORDERDATE, GRPNO, SUB_GRPNO