Re: Cumulative running total
- From: "Michael Moore" <michaeljmoore@xxxxxxxxx>
- To: gints.plivna@xxxxxxxxx
- Date: Fri, 31 Oct 2008 10:23:06 -0700
let me try again...
not sure if this matters, but there is no AS for period 1 in the result set
for Gint's first solution.
Mike
On Fri, Oct 31, 2008 at 10:20 AM, Michael Moore <michaeljmoore@xxxxxxxxx>wrote:
> 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:
