获取流域每隔1小时累计平均降雨量

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