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. 페이징에 적용하여 볼까?
DECLARE @intPageSize INT -- 보여줄갯수
DECLARE @intCurrentPage INT -- 보여줄 페이지 번호
DECLARE @intIDFrom INT
DECLARE @intIDTo INT
SET @intPageSize = 10
SET @intCurrentPage = 2
SET @intIDFrom = (@intPageSize * @intCurrentPage) + 1
SET @intIDTo = ( @intIDFrom + @intPageSize ) -1
SELECT ROWNUM, NAME
FROM ( SELECT ROW_NUMBER()OVER(ORDER BY VENDORID) AS ROWNUM, VENDORID, NAME
FROM Purchasing.Vendor ) AS D
WHERE ROWNUM BETWEEN @intIDFrom AND @intIDTo
ORDER BY VENDORID
페이징에 대해선 이전에 썼던 포스트를 참조 하자!
3. 오라클의 ROW_NUMBER()도 똑같다.
SELECT DEPTNO, ROW_NUMBER() OVER(PARTITION BY DEPTNO ORDER BY EMPNO) RID, EMPNO FROM EMP
4. 오라클에서 ROWNUM를 이용하여 페이징 기능 구현
SELECT *
FROM (
SELECT ROWNUM AS INX, T.* FROM (
SELECT EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO
FROM EMP
ORDER BY EMPNO ) T
) T
WHERE INX BETWEEN 1 AND 10;
ROWNUM은 ORDER BY EMPNO가 실행되기 전에 순위값이 지정되므로 정렬 후 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
전체 187건 ORDERDATE가 2004-07-26인것은 32건 이다.
187/20 = 9.35
전체 20그룹을 각 9건씩 채우면 180건이고 7건이 남는다. (20 * 9 = 180)
남은 7건은 1그룹부터 순차적으로 채워진다.
1~7그룹 : 10건 (7*10 = 70)
8~20그룹 : 9건 (13 * 9 = 117)
PARTITION BY 부분도 위에서 설명한 내용과 같다.~
2. 이것역쉬 오라클에서도 같네?
SELECT
EMPNO,
MGR,
NTILE(5) OVER(ORDER BY EMPNO) AS GRPNO,
NTILE(2) OVER(PARTITION BY MGR ORDER BY EMPNO) AS SUB_GRPNO
FROM EMP
ORDER BY GRPNO, SUB_GRPNO
