분석함수, 순위함수 (RANK, DENSE_RANK, ROW_NUMBER, ROWNUM)

-- 분석 함수
/*
순위함수
RANK         1 2 3 3 5 6 
DENSE_RANK() 1 2 3 3 4 5
ROW_NUMBER() 1 2 3 4 5 6 
ROWNUM

필요한 이유: 순서를 번호로 설정하기위해

*/
 
SELECT employee_id, first_name
FROM employees
WHERE employee_id >= 100 AND employee_id <=109;
 
SELECT employee_id, first_name, 
    RANK()OVER(ORDER BY salary DESC) AS RANK,
    DENSE_RANK()OVER(ORDER BY salary DESC) AS DENSE_RANK,
    ROW_NUMBER()OVER(ORDER BY salary DESC) AS ROW_NUMBER
FROM employees;
 
SELECT ROWNUM ,employee_id, first_name
FROM employees
WHERE ROWNUM <=10;
 
 
SELECT ROWNUM ,employee_id, first_name
FROM employees
WHERE ROWNUM >10 AND ROWNUM <=20;
 
 
 
--월급의 랭킹 1~10
--1.급여의순서
--2.번호설정
--3.범위지정
SELECT RNUM, employee_id, first_name, salary   --3.범위지정
FROM
    (SELECT ROWNUM AS RNUM, employee_id, first_name, salary   --2.번호설정
FROM
(       SELECT employee_id, first_name, salary   --1.급여의순서
        FROM employees
        ORDER BY salary DESC)
)
WHERE RNUM > 0 AND RNUM <=10;
 
/*
SELECT 절
        SELECT  컬럼, 표준함수, 그룹함수, 서브쿼리, OVER() PARTITON BY
        FROM 테이블, 서브쿼리
        (WHERE) 조건 IN AND OR ANY LIKE > <  = >=  <= != 서브쿼리
        (GROUP BY) 컬럼
        (HAVING) 그룹핑 조건
        (ORDER BY)  컬럼 ASC, DESC
        (START BY) 계층형
        (CONNECT BY) 연결형

        JOIN

        INNER
        FULL OUTER
        CROSS
        OUTER
        SELF
*/