如何使用 SUM 和 OVER 在 Oracle 中获得运行总数 [英] How to get running total in Oracle using SUM and OVER

查看：34
本文介绍了如何使用 SUM 和 OVER 在 Oracle 中获得运行总数的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我正在使用 Sum() Over (Partition by ...Order by ..) 函数来计算列的运行总数，但只要值重复(或重复)就会出现问题.Sum() over 似乎按值对它们进行分组，并将所有按值分组的总和作为移动总和.这不是我打算实现的目标.关于为什么它作为一组重复值发生的任何想法或输入，以及如何在不按行进行游标循环的情况下获得正确的运行总数，我们深表感谢.下面是测试表的详细信息及其数据，并附上一个带有预期结果的文件(附件中突出显示的列是 Oracle Sum() 计算错误的内容).

I am using the Sum() Over (Partition by ...Order by ..) function to calculate the running total of a column, but the problem arises whenever there is a repetition (or duplication) of the value. Sum() over seems to group them by the values and puts the sum of all the grouped by values as a moving sum. Which is not what I intend to achieve. Any thoughts or inputs on why it is happening as a group by of duplicate values and how can I achieve the correct running total without doing a cursor loop by the ranks, is deeply appreciated. Below are the details of the test table along with it's data and also attaching a file with the expected results (highlighted column in the attached file is what Oracle Sum() is calculating incorrectly).

CREATE TABLE RUNNINGTOTAL_TEST1
(
A1 NUMBER(10,0),
A2 VARCHAR2(20 BYTE), 
A2SUBCLASS VARCHAR2(1000 BYTE),  
A2CONTRI FLOAT(126), 
DENSERNK NUMBER);

Insert into RUNNINGTOTAL_TEST1 (A1,A2,A2SUBCLASS,A2CONTRI,DENSERNK) values 
(22418,'2231113','CLASS1',0,1);
Insert into RUNNINGTOTAL_TEST1 (A1,A2,A2SUBCLASS,A2CONTRI,DENSERNK) values 
(22418,'2231216','CLASS1',0,1);
Insert into RUNNINGTOTAL_TEST1 (A1,A2,A2SUBCLASS,A2CONTRI,DENSERNK) values 
(22418,'2232517','CLASS1',0.0015,2);
Insert into RUNNINGTOTAL_TEST1 (A1,A2,A2SUBCLASS,A2CONTRI,DENSERNK) values 
(22418,'2274031','CLASS1',0.0045,3);
Insert into RUNNINGTOTAL_TEST1 (A1,A2,A2SUBCLASS,A2CONTRI,DENSERNK) values 
(22418,'2281035','CLASS1',0.0045,3);
Insert into RUNNINGTOTAL_TEST1 (A1,A2,A2SUBCLASS,A2CONTRI,DENSERNK) values 
(22418,'2309174','CLASS1',0.0045,3);
Insert into RUNNINGTOTAL_TEST1 (A1,A2,A2SUBCLASS,A2CONTRI,DENSERNK) values 
(22418,'2243993','CLASS1',0.0045,3);
Insert into RUNNINGTOTAL_TEST1 (A1,A2,A2SUBCLASS,A2CONTRI,DENSERNK) values 
(22418,'2301871','CLASS1',0.0045,3);
Insert into RUNNINGTOTAL_TEST1 (A1,A2,A2SUBCLASS,A2CONTRI,DENSERNK) values 
(22418,'2229261','CLASS1',0.0045,3);
Insert into RUNNINGTOTAL_TEST1 (A1,A2,A2SUBCLASS,A2CONTRI,DENSERNK) values 
(22418,'2239639','CLASS1',0.0085,4);
Insert into RUNNINGTOTAL_TEST1 (A1,A2,A2SUBCLASS,A2CONTRI,DENSERNK) values 
(22418,'2229259','CLASS1',0.0085,4);
Insert into RUNNINGTOTAL_TEST1 (A1,A2,A2SUBCLASS,A2CONTRI,DENSERNK) values 
(22418,'2223745','CLASS1',0.0085,4);
Insert into RUNNINGTOTAL_TEST1 (A1,A2,A2SUBCLASS,A2CONTRI,DENSERNK) values 
(22418,'2221525','CLASS1',0.0085,4);
Insert into RUNNINGTOTAL_TEST1 (A1,A2,A2SUBCLASS,A2CONTRI,DENSERNK) values 
(22418,'2116718','CLASS1',0.0085,4);
Insert into RUNNINGTOTAL_TEST1 (A1,A2,A2SUBCLASS,A2CONTRI,DENSERNK) values 
(22418,'2259505','CLASS1',0.0085,4);
Insert into RUNNINGTOTAL_TEST1 (A1,A2,A2SUBCLASS,A2CONTRI,DENSERNK) values 
(22418,'2260197','CLASS1',0.0085,4);
Insert into RUNNINGTOTAL_TEST1 (A1,A2,A2SUBCLASS,A2CONTRI,DENSERNK) values 
(22418,'2286499','CLASS1',0.0085,4);
Insert into RUNNINGTOTAL_TEST1 (A1,A2,A2SUBCLASS,A2CONTRI,DENSERNK) values 
(22418,'2293773','CLASS1',0.0085,4);
Insert into RUNNINGTOTAL_TEST1 (A1,A2,A2SUBCLASS,A2CONTRI,DENSERNK) values 
(22418,'2116719','CLASS1',0.0085,4);
Insert into RUNNINGTOTAL_TEST1 (A1,A2,A2SUBCLASS,A2CONTRI,DENSERNK) values     
(22418,'2308288','CLASS1',0.0085,4);
Insert into RUNNINGTOTAL_TEST1 (A1,A2,A2SUBCLASS,A2CONTRI,DENSERNK) values 
(22418,'2309143','CLASS1',0.0085,4);
Insert into RUNNINGTOTAL_TEST1 (A1,A2,A2SUBCLASS,A2CONTRI,DENSERNK) values 
(22418,'2302209','CLASS1',0.0085,4);
Insert into RUNNINGTOTAL_TEST1 (A1,A2,A2SUBCLASS,A2CONTRI,DENSERNK) values 
(22418,'2127518','CLASS1',0.01,5);
Insert into RUNNINGTOTAL_TEST1 (A1,A2,A2SUBCLASS,A2CONTRI,DENSERNK) values 
(22418,'2308292','CLASS1',0.01,5);
Insert into RUNNINGTOTAL_TEST1 (A1,A2,A2SUBCLASS,A2CONTRI,DENSERNK) values 
(22418,'2278399','CLASS1',0.01,5);
Insert into RUNNINGTOTAL_TEST1 (A1,A2,A2SUBCLASS,A2CONTRI,DENSERNK) values 
(22418,'2204968','CLASS1',0.01,5);
Insert into RUNNINGTOTAL_TEST1 (A1,A2,A2SUBCLASS,A2CONTRI,DENSERNK) values 
(22418,'2204969','CLASS1',0.01,5);
Insert into RUNNINGTOTAL_TEST1 (A1,A2,A2SUBCLASS,A2CONTRI,DENSERNK) values 
(22418,'2204970','CLASS1',0.01,5);
Insert into RUNNINGTOTAL_TEST1 (A1,A2,A2SUBCLASS,A2CONTRI,DENSERNK) values 
(22418,'2229754','CLASS1',0.01,5);
Insert into RUNNINGTOTAL_TEST1 (A1,A2,A2SUBCLASS,A2CONTRI,DENSERNK) values 
(22418,'2259507','CLASS1',0.01,5);
Insert into RUNNINGTOTAL_TEST1 (A1,A2,A2SUBCLASS,A2CONTRI,DENSERNK) values 
(22418,'2278400','CLASS1',0.01,5);
Insert into RUNNINGTOTAL_TEST1 (A1,A2,A2SUBCLASS,A2CONTRI,DENSERNK) values 
(22418,'2280023','CLASS1',0.01,5);
Insert into RUNNINGTOTAL_TEST1 (A1,A2,A2SUBCLASS,A2CONTRI,DENSERNK) values 
(22418,'6501265','CLASS1',0.01,5);
Insert into RUNNINGTOTAL_TEST1 (A1,A2,A2SUBCLASS,A2CONTRI,DENSERNK) values 
(22418,'6501364','CLASS1',0.01,5);
Insert into RUNNINGTOTAL_TEST1 (A1,A2,A2SUBCLASS,A2CONTRI,DENSERNK) values 
(22418,'6501503','CLASS1',0.01,5);
Insert into RUNNINGTOTAL_TEST1 (A1,A2,A2SUBCLASS,A2CONTRI,DENSERNK) values 
(22418,'2308290','CLASS1',0.01,5);
Insert into RUNNINGTOTAL_TEST1 (A1,A2,A2SUBCLASS,A2CONTRI,DENSERNK) values 
(22418,'2262629','CLASS1',0.013,6);
Insert into RUNNINGTOTAL_TEST1 (A1,A2,A2SUBCLASS,A2CONTRI,DENSERNK) values 
(22418,'2301762','CLASS1',0.013,6);
Insert into RUNNINGTOTAL_TEST1 (A1,A2,A2SUBCLASS,A2CONTRI,DENSERNK) values 
(22418,'2309142','CLASS1',0.013,6);
Insert into RUNNINGTOTAL_TEST1 (A1,A2,A2SUBCLASS,A2CONTRI,DENSERNK) values 
(22418,'2280969','CLASS1',0.013,6);
Insert into RUNNINGTOTAL_TEST1 (A1,A2,A2SUBCLASS,A2CONTRI,DENSERNK) values 
(22418,'2267828','CLASS1',0.014,7);
Insert into RUNNINGTOTAL_TEST1 (A1,A2,A2SUBCLASS,A2CONTRI,DENSERNK) values 
(22418,'2294583','CLASS1',0.0145,8);
Insert into RUNNINGTOTAL_TEST1 (A1,A2,A2SUBCLASS,A2CONTRI,DENSERNK) values 
(22418,'2302508','CLASS1',0.0145,8);
Insert into RUNNINGTOTAL_TEST1 (A1,A2,A2SUBCLASS,A2CONTRI,DENSERNK) values 
(22418,'6501778','CLASS1',0.0155,9);
Insert into RUNNINGTOTAL_TEST1 (A1,A2,A2SUBCLASS,A2CONTRI,DENSERNK) values 
(22418,'2285501','CLASS1',0.0185,10);
Insert into RUNNINGTOTAL_TEST1 (A1,A2,A2SUBCLASS,A2CONTRI,DENSERNK) values 
(22418,'2267827','CLASS1',0.0185,10);
Insert into RUNNINGTOTAL_TEST1 (A1,A2,A2SUBCLASS,A2CONTRI,DENSERNK) values 
(22418,'2211737','CLASS1',0.023,11);
Insert into RUNNINGTOTAL_TEST1 (A1,A2,A2SUBCLASS,A2CONTRI,DENSERNK) values 
(22418,'2216684','CLASS1',0.023,11);
Insert into RUNNINGTOTAL_TEST1 (A1,A2,A2SUBCLASS,A2CONTRI,DENSERNK) values 
(22418,'2091315','CLASS1',0.033,12);
Insert into RUNNINGTOTAL_TEST1 (A1,A2,A2SUBCLASS,A2CONTRI,DENSERNK) values 
(22418,'2305024','CLASS1',0.0395,13);
Insert into RUNNINGTOTAL_TEST1 (A1,A2,A2SUBCLASS,A2CONTRI,DENSERNK) values 
(22418,'2308194','CLASS2',0,1);
Insert into RUNNINGTOTAL_TEST1 (A1,A2,A2SUBCLASS,A2CONTRI,DENSERNK) values 
(22418,'2308226','CLASS2',0,1);
Insert into RUNNINGTOTAL_TEST1 (A1,A2,A2SUBCLASS,A2CONTRI,DENSERNK) values 
(22418,'2308227','CLASS2',0,1);
Insert into RUNNINGTOTAL_TEST1 (A1,A2,A2SUBCLASS,A2CONTRI,DENSERNK) values 
(22418,'2234249','CLASS2',0.0015,2);
Insert into RUNNINGTOTAL_TEST1 (A1,A2,A2SUBCLASS,A2CONTRI,DENSERNK) values 
(22418,'2308192','CLASS2',0.0015,2);
Insert into RUNNINGTOTAL_TEST1 (A1,A2,A2SUBCLASS,A2CONTRI,DENSERNK) values 
(22418,'6501630','CLASS2',0.0015,2);
Insert into RUNNINGTOTAL_TEST1 (A1,A2,A2SUBCLASS,A2CONTRI,DENSERNK) values 
(22418,'2251641','CLASS2',0.0015,2);
Insert into RUNNINGTOTAL_TEST1 (A1,A2,A2SUBCLASS,A2CONTRI,DENSERNK) values 
(22418,'2245418','CLASS2',0.0015,2);
Insert into RUNNINGTOTAL_TEST1 (A1,A2,A2SUBCLASS,A2CONTRI,DENSERNK) values 
(22418,'2286618','CLASS2',0.0055,3);
Insert into RUNNINGTOTAL_TEST1 (A1,A2,A2SUBCLASS,A2CONTRI,DENSERNK) values 
(22418,'2244631','CLASS2',0.0055,3);
Insert into RUNNINGTOTAL_TEST1 (A1,A2,A2SUBCLASS,A2CONTRI,DENSERNK) values 
(22418,'2158617','CLASS2',0.006,4);
Insert into RUNNINGTOTAL_TEST1 (A1,A2,A2SUBCLASS,A2CONTRI,DENSERNK) values 
(22418,'2256762','CLASS2',0.006,4);
Insert into RUNNINGTOTAL_TEST1 (A1,A2,A2SUBCLASS,A2CONTRI,DENSERNK) values 
(22418,'2276442','CLASS2',0.007,5);
Insert into RUNNINGTOTAL_TEST1 (A1,A2,A2SUBCLASS,A2CONTRI,DENSERNK) values 
(22418,'2302212','CLASS2',0.007,5);
Insert into RUNNINGTOTAL_TEST1 (A1,A2,A2SUBCLASS,A2CONTRI,DENSERNK) values 
(22418,'2280881','CLASS2',0.01,6);
Insert into RUNNINGTOTAL_TEST1 (A1,A2,A2SUBCLASS,A2CONTRI,DENSERNK) values 
(22418,'2234248','CLASS2',0.01,6);
Insert into RUNNINGTOTAL_TEST1 (A1,A2,A2SUBCLASS,A2CONTRI,DENSERNK) values 
(22418,'2259247','CLASS2',0.01,6);
Insert into RUNNINGTOTAL_TEST1 (A1,A2,A2SUBCLASS,A2CONTRI,DENSERNK) values 
(22418,'2267339','CLASS2',0.01,6);
Insert into RUNNINGTOTAL_TEST1 (A1,A2,A2SUBCLASS,A2CONTRI,DENSERNK) values 
(22418,'2273637','CLASS2',0.01,6);
Insert into RUNNINGTOTAL_TEST1 (A1,A2,A2SUBCLASS,A2CONTRI,DENSERNK) values 
(22418,'2273646','CLASS2',0.01,6);
Insert into RUNNINGTOTAL_TEST1 (A1,A2,A2SUBCLASS,A2CONTRI,DENSERNK) values 
(22418,'2280779','CLASS2',0.01,6);
Insert into RUNNINGTOTAL_TEST1 (A1,A2,A2SUBCLASS,A2CONTRI,DENSERNK) values 
(22418,'2280882','CLASS2',0.01,6);
Insert into RUNNINGTOTAL_TEST1 (A1,A2,A2SUBCLASS,A2CONTRI,DENSERNK) values 
(22418,'2280924','CLASS2',0.01,6);
Insert into RUNNINGTOTAL_TEST1 (A1,A2,A2SUBCLASS,A2CONTRI,DENSERNK) values 
(22418,'2308132','CLASS2',0.01,6);
Insert into RUNNINGTOTAL_TEST1 (A1,A2,A2SUBCLASS,A2CONTRI,DENSERNK) values 
(22418,'2308134','CLASS2',0.01,6);
Insert into RUNNINGTOTAL_TEST1 (A1,A2,A2SUBCLASS,A2CONTRI,DENSERNK) values 
(22418,'2308193','CLASS2',0.01,6);
Insert into RUNNINGTOTAL_TEST1 (A1,A2,A2SUBCLASS,A2CONTRI,DENSERNK) values 
(22418,'2308235','CLASS2',0.01,6);
Insert into RUNNINGTOTAL_TEST1 (A1,A2,A2SUBCLASS,A2CONTRI,DENSERNK) values 
(22418,'2265174','CLASS2',0.01,6);
Insert into RUNNINGTOTAL_TEST1 (A1,A2,A2SUBCLASS,A2CONTRI,DENSERNK) values 
(22418,'2286617','CLASS2',0.0115,7);
 Insert into RUNNINGTOTAL_TEST1 (A1,A2,A2SUBCLASS,A2CONTRI,DENSERNK) values 
