# Re: Cumulative running total

From: Wolfgang Breitling <breitliw_at_centrexcc.com>
Date: Fri, 31 Oct 2008 09:57:30 -0600
Message-Id: <200810311557.m9VFvSsD016030@mail95c0.megamailservers.com>

My solution is in line with Gints' except I created a permanent table tst with a row for each unique a, b, year and period:

SQL> select * from tst WHERE YEAR = 2008 and A = 'BV' order by a, b, year, period;

A B YEAR PERIOD
------ ------ ---------- ----------

```BV     AS           2008          1
BV     AS           2008          2
BV     AS           2008          3
BV     AS           2008          4
BV     AS           2008          5
BV     AS           2008          6
BV     AS           2008          7
BV     AS           2008          8
BV     AS           2008          9
BV     AS           2008         10
BV     AS           2008         11
BV     AS           2008         12
BV     AS           2008         13
BV     AS           2008         14
BV     BS           2008          1
BV     BS           2008          2
BV     BS           2008          3
BV     BS           2008          4
BV     BS           2008          5
BV     BS           2008          6
BV     BS           2008          7
BV     BS           2008          8
BV     BS           2008          9
BV     BS           2008         10
BV     BS           2008         11
BV     BS           2008         12
BV     BS           2008         13
BV     BS           2008         14

```

In the real case there are several more columns than just A, B, YEAR and PERIOD and an inline creation of such a table would be very unwieldy. And then the analytic function running total gives me what I need:

select x.A

```, x.B
, x.year
, x.period
, sum(x.amount)
over (partition by x.A, x.B
order by x.A, x.B, x.PERIOD) AMOUNT_SUM
```
from (
select b.a, b.b, b.year, b.period, nvl(a.amount,0) amount FROM test a, tst b
WHERE a.year(+) = b.year
```   and a.b(+) = b.b
and a.a(+) = b.a
and a.period(+) = b.period ) x
```

WHERE x.YEAR = 2008

and x.A = 'BV'
and x.period <= 7
and ( x.amount <> 0 or x.period > 1)
ORDER BY x.period, x.year, x.A, x.B

The "and ( x.amount <> 0 or x.period > 1)" predicate is to eliminate rows without a corresponding entry in the base table for the first period. Currently the program repeated
select a, b, year, {n}, sum(amount) from test where year=nnn and period <= {n} group by a, b, year

with {n} running from 1 through 13. The further into the year we get the longer it takes. I want to replace the 13 select with a single sql which shouldn't take much longer than the currently last one ( period <= 13 ).

At 08:55 AM 10/31/2008, Gints Plivna wrote:
>Not sure whether it is the most performant and elegant way, but it works :)
>
>The main idea is somehow generate necessary a, b, year and period
>combinations (inlene view t1), then left join it to your already done
>calculations (inline view t2) and then pick max value for each row
>(remember that default for window for windowing functions is from the
>first row to current row, that's why max produces correct amount).
>
>SELECT t1.a, t1.alt_b, t1.year, t1.period,
> max(amount_sum) OVER (partition by alt_b ORDER BY t1.period) arr_amount
>FROM (
> (SELECT * FROM (
> SELECT a, year, period, lag(b) OVER (ORDER BY period, a, b) alt_b FROM (
> SELECT a, b, year, period FROM test
> UNION ALL
> SELECT a, b, year, period FROM test
> )
> ORDER BY period, a, alt_b
> )
> WHERE alt_b IS NOT NULL
> ) t1
> LEFT JOIN (
> select A
> , B
> , year
> , period
> , sum(amount)
> over (partition by A, B
> order by A, B, PERIOD) AMOUNT_SUM
> FROM test
> WHERE YEAR = 2008
> and A = 'BV') t2
> ON (t1.alt_b = t2.b AND t1.year = t2.year AND t1.period = t2.period))
>ORDER BY PERIOD, A, alt_b;
>1 BV BS 2008 1 100
>2 BV AS 2008 2 200
>3 BV BS 2008 2 100
>4 BV AS 2008 3 200
>5 BV BS 2008 3 400
>6 BV AS 2008 4 600
>7 BV BS 2008 4 400
>8 BV AS 2008 5 600
>9 BV BS 2008 5 900
>10 BV AS 2008 6 1200
>11 BV BS 2008 6 900
>12 BV AS 2008 7 1200
>13 BV BS 2008 7 1600
>14 BV AS 2008 8 2000
>15 BV BS 2008 8 1600
>16 BV AS 2008 9 2000
>17 BV BS 2008 9 2500
>18 BV AS 2008 10 3000
>19 BV BS 2008 10 2500
>20 BV AS 2008 11 3000
>21 BV BS 2008 11 3600
>22 BV AS 2008 12 4200
>23 BV BS 2008 12 3600
>24 BV AS 2008 13 4200
>25 BV BS 2008 13 4900
>26 BV AS 2008 14 5600
>27 BV BS 2008 14 4900
>
>Gints Plivna
>http://www.gplivna.eu
>
>2008/10/31 Wolfgang Breitling <breitliw_at_centrexcc.com>:
> > I have the following table:
> >
> > Name Null? Type
> > ----------------- -------- ------------
> > A VARCHAR2(6)
> > B VARCHAR2(6)
> > YEAR NOT NULL NUMBER(38)
> > PERIOD NOT NULL NUMBER(38)
> > AMOUNT NOT NULL NUMBER(26,3)
> >
> > and the following content:
> >
> > SQL> select * from test where year = 2008 and a = 'BV';
> >
> > A B YEAR PERIOD AMOUNT
> > ------ ------ ---------- ---------- ----------
> > BV BS 2008 1 100
> > BV AS 2008 2 200
> > BV BS 2008 3 300
> > BV AS 2008 4 400
> > BV BS 2008 5 500
> > BV AS 2008 6 600
> > BV BS 2008 7 700
> > BV AS 2008 8 800
> > BV BS 2008 9 900
> > BV AS 2008 10 1000
> > BV BS 2008 11 1100
> > BV AS 2008 12 1200
> > BV BS 2008 13 1300
> > BV AS 2008 14 1400
> >
> > Note that there are gaps for each B. Here BSs are only present for odd
> > periods, ASs only for even periods ( that was an easy way to simulate it ).
> > What I need is a cumulative running total by period:
> >
> > A B YEAR PERIOD AMOUNT_SUM
> > ------ ------ ---------- ---------- ----------
> > BV BS 2008 1 100
> > BV AS 2008 2 200
> > BV BS 2008 2 100
> > BV AS 2008 3 200
> > BV BS 2008 3 400
> > BV AS 2008 4 600
> > BV BS 2008 4 400
> > BV AS 2008 5 600
> > BV BS 2008 5 900
> > BV AS 2008 6 1200
> > BV BS 2008 6 900
> > BV AS 2008 7 1200
> > BV BS 2008 7 1600
> > ...
> >
> > But when I use the sum() analytic function
> >
> > select A
> > , B
> > , year
> > , period
> > , sum(amount)
> > over (partition by A, B
> > order by A, B, PERIOD) AMOUNT_SUM
> > FROM test
> > WHERE YEAR = 2008
> > and A = 'BV'
> > AND PERIOD <= 7
> > ORDER BY PERIOD, A, B;
> >
> > I only get
> >
> > A B YEAR PERIOD AMOUNT_SUM
> > ------ ------ ---------- ---------- ----------
> > BV BS 2008 1 100
> > BV AS 2008 2 200
> > BV BS 2008 3 400
> > BV AS 2008 4 600
> > BV BS 2008 5 900
> > BV AS 2008 6 1200
> > BV BS 2008 7 1600
> >
> > i.e. for periods where B does not have a value in the base table
> there is no
> > cumulative sum. I need the prior period's cumulative sum carried forward.
> >
> > Anybody have a bright idea how to accomplish that in a single SQL. The
> > database is 9.2.0.6.
> > Maybe wrapping the sql in another analytic function with a window looking
> > back to the prior row.
> >
> >
> > Regards
> >
> > Wolfgang Breitling
> > Centrex Consulting Corporation
> > http://www.centrexcc.com

Regards

Wolfgang Breitling
Centrex Consulting Corporation
http://www.centrexcc.com

```--
http://www.freelists.org/webpage/oracle-l
```
Received on Fri Oct 31 2008 - 10:57:30 CDT

Original text of this message