Dynamic running total

Question

I would like to create a view that returns months of rain amt totals. Each row returned by the view would represent a month for a given year.

In the view I would like to include a column that is the cumulative rain over the year, such that a row for January would have the total rain amt for January of that yr. The rows for Feb would have the rain amt total for Jan+Feb of that yr, rows for Mar would have rain amt total for Jan+Feb+Mar... and so on up to Dec. Dec would have the total rain for the year.

The tough part is I would like to be able to query the view for a specific date ex. "select * from vw_rain_stats where rain_date >= to_date('2010-MAR-01')" and the cumulative sum total column should begin cumulating from the month specified in the where clause (March), not January.

I don't want any monthly rain amts from prior to 2010-MAR-01 to be included in the data returned by the view.

I can develop a view that cumulates amts for the whole yr but I can not figure out how to develop the view so that it can begin cumulating amts for a month other than January.

Given there is data for Jan 2012 thru to Feb 2013 in the underlying table and for each month in that time period there is some rain (no zero rain mths).

create table rain_stats (rain_date date, amt number);

insert into rain_stats values ('2012-JAN-01', 50);
insert into rain_stats values ('2012-FEB-01', 10);
insert into rain_stats values ('2012-MAR-01', 20);
insert into rain_stats values ('2012-APR-01', 40);
insert into rain_stats values ('2012-MAY-01', 30);
insert into rain_stats values ('2012-JUN-01', 10);
insert into rain_stats values ('2012-JUL-01', 4);
insert into rain_stats values ('2012-AUG-01', 100);
insert into rain_stats values ('2012-SEP-01', 5);
insert into rain_stats values ('2012-OCT-01', 100);
insert into rain_stats values ('2012-NOV-01', 90);
insert into rain_stats values ('2012-DEC-01', 80);
insert into rain_stats values ('2013-JAN-01', 30);
insert into rain_stats values ('2013-FEB-01', 7);

The user executes this query

select * from vw_rain_stats where rain_date >= '2012-MAY-01'

This is the result that is returned;

|          RAIN_DATE | AMT | RUNNING_AMT |
------------------------------------------
|      May, 01 2012  |  30 |          30 |
|      Jun, 01 2012  |  10 |          40 |
|      Jul, 01 2012  |   4 |          44 |
|      Aug, 01 2012  | 100 |         144 |
|      Sep, 01 2012  |   5 |         149 |
|      Oct, 01 2012  | 100 |         249 | 
|      Nov, 01 2012  |  90 |         339 |
|      Dec, 01 2011  |  80 |         419 |
|      Jan, 01 2013  |  30 |          30 |
|      Feb, 01 2013  |  70 |         100 |
| .................. | ... | ........... |

Notice that the rain amts for Jan-Apr 2012 are not included in the resultset or in the running_amt cumulative values. This is what I want to happen.

Answer 1

UPDATED Are you looking for this?

CREATE VIEW vw_rain_stats
AS
  SELECT TRUNC(rain_date, 'MM') rain_date,
         SUM(amt) amt
    FROM rain_stats
   GROUP BY TRUNC(rain_date, 'MM')
;

Get monthly amounts for 2012 year

SELECT rain_date,
       amt,
       SUM(amt) OVER (ORDER BY rain_date) running_amt
  FROM vw_rain_stats
 WHERE rain_date BETWEEN '01-JAN-12' AND '01-DEC-12';

Get monthly amounts starting with December 2011

SELECT rain_date,
       amt,
       SUM(amt) OVER (ORDER BY rain_date) running_amt
  FROM vw_rain_stats
 WHERE rain_date >= '01-DEC-11';

Sample output:

|          RAIN_DATE | AMT | RUNNING_AMT |
------------------------------------------
| December, 01 2011  |  80 |          80 |
|  January, 01 2012  |  30 |         110 |
| February, 01 2012  |  70 |         180 |
|    March, 01 2012  | 110 |         290 |
| .................. | ... | ........... |

Here is SQLFiddle demo

Answer 2

I'm not sure if I follow exactly what you're after, but it sounds like a situation where you'd want a Window function. If you add this to your view:

SUM(Rain_Amt) OVER (ORDER BY rain_date) AS cum_Rain

You could get the cumulative rainfall starting from any date, by subtracting the cumulative rainfall of your start date from each subsequent date, something like:

SELECT *, cum_Rain - (SELECT cum_Rain FROM vw_rain_stats WHERE rain_date = '20070405')
FROM vw_rain_stats
WHERE rain_date >= '20070405'

Answer 3

Let me assume that you have an underlying table RainDays which is daily rain amounts.

You would think that what you want is the following view:

create view vw_RainMonths as
    select yyyymm, RainMonth,
            sum(RainMonth) over (order by yyyymm) as cum
    from (select to_char(RainDate as 'YYYY-MM') as yyyymm, sum(Rain) as RainMonth
          from RainDays
          group by to_char(RainDate as 'YYYY-MM')
         ) t

However, this doesn't work, because (as you suspect) it accumulates the values from the beginning of time. The where clause applies to this data.

Oracle does support for table-valued functions, parameterized views, and pipelined table functions -- all of which can act as taking a view and applying a value in an argument. Unfortunately, none of these are as clean as a single view. They require creating a new data type for the return value.