(22418,'2216868','CLASS2',0.0115,7);
Insert into RUNNINGTOTAL_TEST1 (A1,A2,A2SUBCLASS,A2CONTRI,DENSERNK) values 
(22418,'2221963','CLASS2',0.0115,7);
Insert into RUNNINGTOTAL_TEST1 (A1,A2,A2SUBCLASS,A2CONTRI,DENSERNK) values 
(22418,'2286614','CLASS2',0.0115,7);
Insert into RUNNINGTOTAL_TEST1 (A1,A2,A2SUBCLASS,A2CONTRI,DENSERNK) values 
(22418,'2259004','CLASS2',0.0115,7);
Insert into RUNNINGTOTAL_TEST1 (A1,A2,A2SUBCLASS,A2CONTRI,DENSERNK) values 
(22418,'2245416','CLASS2',0.0115,7);
Insert into RUNNINGTOTAL_TEST1 (A1,A2,A2SUBCLASS,A2CONTRI,DENSERNK) values 
(22418,'2245417','CLASS2',0.0115,7);
Insert into RUNNINGTOTAL_TEST1 (A1,A2,A2SUBCLASS,A2CONTRI,DENSERNK) values 
(22418,'2280925','CLASS2',0.0115,7);
Insert into RUNNINGTOTAL_TEST1 (A1,A2,A2SUBCLASS,A2CONTRI,DENSERNK) values 
(22418,'2243903','CLASS2',0.0125,8);
Insert into RUNNINGTOTAL_TEST1 (A1,A2,A2SUBCLASS,A2CONTRI,DENSERNK) values 
(22418,'2251873','CLASS2',0.014,9);
Insert into RUNNINGTOTAL_TEST1 (A1,A2,A2SUBCLASS,A2CONTRI,DENSERNK) values 
(22418,'2308234','CLASS2',0.0155,10);
Insert into RUNNINGTOTAL_TEST1 (A1,A2,A2SUBCLASS,A2CONTRI,DENSERNK) values 
(22418,'2216702','CLASS2',0.017,11);
Insert into RUNNINGTOTAL_TEST1 (A1,A2,A2SUBCLASS,A2CONTRI,DENSERNK) values 
(22418,'2302378','CLASS2',0.0185,12);
Insert into RUNNINGTOTAL_TEST1 (A1,A2,A2SUBCLASS,A2CONTRI,DENSERNK) values 
(22418,'2264704','CLASS2',0.0225,13);
Insert into RUNNINGTOTAL_TEST1 (A1,A2,A2SUBCLASS,A2CONTRI,DENSERNK) values 
(22418,'2273645','CLASS2',0.027,14);
Insert into RUNNINGTOTAL_TEST1 (A1,A2,A2SUBCLASS,A2CONTRI,DENSERNK) values 
(22418,'2273217','CLASS3',0,1);

