2014-05-23 对气候表进行了一些初始处理备忘
2014-05-23 09:31:35| 分类:
Oracle
| 标签:
|举报
|字号大中小 订阅
问题:
发现许多站点的气候特征(最大,最小、平均)数据没有更新。
原因:
用语句
select s.nm, a.section_id,a.create_time, a.hour_id, a.p from vw_ele_statistics a,stations s
where a.section_id=s.sta and a.create_time=to_date('1904-01-19','YYYY-MM-DD') and a.stat='最大' and a.p=-9999;
得到:
NM SECTION_ID CREATE_TIME HOUR_ID P
-------------------- ---------------------- ------------------------- ---------------------- ----------------------
xxx xxx 19-1月 -04 24 -9999
xxx xxx 19-1月 -04 24 -9999
xxx xxx 19-1月 -04 24 -9999
xxx xxx 19-1月 -04 24 -9999
xxx xxx 19-1月 -04 24 -9999
这些站点都是一些临时添加使用,以后又没有持续的站点
==============================================================
update ele_statistics set p=-9999 where p is null and stat='最大';
update ele_statistics set t=-9999 where t is null and stat='最大';
update ele_statistics set tmin=-9999 where tmin is null and stat='最大';
update ele_statistics set tmax=-9999 where tmax is null and stat='最大';
update ele_statistics set t0=-9999 where t0 is null and stat='最大';
update ele_statistics set t0min=-9999 where t0min is null and stat='最大';
update ele_statistics set t0max=-9999 where t0max is null and stat='最大';
update ele_statistics set t5=-9999 where t5 is null and stat='最大';
update ele_statistics set t10=-9999 where t10 is null and stat='最大';
update ele_statistics set t15=-9999 where t15 is null and stat='最大';
update ele_statistics set t20=-9999 where t20 is null and stat='最大';
update ele_statistics set t40=-9999 where t40 is null and stat='最大';
update ele_statistics set e=-9999 where e is null and stat='最大';
update ele_statistics set u=-9999 where u is null and stat='最大';
update ele_statistics set umin=-9999 where umin is null and stat='最大';
update ele_statistics set eva=-9999 where eva is null and stat='最大';
update ele_statistics set v=-9999 where v is null and stat='最大';
update ele_statistics set vmin=-9999 where vmin is null and stat='最大';
update ele_statistics set n=-9999 where n is null and stat='最大';
update ele_statistics set nlow=-9999 where nlow is null and stat='最大';
update ele_statistics set r0012=-9999 where r0012 is null and stat='最大';
update ele_statistics set r1200=-9999 where r1200 is null and stat='最大';
update ele_statistics set r2400=-9999 where r2400 is null and stat='最大';
update ele_statistics set r2412=-9999 where r2412 is null and stat='最大';
commit;
update ele_statistics set p=9999 where p is null and stat='最小';
update ele_statistics set t=9999 where t is null and stat='最小';
update ele_statistics set tmin=9999 where tmin is null and stat='最小';
update ele_statistics set tmax=9999 where tmax is null and stat='最小';
update ele_statistics set t0=9999 where t0 is null and stat='最小';
update ele_statistics set t0min=9999 where t0min is null and stat='最小';
update ele_statistics set t0max=9999 where t0max is null and stat='最小';
update ele_statistics set t5=9999 where t5 is null and stat='最小';
update ele_statistics set t10=9999 where t10 is null and stat='最小';
update ele_statistics set t15=9999 where t15 is null and stat='最小';
update ele_statistics set t20=9999 where t20 is null and stat='最小';
update ele_statistics set t40=9999 where t40 is null and stat='最小';
update ele_statistics set e=9999 where e is null and stat='最小';
update ele_statistics set u=9999 where u is null and stat='最小';
update ele_statistics set umin=9999 where umin is null and stat='最小';
update ele_statistics set eva=9999 where eva is null and stat='最小';
update ele_statistics set v=9999 where v is null and stat='最小';
update ele_statistics set vmin=9999 where vmin is null and stat='最小';
update ele_statistics set n=9999 where n is null and stat='最小';
update ele_statistics set nlow=9999 where nlow is null and stat='最小';
update ele_statistics set r0012=9999 where r0012 is null and stat='最小';
update ele_statistics set r1200=9999 where r1200 is null and stat='最小';
update ele_statistics set r2400=9999 where r2400 is null and stat='最小';
update ele_statistics set r2412=9999 where r2412 is null and stat='最小';
commit;
select * from ele_statistics where t is null and stat='最大'
select * from ele_statistics where t is null and stat='最小'
declare
ct date := to_date('2014-04-19','YYYY-MM-DD');
h_id number := 12;
begin
--ele_hour2day(ct,h_id);
stat_day_ten_mon(ct,h_id);
end;
先测试一下日期变量
set serveroutput on
declare
ct date := to_date('2014-01-09','YYYY-MM-DD');
h_id number := 12;
d date;
begin
for i in 1..365 loop
select ct+i into d from dual;
DBMS_OUTPUT.PUT_LINE(d);
end loop;
end;
------------------------------------------------------------------------------------
使用循环统计
declare
ct date := to_date('2014-01-09','YYYY-MM-DD');
h_id number := 12;
d date;
begin
for i in 1..360 loop
select ct+i into d from dual;
stat_day_ten_mon(d,h_id);
end loop;
end;
====================================================================
检验:
select * from vw_ele_statistics where create_time=to_date('1904-01-05','YYYY-MM-DD') and stat='最大' and p=-9999;
检验结果:
统计更新不起作用。:-(
===================================================================
create or replace
procedure stat_day_ten_mon(ct in date,h_id in number) as
d number := -1;
dd date;
begin
if h_id = 12 then
d := toD(ct);
dd := toYYYY_MM_DD(ct);
--日气候常量统计
ele_stat_day(ct);
--旬气候常量统计
if d=10 or d=20 or dd=last_day(ct) then
ele_stat_ten(ct);
end if;
--月气候常量统计
if dd=last_day(ct) then
ele_stat_mon(ct);
end if;
end if;
stat_rain_day_ten_mon(ct,h_id);
end;
评论这张
转发至微博
转发至微博
评论