How to get running total in Oracle using SUM and OVER

问题

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

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);

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

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;

回应霍根的问题和他的询问

回答1

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

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

添加:链接到文档 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
回答2

这个问题是关于 Oracle 的,我在下面的回答是基于 DB2 OLAP 实现的——它有不同的默认值。 有关正确的 Oracle 语法,请参阅@mathguy 的答案。

如果您想要一个运行总和,请不要使用分区,除非在您希望运行总和“跨度”的范围内,然后它将按部分顺序排序,这在这个问题中是无效或不够具体的,它不清楚您使用的是什么顺序——我在下面猜测——应该很接近。

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

更多相关内容:请点击查看