Oracle 里的 分析函数

分析函数例子及讲解
SELECT emp.ename,
emp.sal,
emp.mgr,
row_number() over(PARTITION BY emp.mgr ORDER BY emp.sal DESC) row_number_dept, –部门排行
rownum row_number, –行号
ceil(rownum / 6) page,–每6行一页
ntile(2) over(ORDER BY emp.sa DESC) page_number_nt, –平均分成两类

   AVG(emp.salary) over(PARTITION BY emp.manager_id) avg_salary_department, --该部门薪水均值
   SUM(emp.salary) over(PARTITION BY emp.manager_id) sum_salary_department, --该部门薪水总额
   COUNT(emp.salary) over(PARTITION BY emp.manager_id) count_emp_department, --部门所有的员工
   dense_rank() over(PARTITION BY emp.manager_id ORDER BY emp.salary DESC) rank_salary_dept, --该人员的部门薪水排行
   dense_rank() over(ORDER BY emp.salary DESC) rank_salary_company, --该人员的全公司排行

   MIN(emp.salary) over(PARTITION BY emp.manager_id) min_salary_dept, --部门的最低薪水       
   MIN(emp.salary) keep(dense_rank FIRST ORDER BY emp.salary) over(PARTITION BY emp.manager_id) min_salary_dept_first, --部门的最低薪水
   first_value(emp.salary)