Re: Cumulative running total

From: Gints Plivna <gints.plivna_at_gmail.com>
Date: Fri, 31 Oct 2008 16:55:03 +0200
Message-ID: <6e49b6d00810310755y2dacc588vc8ea9ce4352c072e@mail.gmail.com>

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

```--
http://www.freelists.org/webpage/oracle-l
```
Received on Fri Oct 31 2008 - 09:55:03 CDT

Original text of this message