Re: Cumulative running total
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-lReceived on Fri Oct 31 2008 - 10:57:30 CDT