순위함수(ROW_NUMBER, DENSE_RANK,RANK)

<순위 함수>

- row_number : 1,2,3,4,... 번호를 부여

 

<형식>

select row_number()

over (partition by 컬럼명, order by컬럼명, .. as 별칭

from 테이블 명

(partition by 컬럼명은 생략가능 (=group by)

(order by 생략 불가)

 

1) partition by 생략 

2) partition by 포함

 

select row_number() over(partition by CustomerAddr order by CustomerAge desc) as you,
CustomerName, CustomerId, CustomerAddr, CustomerAge from Customer;

 

- dense_rank()

: 비교값이 동일할 때 같은 번호를 부여 

1, 2, 2, 3 , 4 식으로 표현

select dense_rank() over(order by CustomerAge) as age,
    -> CustomerName, CustomerId, CustomerAddr, CustomerAge from Customer;

 

 

- rank()

: 표현을 1,2,2,4,5,6 

같은 값이 있으면 동일한 숫자로 표현하되 다음 숫자는 건너뛴다

 

select rank() over(order by CustomerAge) as age, 
CustomerName, CustomerId, CustomerAddr, CustomerAge from Customer;

 

 

-  ntile()

: 원하는 수만큼 그룹화

select ntile(2) over(order by CustomerAge) as age, 
CustomerName, CustomerId, CustomerAddr, CustomerAge from Customer;