[MSSQL] RANK, DENSE_RANK, ROW_NUMBER, PARTITION BY 차이

 

MS-SQL 에서 자주 사용되는 함수 중 순위 함수에 대해서 예제를 통해 좀 더 명확한 차이를 확인하도록 하자.

 

TEST TABLE CREATE

--DROP TABLE CUSTOMER
CREATE TABLE CUSTOMER (
 CUST_ID VARCHAR(10) NOT NULL PRIMARY KEY,
 CUST_NM VARCHAR(10)
 ) 

--DROP TABLE MONTH_REPORT
CREATE TABLE MONTH_REPORT (
 MONTH_DATE VARCHAR(10) ,
 CUST_ID VARCHAR(10) ,
 REPOT_DATA VARCHAR(10),
 CONSTRAINT PK_MONTH_REPORT PRIMARY KEY CLUSTERED ( MONTH_DATE, CUST_ID )
 )

 

TEST DATA CREATE

INSERT INTO CUSTOMER VALUES ( '001', '홍길동' )
INSERT INTO CUSTOMER VALUES ( '002', '이순신' )
INSERT INTO CUSTOMER VALUES ( '003', '강감찬' )
INSERT INTO CUSTOMER VALUES ( '004', '이상화' )
INSERT INTO CUSTOMER VALUES ( '005', '이규혁' )

INSERT INTO MONTH_REPORT VALUES ('201301', '001', '200')
INSERT INTO MONTH_REPORT VALUES ('201301', '002', '300')
INSERT INTO MONTH_REPORT VALUES ('201301', '003', '250')
INSERT INTO MONTH_REPORT VALUES ('201301', '004', '300')
INSERT INTO MONTH_REPORT VALUES ('201301', '005', '250')
INSERT INTO MONTH_REPORT VALUES ('201302', '001', '150')
INSERT INTO MONTH_REPORT VALUES ('201302', '002', '150')
INSERT INTO MONTH_REPORT VALUES ('201302', '004', '200')
INSERT INTO MONTH_REPORT VALUES ('201302', '005', '100')
INSERT INTO MONTH_REPORT VALUES ('201303', '002', '100')
INSERT INTO MONTH_REPORT VALUES ('201303', '003', '100')
INSERT INTO MONTH_REPORT VALUES ('201303', '004', '200')
INSERT INTO MONTH_REPORT VALUES ('201303', '005', '350')

 

ROW_NUMBER() 

• 각 row 별 순서가 표시

SELECT *, ROW_NUMBER() OVER(ORDER BY REPOT_DATA) AS RNK
  FROM MONTH_REPORT
ORDER BY REPOT_DATA, RNK

RNK 항목에 순서표기

 

DENSE_RANK()

• 지정한 항목의 카운트에 상관없이 동번 이후 다음 순서가 표시

SELECT *, DENSE_RANK() OVER(ORDER BY REPOT_DATA) AS RNK
  FROM MONTH_REPORT
ORDER BY REPOT_DATA, RNK

REPOT_DATA별 RNK 항목에 순서표기

 

RANK()

• 지정한 항목의 카운트를 제외하고 이후 다음 순서가 표시

SELECT *, RANK() OVER(ORDER BY REPOT_DATA) AS RNK
  FROM MONTH_REPORT
ORDER BY REPOT_DATA, RNK

REPOT_DATA 별 RNK 항목에 순서표기

 

PARTITION BY

• 설정한 PARTITION 에 따라 순위 함수 처리 가능

SELECT *, 
 DENSE_RANK()OVER(PARTITION BY MONTH_DATE ORDER BY REPOT_DATA) AS DEN_RNK, 
 RANK() OVER(PARTITION BY MONTH_DATE ORDER BY REPOT_DATA) AS RNK,
 ROW_NUMBER() OVER(PARTITION BY MONTH_DATE ORDER BY REPOT_DATA) AS ROW_NUM
  FROM MONTH_REPORT
ORDER BY MONTH_DATE, REPOT_DATA, RNK

MONTH_DATE 기준의 REPOT_DATA 별 순서