Re: Cumulative running total

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
--
http://www.freelists.org/webpage/oracle-l

Other related posts: