SQL 데이터 분석함수 - 순위출력 Rank () over ( partition by /order by ~ desc/asc), Dense_Rank ()

데이터 분석함수

→ 그룹함수 가 아닌 별도의 함수 이다.

RANK (순위)

(RANK () OVER (partition by               order by                   desc or  asc )

→ OVER = 확장하다.

→즉 OVER 다음에 나오는 괄호안의 내용으로 확장해서 RANK를 출력하겠다는 뜻.

예제 : 이름과 월급을 출력하고 월급이 높은 순으로 순위를 출력 

select ename, sal, rank() over (order by sal desc) as 순위
from emp;

→ 설명 () 의 내용? 괄호 안의 내용을 확장해서 순위를 출력해라.

 

예제 : 직업이 애널리스트, 매니저 인 사원들의 이름 직업 월급과 월급에 대한 순위를 출력

select ename, job, sal, rank() over (order by sal desc) as 순위
from emp 
where job in ('ANALYST', 'MANAGER');

→ 일등이 두명이라서 2 등이 안나오고 바로 3등으로 출력 된다.

그러나 만약 1등 다음에 2등이 출력 되게 하고 싶다면  dens _ rank 사용한다. 

문제 : 이름, 나이, 나이에대한 순위 출력

select ename, age, rank () over (order by age desc)as 순위
from emp14 ;

RANK() 바로 다음 () 은 보통 비워 놓는데, 채우는 경우가 있다.

그 경우는 , 특정한 정보를 채워 넣었으면 그 다음에 나오는 전치사는 over 가 아닌 within group 을 작성해야한다.

→ witin 은 뜻이 ~ 이내에 이기 때문에 , witihn group 은 어느 group 이내에 라는 뜻입니다.

→ () 에 어떤 특정한 정보를 넣었으면 다른 컬럼은 넣을 수 없다.

문제 : 월급이 2850 은 사원테이블 전체에서 월급의 순위가 몇위인가?

select rank(2850) within group (order by sal desc) as 순위 
from emp;

→ 월급이 높은사람으로 정렬된 순위이내에 rank가 몇위인가? 

문제 : 나이가 28 살은 우리반에 나이의 순위가 몇위인가?

select rank(28) within group (order by age desc) as 순위
from emp14;

or

select dense_rank(28) within group (order by age desc) as 순위 
from emp14;

→ dense_rnak 를 쓰게 되면 차례대로 순위가 나온다.

문제 : 이름, 입사일, 순위를 출력하는데 순위가 먼저 입사한 사원순으로 순위를 부여하시오.

select ename, hiredate, rank() over (order by hiredate asc)as 순위 
from emp;

문제 : 살인이 일어나는 장소, 건수, 순위 출력 (건수가 높은순서대로)

select loc, ccount, rank () over (order by ccount desc) as 순위 
from crime_loc
where crime = '살인';

---

DENSE_RANK (순위)

예제 : 이름,직업 월급 순위 출력, 그여에 순위가 동일한 사람이 여러명인 경우 바로 다음 순위가 출력되도록

select ename, job, sal, rank() over (order by sal desc) 순위, 
                        dense_rank () over (order by sal desc) 순위2 
from emp
where job in ('ANALST', 'SALESMAN');

문제  : 부서번호,이름, 입사일, 순위를 출력하는데 순위가 먼저 입사한 사원순으로 순위를 부여 하여 출력

select deptno, ename, hiredate, dense_rank() over (order by hiredate asc)순위 
from emp;

문제 : 위의결과를 다시 출력하는데, 부서번호별로 각각 먼저 입사한 사원순으로 순위를 부여

select deptno, ename, hiredate, dense_rank() over (partition by deptno 
                                                   order by hiredate asc)순위 
from emp;

→ 부서 번호별로 묶어서 따로 순위를 출력하고 싶다면 partition by 컬럼명 (전체 등수가 아닌 부서별로 나뉜 컬럼에서 순위가 나타난다)

→ partition by : 오라클 전체를 통틀어서 분석함수 사용시에만 사용되며

over 다음에 ( 괄호 ) 안에 쓰이는 문법이다.

order by 절 앞에 파티션 할 컬럼명을 명시할때 사용하는 키워드이다.

문제 175 : 직업, 이름, 월급, 순위 출력 , 순위가 직업별로 각각 월급이 높은 순서대로 순위를 부여

select job, ename, sal, dense_rank () over (partition by job
                                            order by sal desc) 순위 
from emp ;

문제 176 : 통신사, 이름, 나이, 순위를 출력 순위가 통신사별로 각각 나이가 높은순으로 순위를 부여하시오

select decode(lower(telecom), 'skt','sk',lower(telecom))as telecom , 
       ename, age, 
       dense_rank() over (partition by decode(lower(telecom), 'skt','sk',lower(telecom)) 
                          order by age desc) as 순위
from emp14 ;

→ partition by 절에 쓰이는 컬럼은 select 절에 쓰인 컬럼을 사용해 주어야한다.

 

각각 순위를 구한다고 하면 PARTITION By 와 order by 를 꼭 함께 쓰도록