Re: Cumulative running total
- From: "Michael Moore" <michaeljmoore@xxxxxxxxx>
- To: gints.plivna@xxxxxxxxx
- Date: Fri, 31 Oct 2008 10:20:52 -0700
not sure if this matters, but there is no AS for period 1 in the result set
for this solution.
On Fri, Oct 31, 2008 at 7:55 AM, Gints Plivna <gints.plivna@xxxxxxxxx>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@xxxxxxxxxxxxx>:
> > 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
> --
> //www.freelists.org/webpage/oracle-l
>
>
>
Other related posts:
