oracle 分析函数

row_number() over(partition by ... order by ...)  

rank() over(partition by ... order by ...)  

dense_rank() over(partition by ... order by ...)  

count() over(partition by ... order by ...)  

max() over(partition by ... order by ...)  

min() over(partition by ... order by ...)  

sum() over(partition by ... order by ...)  

avg() over(partition by ... order by ...)  

first_value() over(partition by ... order by ...)  

last_value() over(partition by ... order by ...)  

lag() over(partition by ... order by ...)  

lead() over(partition by ... order by ...)  

 

rank()和dense_rank()的区别是：  

rank()是跳跃排序，有两个第二名时接下来就是第四名  

dense_rank()是连续排序，有两个第二名时仍然跟着第三名  

 

例子:

SELECT emp.full_name,

       emp.salary,

       emp.manager_id,

       row_number() over(PARTITION BY emp.manager_id ORDER BY emp.salary DESC) row_number_dept, --部门排行

       rownum row_number, --行号

       round((rownum + 1) / 4) page_number, --每4行一页

       ntile(2) over(ORDER BY emp.salary 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) over(PARTITION BY emp.manager_id ORDER BY emp.salary) min_salary_dept_firstv, --部门的最低薪水     

       

       MAX(emp.salary) over(PARTITION BY emp.manager_id) max_salary_dept, --部门的最高薪水 

       MAX(emp.salary) keep(dense_rank LAST ORDER BY emp.salary) over(PARTITION BY emp.manager_id) max_salary_dept_last, --部门的最高薪水 

       last_value(emp.salary) over(PARTITION BY emp.manager_id ORDER BY emp.salary) max_salary_dept_lastv, --部门的最高薪水

       

       lag(emp.full_name, 1, '00') over(ORDER BY emp.salary DESC) last_persion, --薪水在自己前一位的人

       lead(emp.full_name, 1, '00') over(ORDER BY emp.salary DESC) next_persion --薪水在自己后一位的人

  FROM fwk_tbx_employees emp

ORDER BY emp.salary DESC;