Oracle 逐行累计求和函数 sum() over()

文章目录

• 1 概述
• 2 示例
• 3 扩展
• • 3.1 最大值和最小值

1 概述

1. 需求简述：每列 '追加' 所有前列的值
   column   =>   new column
   v1            v1
   v2            v1 + v2
   v3            v1 + v2 + v3
   ...           ...
   vn            v1 + v2 + v3 + ... + vn   
2. 解决办法
   sum(列1) over(order by 列2)
   sum(列1) over(partition by 列2 order by 列3) -- 先按 列2 分组

 
 

2 示例

with student_info as (
  select 1 sno, 'a' sname, 80 score from dual union all
  select 2 sno, 'b' sname, 100 score from dual union all
  select 3 sno, 'c' sname, 120 score from dual union all
  select 4 sno, 'a' sname, 100 score from dual union all
  select 5 sno, 'b' sname, 200 score from dual
)
select si.sno 学号,
       si.sname 姓名,
       si.score 成绩,
       sum(si.score) over(order by si.sno) 累计成绩1,
       sum(si.score) over(partition by si.sname order by si.sno) 累计成绩2
  from student_info si
 order by si.sno;

 
 

查询结果:

学号		姓名		成绩		累计成绩1	累计成绩2
1		a		80		80			80       -- a1
2		b		100		180			100            --- b1         
3		c		120		300			120 
4		a		100		400			180      -- a2
5		b		200		600			300            --- b2

 
 

3 扩展

3.1 最大值和最小值

-- 聚合函数 + over()
with student_info as (
  select 1 sno, 'a' sname, 80 score from dual union all
  select 2 sno, 'b' sname, 100 score from dual union all
  select 3 sno, 'c' sname, 120 score from dual
)
select si.sno 学号,
       si.sname 姓名,
       si.score 成绩,
       max(si.score) over(order by si.sno) 最大值,
       min(si.score) over(order by si.sno) 最小值,
       avg(si.score) over(order by si.sno) 平均值
  from student_info si;