Oracle 分析函数

说明：由于 oracle 默认大写，所以在本文中会出现一下大写一下小写，在 oracle 都可以执行，如果 oracle 字段为小写字段，则需要用双引号查询。

需求举例

需求

假如存在一个需求，查询每个用户所在部门的总工资，在每个员工后面增加一个总工资字段显示。

例如部门一合计为15000，部门二合计为10000，需要显示如下

模拟数据

我们先创建一下数据：

-- 创建测试表
CREATE TABLE user_info_test(
    id varchar2(32) PRIMARY KEY,
    name varchar2(32),
    dept varchar2(32),
    salary number(10,4)
);

-- 批量插入数据
INSERT INTO user_info_test(id,name,dept,salary)
(SELECT '1','员工一','部门一',3000 FROM dual UNION ALL 
 SELECT '2','员工二','部门一',4000 FROM dual UNION all
 SELECT '3','员工三','部门一',8000 FROM dual UNION all
 SELECT '4','员工四','部门二',10000 FROM dual);

实现

如果要实现以上需求，常规做法为：

• 先查询出每个部门的工资合计
• 再与原表拼接（根据 dept 部门拼接）

-- 查询每个部门的工资合计
SELECT sum(SALARY) sum_salary,dept FROM USER_INFO_TEST u2 GROUP BY dept;

-- 与原表拼接
SELECT u1.*,u3.sum_salary 
FROM USER_INFO_TEST u1 
INNER JOIN (SELECT sum(SALARY) sum_salary,dept FROM USER_INFO_TEST u2 GROUP BY dept) u3
ON u1.dept = u3.dept
ORDER BY u1.ID;

分析函数

但是上面的写法过于冗余，可以利用 oracle 特有函数，分析函数（窗口函数/开窗函数）。

说明

分析函数可以在数据中进行分组然后计算基于组的某种统计值，并且每一组的每一行都可以返回一个统计值

格式

函数 over([partition by 分组字段] [order by 排序字段 [rows 窗口]])

注：[] 为可填，并非必填

• 函数可以分为
  • 聚合函数，例如 sum(),max(),min() ...
  • 排序函数，例如 row_number(),rank() ...
• partition by
  • 可以看成是 group by，对前面的聚合函数进行分组计算
    • 例如 sum(salary) over(partition by dept) 就是根据 dept 进行分组，求出每个分组中的 sum(salary)
  • 当 partition by 不写时，则为整个列表看成是一个分组
• order by 比较麻烦，具体看后面 order by。
• rows 窗口分为
  • UNBOUNDED PRECEDING 第一行
  • CURRENT ROW 当前行
  • UNBOUNDED FOLLOWING 最后一行

实现上述需求

利用窗口函数实现需求，由于我们需要求和，所以需要使用 聚合函数sum，同时对 dept 进行分组

-- 分析函数/窗口函数
sum(salary) over(partition by dept);

-- 整个函数
SELECT u1.*,
sum(SALARY) over(PARTITION BY u1.DEPT) sum_salary 
FROM USER_INFO_TEST u1
ORDER BY u1.ID

计算总工资合计，但是通过 over() 进行分析，通过 dept 字段进行分组合计，所以只会求出自己所在部门的 salary 合计。

再如我们需要查询出本部门最低、最高的 salary 在后面进行显示对比

SELECT u1.*,
sum(SALARY) over(PARTITION BY u1.DEPT) sum_salary,
min(SALARY) over(PARTITION BY u1.DEPT) min_salary,
max(SALARY) over(PARTITION BY u1.DEPT) max_salary
FROM USER_INFO_TEST u1
ORDER BY u1.ID;

谈谈 order by

分析函数主要是用于将聚合函数等转换成单行函数，方便后续修改或者查看等操作。

函数主要使用聚合函数，通过 partition by 分组字段进行分组，等同于 group by。

要注意 order by 并不是普通排序，既然是某个分组聚合，肯定内部不存在内排序，我们来试一下采用 order by。

SELECT u1.*,
sum(SALARY) over(PARTITION BY u1.DEPT) sum_salary,
sum(SALARY) over(PARTITION BY u1.DEPT ORDER BY id) order_salary
FROM USER_INFO_TEST u1
ORDER BY u1.ID;

可以看到加了 order by 之后，order_salary 首先是在部门内部进行了合计，但是是从上到下，在部门内部进行合计，是根据 id 从小到大在 dept 内部进行合计（order by id）。

id 为 2 的 order_salary 为 （id 为 1 的 salary + id 为 2 的 salary）；

id 为 3 的 order_salary 为 （id 为 1 的 salary + id 为 2 的 salary + id 为 3 的 salary）；

id 为 4 的 order_slary 为 自己的 salary（因为他是部门二，不属于部门一）

rows 窗口

在了解了分析函数的 order by 之后，rows 相对而言属于比较简单。

注意 rows 不能单独使用，必须和 order by 结合使用，order by 默认 rows between UNBOUNDED PRECEDING and CURRENT ROW ，即从第一行到当前行，所以上方合计就是从 id 小的到当前行合计。

SELECT u1.*,
sum(SALARY) over(PARTITION BY u1.DEPT) sum_salary,
sum(SALARY) over(PARTITION BY u1.DEPT ORDER BY id) order_salary,
sum(SALARY) over(PARTITION BY u1.DEPT ORDER BY id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) order_salary2
FROM USER_INFO_TEST u1
ORDER BY u1.ID;

一般用的 rows 比较少，大致了解就行。

再谈分析函数

分析函数主要用于将聚合函数变成单行函数去执行计算等操作，注意由于 select 字段是在 sql 中最后执行的，所以分析函数也同样会用到查询中的 where 条件，所以不用担心他只会进行分组。

不使用分析函数也可以通过其他方法查询数据，分析函数只是一种辅助功能，方便编写 sql。