我使用的查询计算我的运行总数如下 -

The query I am using the calculate my running total is as below -

SELECT A1,A2,A2SUBCLASS,DENSERNK,A2CONTRI,
SUM(A2CONTRI) OVER (PARTITION BY A1,A2SUBCLASS,DENSERNK ORDER BY A2CONTRI 
asc) AS RUNTOTA2CONTRI
FROM RUNNINGTOTAL_TEST1
ORDER BY A2SUBCLASS ASC;

回应霍根的问题和疑问

推荐答案

默认情况下，分析函数使用 RANGE 窗口指令(见下文 - 默认为第一个版本).您正在寻找的是 ROWS 指令(见下文)，这不是默认值，因此您必须明确包含它.

By default analytic functions use a RANGE windowing directive (see below - the default is the first version). What you are looking for is a ROWS directive (see below), which is not the default so you must include it explicitly.

RANGE 窗口子句(默认)完全符合您的注意事项:它将所有绑定"行视为包含在总和中".

The RANGE windowing clause (default) does exactly what you noticed: it treats all "tied" rows as "included in the sum".

添加:文档链接 https://docs.oracle.com/cd/E11882_01/server.112/e41084/functions004.htm#i97640

with test_data ( x ) as (
       select  10 from dual union all
       select  20 from dual union all
       select  30 from dual union all
       select  30 from dual union all
       select  30 from dual union all
       select 100 from dual
     )
select x
     , sum(x) over (order by x range between unbounded preceding and current row) range_sum
     , sum(x) over (order by x rows  between unbounded preceding and current row) rows_sum
from   test_data
order by x;

         X  RANGE_SUM   ROWS_SUM
---------- ---------- ----------
        10         10         10
        20         30         30
        30        120         60
        30        120         90
        30        120        120
       100        220        220

这篇关于如何使用 SUM 和 OVER 在 Oracle 中获得运行总数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！

查看全文
相关文章
数据库最新文章
热门教程
热门工具
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