Re: Cumulative running total
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-lReceived on Fri Oct 31 2008 - 09:55:03 CDT