读取流域平均降雨累计值

  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