with data as (
select rtrim(a.wcpcode) as stcd,b.lareasize as area,lname as name,lcode as code from SS_MESHING_GRIDSLGC_RELATION a
inner join SS_MESHING_GRID_BASIN b on a.ennmcd = b.lcode
where wcptype in (5,6)
), 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_char(tm, 'yyyy/MM/dd hh24')||':00:00' 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(a.wcpcode) as stcd,b.lareasize as area,lname as name,lcode as code from SS_MESHING_GRIDSLGC_RELATION a
inner join SS_MESHING_GRID_BASIN b on a.ennmcd = b.lcode
where wcptype in (5,6)
), 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_char(tm, 'yyyy/MM/dd hh24')||':00:00' 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