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 14In 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@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
Regards Wolfgang Breitling Centrex Consulting Corporationhttp://www.centrexcc.com
-- //www.freelists.org/webpage/oracle-l