[오라클로 베우는 데이터베이스 개론과 실습] 실무 실습 A.10 분석 함수 NTILE, RANK

A.10 분석 함수 : NTILE, RANK

 

샘플문제

SELECT DEPARTMENT_ID, SUM(SALARY) AS "Sum Salary", NTILE(4) 
OVER (ORDER BY SUM(SALARY) DESC) AS "Bucket#"
FROM EMPLOYEES
GROUP BY DEPARTMENT_ID
ORDER BY 3;

 

 

문제 1

SELECT EMPLOYEE_ID, LAST_NAME, SALARY, DEPARTMENT_ID, 
                RANK() OVER(PARTITION BY DEPARTMENT_ID ORDER BY SALARY DESC) AS "Rank",
                DENSE_RANK() OVER(PARTITION BY DEPARTMENT_ID ORDER BY SALARY DESC) AS "Dense_rank",
                ROW_NUMBER() OVER (PARTITION BY DEPARTMENT_ID ORDER BY SALARY DESC) AS " Row_number"
FROM EMPLOYEES
ORDER BY DEPARTMENT_ID;

 

문제 2

SELECT EMPLOYEE_ID, LAST_NAME, SALARY, 
                               LAG(SALARY,1,0) OVER(ORDER BY SALARY) AS "Pre_sal",
                               LEAD(SALARY,1,0) over (order by salary) as "Next_sal"
FROM EMPLOYEEs
where department_id = 50;

 

문제 3

SELECT DEPARTMENT_ID, COUNT(EMPLOYEE_ID) AS "COUNT(*)", 
                MAX(LAST_NAME) KEEP (DENSE_RANK FIRST ORDER BY SALARY) "Max_Emp_Name",
                MIN(LAST_NAME) KEEP (DENSE_RANK LAST ORDER BY SALARY) "Min_Emp_Name"
FROM EMPLOYEES
GROUP BY DEPARTMENT_ID
ORDER BY DEPARTMENT_ID;