数据如下,STATIONID是站名,OBSTIME是观测时间,RAINFALL是一分钟雨量,现在要计算每个站每个观测时间前10分钟的累计雨量,并在RAINFALL后加一列RAINFALL10M,把数据插入。如果表中再插入数据,
如5 2013/7/1 0:41:00 2013/7/1 0:41:00 9.00 ,怎么能自动完成上述要求?
小弟刚接触ORACLE,看了很多帖子,还是搞不定,求助各位大神。万分感谢!
STATIONID OBSTIME INSERTTIME RAINFALL
1 2013/7/1 0:01:00 2013/7/1 0:05:00 1.00
1 2013/7/1 0:02:00 2013/7/1 0:05:00 1.00
1 2013/7/1 0:03:00 2013/7/1 0:05:00 1.00
1 2013/7/1 0:04:00 2013/7/1 0:05:00 1.00
1 2013/7/1 0:05:00 2013/7/1 0:05:00 1.00
1 2013/7/1 0:06:00 2013/7/1 0:11:00 2.00
1 2013/7/1 0:07:00 2013/7/1 0:11:00 2.00
1 2013/7/1 0:08:00 2013/7/1 0:11:00 2.00
1 2013/7/1 0:09:00 2013/7/1 0:11:00 2.00
1 2013/7/1 0:10:00 2013/7/1 0:11:00 2.00
2 2013/7/1 0:11:00 2013/7/1 0:17:00 3.00
2 2013/7/1 0:12:00 2013/7/1 0:17:00 3.00
2 2013/7/1 0:13:00 2013/7/1 0:17:00 3.00
2 2013/7/1 0:14:00 2013/7/1 0:17:00 3.00
2 2013/7/1 0:15:00 2013/7/1 0:17:00 3.00
2 2013/7/1 0:16:00 2013/7/1 0:21:00 4.00
2 2013/7/1 0:17:00 2013/7/1 0:21:00 4.00
2 2013/7/1 0:18:00 2013/7/1 0:21:00 4.00
2 2013/7/1 0:19:00 2013/7/1 0:21:00 4.00
2 2013/7/1 0:20:00 2013/7/1 0:21:00 4.00
3 2013/7/1 0:21:00 2013/7/1 0:26:00 5.00
3 2013/7/1 0:22:00 2013/7/1 0:26:00 5.00
3 2013/7/1 0:23:00 2013/7/1 0:26:00 5.00
3 2013/7/1 0:24:00 2013/7/1 0:26:00 5.00
3 2013/7/1 0:25:00 2013/7/1 0:26:00 5.00
3 2013/7/1 0:26:00 2013/7/1 0:30:00 6.00
3 2013/7/1 0:27:00 2013/7/1 0:30:00 6.00
3 2013/7/1 0:28:00 2013/7/1 0:30:00 6.00
3 2013/7/1 0:29:00 2013/7/1 0:30:00 6.00
3 2013/7/1 0:30:00 2013/7/1 0:30:00 6.00
4 2013/7/1 0:31:00 2013/7/1 0:36:00 7.00
4 2013/7/1 0:32:00 2013/7/1 0:36:00 7.00
4 2013/7/1 0:33:00 2013/7/1 0:36:00 7.00
4 2013/7/1 0:34:00 2013/7/1 0:36:00 7.00
4 2013/7/1 0:35:00 2013/7/1 0:36:00 7.00
4 2013/7/1 0:36:00 2013/7/1 0:40:00 8.00
4 2013/7/1 0:37:00 2013/7/1 0:40:00 8.00
4 2013/7/1 0:38:00 2013/7/1 0:40:00 8.00
4 2013/7/1 0:39:00 2013/7/1 0:40:00 8.00
4 2013/7/1 0:40:00 2013/7/1 0:40:00 8.00
累加
如5 2013/7/1 0:41:00 2013/7/1 0:41:00 9.00 ,怎么能自动完成上述要求?
小弟刚接触ORACLE,看了很多帖子,还是搞不定,求助各位大神。万分感谢!
STATIONID OBSTIME INSERTTIME RAINFALL
1 2013/7/1 0:01:00 2013/7/1 0:05:00 1.00
1 2013/7/1 0:02:00 2013/7/1 0:05:00 1.00
1 2013/7/1 0:03:00 2013/7/1 0:05:00 1.00
1 2013/7/1 0:04:00 2013/7/1 0:05:00 1.00
1 2013/7/1 0:05:00 2013/7/1 0:05:00 1.00
1 2013/7/1 0:06:00 2013/7/1 0:11:00 2.00
1 2013/7/1 0:07:00 2013/7/1 0:11:00 2.00
1 2013/7/1 0:08:00 2013/7/1 0:11:00 2.00
1 2013/7/1 0:09:00 2013/7/1 0:11:00 2.00
1 2013/7/1 0:10:00 2013/7/1 0:11:00 2.00
2 2013/7/1 0:11:00 2013/7/1 0:17:00 3.00
2 2013/7/1 0:12:00 2013/7/1 0:17:00 3.00
2 2013/7/1 0:13:00 2013/7/1 0:17:00 3.00
2 2013/7/1 0:14:00 2013/7/1 0:17:00 3.00
2 2013/7/1 0:15:00 2013/7/1 0:17:00 3.00
2 2013/7/1 0:16:00 2013/7/1 0:21:00 4.00
2 2013/7/1 0:17:00 2013/7/1 0:21:00 4.00
2 2013/7/1 0:18:00 2013/7/1 0:21:00 4.00
2 2013/7/1 0:19:00 2013/7/1 0:21:00 4.00
2 2013/7/1 0:20:00 2013/7/1 0:21:00 4.00
3 2013/7/1 0:21:00 2013/7/1 0:26:00 5.00
3 2013/7/1 0:22:00 2013/7/1 0:26:00 5.00
3 2013/7/1 0:23:00 2013/7/1 0:26:00 5.00
3 2013/7/1 0:24:00 2013/7/1 0:26:00 5.00
3 2013/7/1 0:25:00 2013/7/1 0:26:00 5.00
3 2013/7/1 0:26:00 2013/7/1 0:30:00 6.00
3 2013/7/1 0:27:00 2013/7/1 0:30:00 6.00
3 2013/7/1 0:28:00 2013/7/1 0:30:00 6.00
3 2013/7/1 0:29:00 2013/7/1 0:30:00 6.00
3 2013/7/1 0:30:00 2013/7/1 0:30:00 6.00
4 2013/7/1 0:31:00 2013/7/1 0:36:00 7.00
4 2013/7/1 0:32:00 2013/7/1 0:36:00 7.00
4 2013/7/1 0:33:00 2013/7/1 0:36:00 7.00
4 2013/7/1 0:34:00 2013/7/1 0:36:00 7.00
4 2013/7/1 0:35:00 2013/7/1 0:36:00 7.00
4 2013/7/1 0:36:00 2013/7/1 0:40:00 8.00
4 2013/7/1 0:37:00 2013/7/1 0:40:00 8.00
4 2013/7/1 0:38:00 2013/7/1 0:40:00 8.00
4 2013/7/1 0:39:00 2013/7/1 0:40:00 8.00
4 2013/7/1 0:40:00 2013/7/1 0:40:00 8.00
累加
SELECT t1.STATIONID, t1.OBSTIME ,t1.rainfall,sum(t2.rainfall) rainfall10m from table1 t1,table2 t2
WHERE t1.stationid=t2.stationid
and t2.obstime between t1.obstime and t1.obstime+1/24/60;
试了一下,可以得出10分钟累加的雨量,不过想要插入rainfall10m的时候(我用的update table set rainfall10m = ,不知道对不对)提示ORA-00913值过多,求解?
试了一下,可以得出10分钟累加的雨量,不过想要插入rainfall10m的时候(我用的update table set rainfall10m = ,不知道对不对)提示ORA-00913值过多,求解?update table m set rainfall10 = (select ****** from table where table.****=m.***** and table.****=m.*****)PS:3楼那是对的。就一个表
SELECT sum(rainfall) from t1 t2 WHERE t2.obstime between t1.obstime-1/24/60 and t1.obstime and t1.stationid=t2.stationid
)
试了一下,可以得出10分钟累加的雨量,不过想要插入rainfall10m的时候(我用的update table set rainfall10m = ,不知道对不对)提示ORA-00913值过多,求解?update table m set rainfall10 = (select ****** from table where table.****=m.***** and table.****=m.*****)PS:3楼那是对的。就一个表
按照你说的,我这样写不知道对不对,这样的结果是rainfallm10更新的不是10分钟累加的量,只是前面rainfall的一分钟的量。求助,谢谢。
update table m set rainfallm10 =
(select
sum(rainfall ) over
(partition by stationid order by obstime range between 9/1440 preceding and 0 following) rainfallm10 from table
where table.Stationid = m.stationid
and table.Obstime = m.obstime
) ;
试了一下,可以得出10分钟累加的雨量,不过想要插入rainfall10m的时候(我用的update table set rainfall10m = ,不知道对不对)提示ORA-00913值过多,求解?update table m set rainfall10 = (select ****** from table where table.****=m.***** and table.****=m.*****)PS:3楼那是对的。就一个表
按照你说的,我这样写不知道对不对,这样的结果是rainfallm10更新的不是10分钟累加的量,只是前面rainfall的一分钟的量。求助,谢谢。
update table m set rainfallm10 =
(select
sum(rainfall ) over
(partition by stationid order by obstime range between 9/1440 preceding and 0 following) rainfallm10 from table
where table.Stationid = m.stationid
and table.Obstime = m.obstime
) ;
rows between 10 preceding and current row
可以不
试了一下,可以得出10分钟累加的雨量,不过想要插入rainfall10m的时候(我用的update table set rainfall10m = ,不知道对不对)提示ORA-00913值过多,求解?update table m set rainfall10 = (select ****** from table where table.****=m.***** and table.****=m.*****)PS:3楼那是对的。就一个表
按照你说的,我这样写不知道对不对,这样的结果是rainfallm10更新的不是10分钟累加的量,只是前面rainfall的一分钟的量。求助,谢谢。
update table m set rainfallm10 =
(select
sum(rainfall ) over
(partition by stationid order by obstime range between 9/1440 preceding and 0 following) rainfallm10 from table
where table.Stationid = m.stationid
and table.Obstime = m.obstime
) ;
rows between 10 preceding and current row
可以不
行
ROW不一定是按时间顺序排的,用8楼的已经能够实现了,谢谢