oracleoracle12ccumulative-sum

Get 12 month running total for subgroups in Oracle


I'm trying to get a running 12 month total for data, but changing when values in my fields change. I've made a simpler sql fiddle, for a 3 month running total, for one value in the table. I'm stuck on two parts: 1) how to get the running total, but have it change each time my field values change, and 2) how to then write that running total back to my table. (Or, I guess I could have a 2nd table and write to that instead. Yeah, that's probably easier.)

-- INIT database
CREATE TABLE Product (
  Bill_Date         DATE,
  LGroup            VARCHAR2(250 BYTE),
  District          VARCHAR2(50 BYTE),
  MonthTot          NUMBER(15,4),
  Last12Tot         NUMBER(18,4)
);

INSERT INTO Product(Bill_Date, LGroup, District, MonthTot) VALUES (to_date('05/01/2022','MM/DD/YYYY'), 'Sam','West', 13);
INSERT INTO Product(Bill_Date, LGroup, District, MonthTot) VALUES (to_date('06/01/2022','MM/DD/YYYY'), 'Sam','West', 10);
INSERT INTO Product(Bill_Date, LGroup, District, MonthTot) VALUES (to_date('07/01/2022','MM/DD/YYYY'), 'Sam','West', 11);
INSERT INTO Product(Bill_Date, LGroup, District, MonthTot) VALUES (to_date('08/01/2022','MM/DD/YYYY'), 'Sam','West', 8);
INSERT INTO Product(Bill_Date, LGroup, District, MonthTot) VALUES (to_date('05/01/2022','MM/DD/YYYY'), 'Chi','West', 21);
INSERT INTO Product(Bill_Date, LGroup, District, MonthTot) VALUES (to_date('06/01/2022','MM/DD/YYYY'), 'Chi','West', 19);
INSERT INTO Product(Bill_Date, LGroup, District, MonthTot) VALUES (to_date('07/01/2022','MM/DD/YYYY'), 'Chi','West', 15);
INSERT INTO Product(Bill_Date, LGroup, District, MonthTot) VALUES (to_date('08/01/2022','MM/DD/YYYY'), 'Chi','West', 11);
INSERT INTO Product(Bill_Date, LGroup, District, MonthTot) VALUES (to_date('05/01/2022','MM/DD/YYYY'), 'Emi','East', 8);
INSERT INTO Product(Bill_Date, LGroup, District, MonthTot) VALUES (to_date('06/01/2022','MM/DD/YYYY'), 'Emi','East', 5);
INSERT INTO Product(Bill_Date, LGroup, District, MonthTot) VALUES (to_date('07/01/2022','MM/DD/YYYY'), 'Emi','East', 6);
INSERT INTO Product(Bill_Date, LGroup, District, MonthTot) VALUES (to_date('08/01/2022','MM/DD/YYYY'), 'Emi','East', 4);

-- QUERY database
SELECT * FROM Product;
-- this will do a running total for one group
SELECT bill_date, LGroup, District, MonthTot
     , sum(MonthTot) over (order by bill_date, LGroup, District rows between 2 preceding and current row) as Last12Tot
from (
  select bill_date, LGroup, District, MonthTot
  from Product
  where LGroup = 'Sam'
      )
order by LGroup, District, bill_date
;

So my data set will look something like this:

BILL_DATE   LGROUP  DISTRICT    MONTHTOT
01-MAY-22   Sam     West        13   
01-JUN-22   Sam     West        10   
01-JUL-22   Sam     West        11   
01-AUG-22   Sam     West        8    
01-MAY-22   Chi     West        21   
01-JUN-22   Chi     West        19   
01-JUL-22   Chi     West        15   
01-AUG-22   Chi     West        11   
01-MAY-22   Emi     East        8    
01-JUN-22   Emi     East        5    
01-JUL-22   Emi     East        6    
01-AUG-22   Emi     East        4   

My current query returns this:

BILL_DATE   LGROUP  DISTRICT    MONTHTOT    LAST12TOT
01-MAY-22   Sam     West        13          13
01-JUN-22   Sam     West        10          23
01-JUL-22   Sam     West        11          34
01-AUG-22   Sam     West        8           29

I will not have one LGroup in multiple Districts, so LGroup is essentially what I need to group by. There isn't a primary key - it's already data assembled from a query to get the totals for each month for each person and district.

Every time I try to get all the groups, my partitioning is not working at all. I have multiple years and want a 12 month rolling total, but only within each group.

Once I have this, I can just sum my group totals to get district totals.


Solution

  • Use PARTITION BY and a RANGE window:

    SELECT bill_date,
           LGroup,
           District,
           MonthTot,
           sum(MonthTot) over (
             PARTITION BY LGroup
             ORDER BY bill_date
             RANGE BETWEEN INTERVAL '11' MONTH PRECEDING AND CURRENT ROW
           ) AS Last12Tot
    FROM   Product
    order by LGroup, District, bill_date;
    

    Which, for the sample data, outputs:

    BILL_DATE LGROUP DISTRICT MONTHTOT LAST12TOT
    2022-05-01 00:00:00 Chi West 21 21
    2022-06-01 00:00:00 Chi West 19 40
    2022-07-01 00:00:00 Chi West 15 55
    2022-08-01 00:00:00 Chi West 11 66
    2022-05-01 00:00:00 Emi East 8 8
    2022-06-01 00:00:00 Emi East 5 13
    2022-07-01 00:00:00 Emi East 6 19
    2022-08-01 00:00:00 Emi East 4 23
    2022-05-01 00:00:00 Sam West 13 13
    2022-06-01 00:00:00 Sam West 10 23
    2022-07-01 00:00:00 Sam West 11 34
    2022-08-01 00:00:00 Sam West 8 42

    fiddle