# Oracle 分析函数、聚合函数的简单使用 学习笔记+实例

## 1.分析函数结构分析

function (argument1,argument2,...argumentN)
over ([partition-by-clause] [order-by-clause] [windowing-clause])

• 1
• 2

1.[partition-by-clause] 分区子句
2.[order-by-clause] 排序子句
3.[windowing-clause] 开窗子句

## 2.分析函数实例解析

SELECT d.department_name,e.last_name,e.salary,
rank() over(PARTITION BY d.department_name ORDER BY e.salary DESC) dept_salary_rank1,
percent_rank() over(PARTITION BY d.department_name ORDER BY e.salary DESC) dept_salary_rank2,
row_number() over(PARTITION BY d.department_name ORDER BY e.salary) dept_salary_rank3
FROM employees e, departments d
WHERE 1 = 1
AND e.department_id = d.department_id;

• 1
• 2
• 3
• 4
• 5
• 6
• 7

(1) rank() over(PARTITION BY d.department_name ORDER BY e.salary DESC) dept_salary_rank1,
1>分区子句：partiton by 这里按照部门名称进行分区
2>排序子句：order by 这里按照薪水进行排序
3>开窗子句：暂未用到
4>分析函数：rank()即按照排序后的顺序进行排名

(2) percent_rank() over(PARTITION BY d.department_name ORDER BY e.salary DESC) dept_salary_rank2,

(3) row_number() over(PARTITION BY d.department_name ORDER BY e.salary) dept_salary_rank3

row_number()意为对排序后的每一行增加一个唯一编号，此处图中与rank1列不同的原因是这条语句采用的是升序！！！

## 3.聚合函数的分析模式运算

function (argument1,argument2,...argumentN)
over ([partition-by-clause] [order-by-clause] [windowing-clause])

• 1
• 2

SELECT e.last_name,e.salary,d.department_name,
AVG(e.salary) over(PARTITION BY d.department_name) department_avg_salary,
MAX(e.salary) over(PARTITION BY d.department_name) department_max_salary,
MIN(e.salary) over(PARTITION BY d.department_name) department_min_salary
FROM employees e, departments d
WHERE 1 = 1 AND e.department_id = d.department_id;

• 1
• 2
• 3
• 4
• 5
• 6

## 4.开窗子句

##### 4.2 开窗子句的语法格式
[rows|range] between <start expr> and [end expr]

• 1

start expr和end expr两个参数的值可以为unbounded following（无限制的）、current row（当前行）、 n preceding（向前n行）、 n following（向后n行）。

##### 4.3 使用实例解析

SELECT d.department_name, e.last_name, e.salary,
sum(e.salary) over(PARTITION BY d.department_name) department_sum1_salary,
-- 此处使用默认的开窗子句
SUM(e.salary) over(PARTITION BY d.department_name order by e.salary ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) department_sum2_salary
-- 此处使用自定义开窗子句，为选择上一条数据到下一条数据区域
FROM employees e, departments d
WHERE 1 = 1 AND e.department_id = d.department_id;

• 1
• 2
• 3
• 4
• 5
• 6
• 7

+ 订阅