动态运行总计 [英] Dynamic running total
问题描述
我想创建一个视图，该视图返回数月的降雨总数.视图返回的每一行将代表给定年份的一个月.
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.
在视图中，我想添加一列，该列是一年中的累计降雨，这样一月的行将具有该年一月的总降雨量. 2月的行将具有该年Jan+Feb的降雨总量，3月的行将具有Jan+Feb+Mar的降雨总量...依此类推，直到12月，该年的降雨总量才是.
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.
困难的部分是我希望能够查询特定日期的视图
前任. "select * from vw_rain_stats where rain_date >= to_date('2010-MAR-01')"和累积总计列应从where子句(3月)中指定的月份开始，而不是从1月开始累计.
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.
我不希望2010年3月1日之前的每月降雨都包含在该视图返回的数据中.
I don't want any monthly rain amts from prior to 2010-MAR-01 to be included in the data returned by the view.
我可以开发一个视图，该视图可以在整个年度内累计amts，但是我无法弄清楚如何开发该视图，以便它可以开始对除1月以外的一个月的amts进行累计.
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.
鉴于基础表中有2012年1月至2013年2月的数据，并且该时间段内的每个月都有降雨(没有零的降雨时间).
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);
用户执行此查询
select * from vw_rain_stats where rain_date >= '2012-MAY-01'
这是返回的结果；
| 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 |
| .................. | ... | ........... |
请注意，结果集中或running_amt累积值中未包含2012年1月至4月的降雨面积.这就是我想发生的事情.
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.
推荐答案
更新?
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')
;
获取2012年的月度金额
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';
从2011年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';
示例输出:
| 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 |
| .................. | ... | ........... |
这里是 SQLFiddle 演示
Here is SQLFiddle demo
这篇关于动态运行总计的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
