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.rain_val,a.duration from (
select a.*, row_number() over(partition by stcd order by rain_val desc) as rn from (
select rtrim(stcd) stcd,sum (drp) over(partition by stcd order by tm range unbounded preceding) as rain_val, 1 as duration
from st_pptn_r where tm>@time@-1/24
and tm<= @time@ group by stcd,drp,tm
) a
) a where a.rn=1
union all
select a.stcd, a.rain_val,a.duration from (
select a.*, row_number() over(partition by stcd order by rain_val desc) as rn from (
select rtrim(stcd) stcd,sum (drp) over(partition by stcd order by tm range unbounded preceding) as rain_val, 3 as duration
from st_pptn_r where tm>@time@-3/24
and tm<= @time@ group by stcd,drp,tm
) a
) a where a.rn=1
union all
select a.stcd, a.rain_val,a.duration from (
select a.*, row_number() over(partition by stcd order by rain_val desc) as rn from (
select rtrim(stcd) stcd,sum (drp) over(partition by stcd order by tm range unbounded preceding) as rain_val, 6 as duration
from st_pptn_r where tm>@time@-6/24
and tm<= @time@ group by stcd,drp,tm
) a
) a where a.rn=1
union all
select a.stcd, a.rain_val,a.duration from (
select a.*, row_number() over(partition by stcd order by rain_val desc) as rn from (
select rtrim(stcd) stcd,sum (drp) over(partition by stcd order by tm range unbounded preceding) as rain_val, 9 as duration
from st_pptn_r where tm>@time@-9/24
and tm<= @time@ group by stcd,drp,tm
) a
) a where a.rn=1
union all
select a.stcd, a.rain_val,a.duration from (
select a.*, row_number() over(partition by stcd order by rain_val desc) as rn from (
select rtrim(stcd) stcd,sum (drp) over(partition by stcd order by tm range unbounded preceding) as rain_val, 12 as duration
from st_pptn_r where tm>@time@-12/24
and tm<= @time@ group by stcd,drp,tm
) a
) a where a.rn=1
union all
select a.stcd, a.rain_val,a.duration from (
select a.*, row_number() over(partition by stcd order by rain_val desc) as rn from (
select rtrim(stcd) stcd,sum (drp) over(partition by stcd order by tm range unbounded preceding) as rain_val, 24 as duration
from st_pptn_r where tm>@time@-24/24
and tm<= @time@ group by stcd,drp,tm
) a
) a where a.rn=1
union all
select a.stcd, a.rain_val,a.duration from (
select a.*, row_number() over(partition by stcd order by rain_val desc) as rn from (
select rtrim(stcd) stcd,sum (drp) over(partition by stcd order by tm range unbounded preceding) as rain_val, 48 as duration
from st_pptn_r where tm>@time@-48/24
and tm<= @time@ group by stcd,drp,tm
) a
) a where a.rn=1
union all
select a.stcd, a.rain_val,a.duration from (
select a.*, row_number() over(partition by stcd order by rain_val desc) as rn from (
select rtrim(stcd) stcd,sum (drp) over(partition by stcd order by tm range unbounded preceding) as rain_val, 72 as duration
from st_pptn_r where tm>@time@-72/24
and tm<= @time@ group by stcd,drp,tm
) a
) a where a.rn=1
union all
select a.stcd, a.rain_val,a.duration from (
select a.*, row_number() over(partition by stcd order by rain_val desc) as rn from (
select rtrim(stcd) stcd,sum (drp) over(partition by stcd order by tm range unbounded preceding) as rain_val, 96 as duration
from st_pptn_r where tm>@time@-96/24
and tm<= @time@ group by stcd,drp,tm
) a
) a where a.rn=1
union all
select a.stcd, a.rain_val,a.duration from (
select a.*, row_number() over(partition by stcd order by rain_val desc) as rn from (
select rtrim(stcd) stcd,sum (drp) over(partition by stcd order by tm range unbounded preceding) as rain_val, 120 as duration
from st_pptn_r where tm>@time@-120/24
and tm<= @time@ group by stcd,drp,tm
) a
) a where a.rn=1
union all
select a.stcd, a.rain_val,a.duration from (
select a.*, row_number() over(partition by stcd order by rain_val desc) as rn from (
select rtrim(stcd) stcd,sum (drp) over(partition by stcd order by tm range unbounded preceding) as rain_val, 144 as duration
from st_pptn_r where tm>@time@-144/24
and tm<= @time@ group by stcd,drp,tm
) a
) a where a.rn=1
union all
select a.stcd, a.rain_val,a.duration from (
select a.*, row_number() over(partition by stcd order by rain_val desc) as rn from (
select rtrim(stcd) stcd,sum (drp) over(partition by stcd order by tm range unbounded preceding) as rain_val, 168 as duration
from st_pptn_r where tm>@time@-168/24
and tm<= @time@ group by stcd,drp,tm
) a
) a where a.rn=1
) a
inner join data b on a.stcd=b.stcd
)
select a.*,b.name,@time@-duration/24 as begin_time, @time@ as end_time from (
select code,duration,area,cast( sum(rain_val)/count(0) as number(9,1) ) as rain_val, cast( sum(rain_val)/count(0)*area*0.1 as number(9,1) ) water_val from real group by code,duration,area
) 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.rain_val,a.duration from (
select a.*, row_number() over(partition by stcd order by rain_val desc) as rn from (
select rtrim(stcd) stcd,sum (drp) over(partition by stcd order by tm range unbounded preceding) as rain_val, 1 as duration
from st_pptn_r where tm>@time@-1/24
and tm<= @time@ group by stcd,drp,tm
) a
) a where a.rn=1
union all
select a.stcd, a.rain_val,a.duration from (
select a.*, row_number() over(partition by stcd order by rain_val desc) as rn from (
select rtrim(stcd) stcd,sum (drp) over(partition by stcd order by tm range unbounded preceding) as rain_val, 3 as duration
from st_pptn_r where tm>@time@-3/24
and tm<= @time@ group by stcd,drp,tm
) a
) a where a.rn=1
union all
select a.stcd, a.rain_val,a.duration from (
select a.*, row_number() over(partition by stcd order by rain_val desc) as rn from (
select rtrim(stcd) stcd,sum (drp) over(partition by stcd order by tm range unbounded preceding) as rain_val, 6 as duration
from st_pptn_r where tm>@time@-6/24
and tm<= @time@ group by stcd,drp,tm
) a
) a where a.rn=1
union all
select a.stcd, a.rain_val,a.duration from (
select a.*, row_number() over(partition by stcd order by rain_val desc) as rn from (
select rtrim(stcd) stcd,sum (drp) over(partition by stcd order by tm range unbounded preceding) as rain_val, 9 as duration
from st_pptn_r where tm>@time@-9/24
and tm<= @time@ group by stcd,drp,tm
) a
) a where a.rn=1
union all
select a.stcd, a.rain_val,a.duration from (
select a.*, row_number() over(partition by stcd order by rain_val desc) as rn from (
select rtrim(stcd) stcd,sum (drp) over(partition by stcd order by tm range unbounded preceding) as rain_val, 12 as duration
from st_pptn_r where tm>@time@-12/24
and tm<= @time@ group by stcd,drp,tm
) a
) a where a.rn=1
union all
select a.stcd, a.rain_val,a.duration from (
select a.*, row_number() over(partition by stcd order by rain_val desc) as rn from (
select rtrim(stcd) stcd,sum (drp) over(partition by stcd order by tm range unbounded preceding) as rain_val, 24 as duration
from st_pptn_r where tm>@time@-24/24
and tm<= @time@ group by stcd,drp,tm
) a
) a where a.rn=1
union all
select a.stcd, a.rain_val,a.duration from (
select a.*, row_number() over(partition by stcd order by rain_val desc) as rn from (
select rtrim(stcd) stcd,sum (drp) over(partition by stcd order by tm range unbounded preceding) as rain_val, 48 as duration
from st_pptn_r where tm>@time@-48/24
and tm<= @time@ group by stcd,drp,tm
) a
) a where a.rn=1
union all
select a.stcd, a.rain_val,a.duration from (
select a.*, row_number() over(partition by stcd order by rain_val desc) as rn from (
select rtrim(stcd) stcd,sum (drp) over(partition by stcd order by tm range unbounded preceding) as rain_val, 72 as duration
from st_pptn_r where tm>@time@-72/24
and tm<= @time@ group by stcd,drp,tm
) a
) a where a.rn=1
union all
select a.stcd, a.rain_val,a.duration from (
select a.*, row_number() over(partition by stcd order by rain_val desc) as rn from (
select rtrim(stcd) stcd,sum (drp) over(partition by stcd order by tm range unbounded preceding) as rain_val, 96 as duration
from st_pptn_r where tm>@time@-96/24
and tm<= @time@ group by stcd,drp,tm
) a
) a where a.rn=1
union all
select a.stcd, a.rain_val,a.duration from (
select a.*, row_number() over(partition by stcd order by rain_val desc) as rn from (
select rtrim(stcd) stcd,sum (drp) over(partition by stcd order by tm range unbounded preceding) as rain_val, 120 as duration
from st_pptn_r where tm>@time@-120/24
and tm<= @time@ group by stcd,drp,tm
) a
) a where a.rn=1
union all
select a.stcd, a.rain_val,a.duration from (
select a.*, row_number() over(partition by stcd order by rain_val desc) as rn from (
select rtrim(stcd) stcd,sum (drp) over(partition by stcd order by tm range unbounded preceding) as rain_val, 144 as duration
from st_pptn_r where tm>@time@-144/24
and tm<= @time@ group by stcd,drp,tm
) a
) a where a.rn=1
union all
select a.stcd, a.rain_val,a.duration from (
select a.*, row_number() over(partition by stcd order by rain_val desc) as rn from (
select rtrim(stcd) stcd,sum (drp) over(partition by stcd order by tm range unbounded preceding) as rain_val, 168 as duration
from st_pptn_r where tm>@time@-168/24
and tm<= @time@ group by stcd,drp,tm
) a
) a where a.rn=1
) a
inner join data b on a.stcd=b.stcd
)
select a.*,b.name,@time@-duration/24 as begin_time, @time@ as end_time from (
select code,duration,area,cast( sum(rain_val)/count(0) as number(9,1) ) as rain_val, cast( sum(rain_val)/count(0)*area*0.1 as number(9,1) ) water_val from real group by code,duration,area
) a inner join (SELECT NAME,CODE FROM data GROUP BY NAME,CODE) b on a.code=b.code
ORDER BY a.code,DURATION