oracle 分析函数之分组求和、连续求和

article/2025/9/18 6:50:13

最近在《sql cookbook》书上发现了名叫 分析函数 的东西，之前学 oracle 时没有印象，现在感觉其分析函数的功能相当强大、神奇，

就特意去找到了 chm 文档研究了一下，想要的朋友在本文末尾自行下载。

本文的例子都来源于 chm 文档，本人只是执行了这些 sql，看着结果理解分析函数的功能用途，并对分析函数做了注释，方便后面查看理解。

请各位读者结合 chm 中的例子一起食用，更好理解消化。

1、分组求和：GROUP BY子句

具体 sql 实例我就不贴了，具体请查看chm 分析函数 6.00

分别执行以下 sql ，对比它们执行后的结果：

--原：这是我们大家都知道的 group by() 函数
select id,area,stu_type,sum(score) score 
from students
group by id,area,stu_type
order by id,area,stu_type;--A、GROUPING SETS
select id,area,stu_type,sum(score) score 
from students
group by grouping sets((id,area,stu_type),(id,area),id)
order by id,area,stu_type;--B、ROLLUP
select id,area,stu_type,sum(score) score 
from students
group by rollup(id,area,stu_type)
order by id,area,stu_type;--C、CUBE
select id,area,stu_type,sum(score) score 
from students
group by cube(id,area,stu_type)
order by id,area,stu_type;

下面是这四条语句分别执行后的结果

注：1）每个函数都是在原来的基础上增加了功能，对不同分组条件的求和；

2）对函数的理解自行看结果对比理解，鄙人暂时没想到合适的语言解释。

2、连续求和：sum(...) over(...)

具体实例看 chm 文档 6.01

使用 sum(sal) over (order by ename)... 查询员工的薪水“连续”求和,注意over (order by ename)；

如果没有order by 子句，求和就不是“连续”的，放在一起，体会一下不同之处：

select deptno,ename,sal,sum(sal) over (order by ename) 连续求和,sum(sal) over () 总和,   -- 此处sum(sal) over () 等同于sum(sal)100*round(sal/sum(sal) over (),4) "份额(%)"from emp

以下是执行结果，由于内置的表数据可能不一样，每个人的结果不一定一样，但效果是一样的

从结果可以看出，这里的连续求和的意思是把当前行的数据与之前所有数据相加求和作为该行的和

------------------------------------------------------------------------------------------------------------------------------------------------

使用子分区查出各部门薪水连续的总和。注意按部门分区。注意over(...)条件的不同，

sum(sal) over (partition by deptno order by ename) 按部门“连续”求总和
sum(sal) over (partition by deptno) 按部门求总和
sum(sal) over (order by deptno，ename) 不按部门“连续”求总和
sum(sal) over () 不按部门，求所有员工总和，效果等同于sum(sal)

还是贴一下执行结果更方便理解吧

partition 是划分、分割、区分的意思，partition by 类似于 group by

这里的功能就是按部门分区，在每个部门内部做连续求和

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------

注意：在"... from emp;"后面不要加order by 子句，使用的分析函数的(partition by deptno order by sal)里已经有排序的语句了，

如果再在句尾添加排序子句，一致倒罢了，不一致，结果就令人费劲了。