오라클 ROWNUM, RANK, DENSE_RANK, ROW_NUMBER, PARTITION

순위매기기

ROWNUM으로 만들면 같은 금액도 순위가 넘어간다

SELECT ename, sal, rownum
FROM
(
SELECT ename, sal
FROM emp
);

 

 

ROWNUM으로 순위 합치기

 + LAG OVER 함수는 순서를 미루는 함수

SELECT ename, s1, s2, DECODE(s1, s2, NO-1, NO)
FROM
(
SELECT ename, sal "S1", LAG(sal, 1, 0) OVER(ORDER BY sal DESC) "S2", ROWNUM "NO"
FROM
(
SELECT ename, sal
FROM emp           -- 프롬으로 퍼올릴때 로우넘 번호가 매겨진다
ORDER BY Sal desc
)
);

 

 

RANK OVER를 이용한 순위합치기

SELECT ename, sal, RANK() OVER (ORDER BY sal DESC)
FROM emp;

 

 

RANK() OVER 를 이용해서 학생 키 순위 구하기

SELECT name, height, RANK() OVER (ORDER BY height DESC) "RANK"
FROM student;

 

 

학년별 키순위를 구하시오

union all 방식

SELECT name, grade, height, RANK() OVER (ORDER BY height DESC) "RANK"
FROM student
WHERE grade = 1

UNION ALL

SELECT name, grade, height, RANK() OVER (ORDER BY height DESC) "RANK"
FROM student
WHERE grade = 2

UNION ALL

SELECT name, grade, height, RANK() OVER (ORDER BY height DESC) "RANK"
FROM student
WHERE grade = 3

UNION ALL

SELECT name, grade, height, RANK() OVER (ORDER BY height DESC) "RANK"
FROM student
WHERE grade = 4;

 

PARTITION 방식

SELECT name, grade, height, RANK() OVER (PARTITION BY grade ORDER BY height DESC) "RANK"
FROM student;

 

 

RANK, DENSE_RANK, ROW_NUMBER 비교

SELECT empno, ename, job, sal,
    RANK() OVER (ORDER BY sal DESC) sal_rank,
    DENSE_RANK() OVER (ORDER BY sal DESC) sal_dense_rank,
    ROW_NUMBER() OVER (ORDER BY sal DESC) sal_row_num
FROM emp;

 

--------------------------