Oracle分析函数入门教程

一：分析函数是什么？
分析函数是Oracle专门用于解决复杂报表统计需求的功能强大的函数，它可以在数据中进行分组然后计算基于组的某种统计值，并且每一组的每一行都可以返回一个统计值。

二：分析函数和聚合函数的不同之处是什么？

普通的聚合函数用group by分组，每个分组返回一个统计值，而分析函数采用partition by分组，并且每组每行都可以返回一个统计值。

三：分析函数的形式
分析函数带有一个开窗函数over()，包含两个分析子句：分组(partition by)、 排序(order by)，他们的使用形式如下：over(partition by xxx order by yyy)。

四：分析函数例子(在scott用户下模拟)

示例一：按照部分Id分组，然后计算每组薪水的最大值

1 select employee_id,last_name,department_id,salary,max(salary) over(partition by department_id) "max_val"
2 from cux_employees
3 order by employee_id

运行结果：

示例二：按照部门名称分组，然后按照部门内员工薪水升序排列，计算每组成员薪水的排序情况

1 SELECT d.department_name,
2        e.last_name,
3        e.salary,
4        rank() over(PARTITION BY d.department_name ORDER BY e.salary) dept_salary_rank1,
5        dense_rank() over(PARTITION BY d.department_name ORDER BY e.salary) dept_salary_rank2,
6        row_number() over(PARTITION BY d.department_name ORDER BY e.salary) dept_salary_rank3
7 FROM cux_employees e,cux_departments d
8 WHERE e.department_id = d.department_id

运行结果：

补充：

rank()，dense\_rank()，row\_number()的区别

一：语法
rank() over([partition by col1] order by col2)
dense\_rank() over([partition by col1] order by col2)
row\_number() over([partition by col1] order by col2)
其中[partition by col1]可省略。

二：区别
三个分析函数都是按照col1分组内从1开始排序。

row\_number()：是没有重复值的排序(即使两天记录相等，顺序值也是不重复的)；

dense\_rank()： 是连续排序，两个第二名下来的是第三名；

rank()： 是跳跃排序，两个第二名下来就是第四名。