with data as (
select rtrim(b.stcd) as stcd,a.vareasize as area,a.vname as name,a.vcode as code
from SS_MESHING_GRID_VILLAGE a
inner join ss_stationextend b on a.vcode=trim(b.adcd)
where b.strongtype='PP'
), real as (
select a.*,b.area,b.name,b.code from (
select a.stcd,a.drp,a.duration,a.begin_time from (
select rtrim(stcd) stcd,drp, to_char(tm, 'yyyy/MM/dd hh24') as duration,
to_date(to_char(tm, 'yyyy/MM/dd hh24')||':00:00','yyyy/mm/dd hh24:mi:ss') as begin_time
from st_pptn_r where tm>@begin_time@
and tm<=@end_time@
and (intv='1.00' or intv is null) group by stcd,drp,tm
) a
) a
inner join data b on a.stcd=b.stcd
)
select a.*,b.name, @end_time@ as end_time
from (
select code,duration,begin_time,area,stcd_count,zslj,rain_val,cast( rain_val*area*0.1 as number(9,1) ) water_val from(
select cast(sum(rain_val)over(partition by code order by duration range unbounded preceding) as number(9,1) ) as rain_val,
code,begin_time,duration,area,stcd_count,zslj from (
select code,duration,area,stcd_count,begin_time,zslj,cast( zslj/stcd_count as number(9,1) ) as rain_val
from(
select t2.stcd_count,t1.code,t1.duration,t1.begin_time,t1.area,sum(t1.drp)over(partition by t1.duration order by t1.duration range unbounded preceding) as zslj
from real t1
inner join
(select count(stcd) as stcd_count,code,duration from real group by code,duration,begin_time) t2 on t1.code=t2.code and t1.duration=t2.duration
) group by code,duration,area,zslj,stcd_count,begin_time
)
)
) a inner join (SELECT NAME,CODE FROM data GROUP BY NAME,CODE) b on a.code=b.code
ORDER BY a.code,DURATION
select rtrim(b.stcd) as stcd,a.vareasize as area,a.vname as name,a.vcode as code
from SS_MESHING_GRID_VILLAGE a
inner join ss_stationextend b on a.vcode=trim(b.adcd)
where b.strongtype='PP'
), real as (
select a.*,b.area,b.name,b.code from (
select a.stcd,a.drp,a.duration,a.begin_time from (
select rtrim(stcd) stcd,drp, to_char(tm, 'yyyy/MM/dd hh24') as duration,
to_date(to_char(tm, 'yyyy/MM/dd hh24')||':00:00','yyyy/mm/dd hh24:mi:ss') as begin_time
from st_pptn_r where tm>@begin_time@
and tm<=@end_time@
and (intv='1.00' or intv is null) group by stcd,drp,tm
) a
) a
inner join data b on a.stcd=b.stcd
)
select a.*,b.name, @end_time@ as end_time
from (
select code,duration,begin_time,area,stcd_count,zslj,rain_val,cast( rain_val*area*0.1 as number(9,1) ) water_val from(
select cast(sum(rain_val)over(partition by code order by duration range unbounded preceding) as number(9,1) ) as rain_val,
code,begin_time,duration,area,stcd_count,zslj from (
select code,duration,area,stcd_count,begin_time,zslj,cast( zslj/stcd_count as number(9,1) ) as rain_val
from(
select t2.stcd_count,t1.code,t1.duration,t1.begin_time,t1.area,sum(t1.drp)over(partition by t1.duration order by t1.duration range unbounded preceding) as zslj
from real t1
inner join
(select count(stcd) as stcd_count,code,duration from real group by code,duration,begin_time) t2 on t1.code=t2.code and t1.duration=t2.duration
) group by code,duration,area,zslj,stcd_count,begin_time
)
)
) a inner join (SELECT NAME,CODE FROM data GROUP BY NAME,CODE) b on a.code=b.code
ORDER BY a.code,DURATION