<순위 함수>
- 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;
'oracle' 카테고리의 다른 글
1020_서브쿼리 (0) | 2022.10.26 |
---|---|
Foreign Key(외래키) (0) | 2022.10.25 |
1021_제약조건(not null, unique, primary key) (0) | 2022.10.25 |
1021_인덱스 (0) | 2022.10.25 |
TCL (Transaction Control Language, 트랜잭션 제어 언어) (0) | 2022.10.25 |