[oracle]RANK 관련 예제

RANK 관련 예제

테이블 생성

조직별 회원별 판매실적 테이블 생성

CREATE TABLE SALE( 
	ITEM_CD VARCHAR2(10) , 
	MEMBER_ID NUMBER(10),
	SAL NUMBER(20)
);

데이터준비

INSERT ALL
INTO SALE(ITEM_CD,MEMBER_ID,SAL)VALUES('I001',1,10000)
INTO SALE(ITEM_CD,MEMBER_ID,SAL)VALUES('I002',1,20000)
INTO SALE(ITEM_CD,MEMBER_ID,SAL)VALUES('I003',4,40000)
INTO SALE(ITEM_CD,MEMBER_ID,SAL)VALUES('I004',1,560000)
INTO SALE(ITEM_CD,MEMBER_ID,SAL)VALUES('I005',2,30000)
INTO SALE(ITEM_CD,MEMBER_ID,SAL)VALUES('I001',5,70000)
INTO SALE(ITEM_CD,MEMBER_ID,SAL)VALUES('I002',4,870000)
INTO SALE(ITEM_CD,MEMBER_ID,SAL)VALUES('I003',2,330000)
INTO SALE(ITEM_CD,MEMBER_ID,SAL)VALUES('I004',2,260000)
INTO SALE(ITEM_CD,MEMBER_ID,SAL)VALUES('I005',1,110000)
INTO SALE(ITEM_CD,MEMBER_ID,SAL)VALUES('I001',5,60000)
INTO SALE(ITEM_CD,MEMBER_ID,SAL)VALUES('I002',3,770000)
INTO SALE(ITEM_CD,MEMBER_ID,SAL)VALUES('I003',3,1700000)
INTO SALE(ITEM_CD,MEMBER_ID,SAL)VALUES('I004',1,320000)
INTO SALE(ITEM_CD,MEMBER_ID,SAL)VALUES('I005',2,220000)
INTO SALE(ITEM_CD,MEMBER_ID,SAL)VALUES('I001',1,890000)
INTO SALE(ITEM_CD,MEMBER_ID,SAL)VALUES('I002',1,750000)
INTO SALE(ITEM_CD,MEMBER_ID,SAL)VALUES('I003',1,740000)
SELECT 1 FROM DUAL;

RANK() 는 동일순위를 동일건수로 취급
DENSE_RANK() 동일순위를 하나의 건수로 취급
ROW_NUMBER() 는 동일 순위라고 하더라도 고유번호를 부여한다.

단순 순위 예

SELECT ITEM_CD,MEMBER_ID,SAL, RANK() OVER(ORDER BY SAL DESC)
FROM SALE;

회원의 전체 판매량 순위
ITEM_CD MEMBER_ID SAL RANK()OVER(ORDERBYSALDESC)
1 I003 3 1,700,000 1
2 I001 1 890,000 2
3 I002 4 870,000 3
4 I002 3 770,000 4
5 I002 1 750,000 5
6 I003 1 740,000 6
7 I004 1 560,000 7
8 I003 2 330,000 8
9 I004 1 320,000 9
10 I004 2 260,000 10
11 I005 2 220,000 11
12 I005 1 110,000 12
13 I001 5 70,000 13
14 I001 5 60,000 14
15 I003 4 40,000 15
16 I005 2 30,000 16
17 I002 1 20,000 17
18 I001 1 10,000 18

아이템 종류별 회원의 판매량 순위

SELECT ITEM_CD,MEMBER_ID,SAL, RANK() OVER( PARTITION BY ITEM_CD ORDER BY SAL DESC)
FROM SALE;

ITEM_CD MEMBER_ID SAL RANK()OVER(PARTITIONBYITEM_CDORDERBYSALDESC)
1 I001 1 890,000 1
2 I001 5 70,000 2
3 I001 5 60,000 3
4 I001 1 10,000 4
5 I002 4 870,000 1
6 I002 3 770,000 2
7 I002 1 750,000 3
8 I002 1 20,000 4
9 I003 3 1,700,000 1
10 I003 1 740,000 2
11 I003 2 330,000 3
12 I003 4 40,000 4
13 I004 1 560,000 1
14 I004 1 320,000 2
15 I004 2 260,000 3
16 I005 2 220,000 1
17 I005 1 110,000 2
18 I005 2 30,000 3