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

复杂SQL查询解析

最新推荐文章于 2024-08-14 19:18:20 发布

原创最新推荐文章于 2024-08-14 19:18:20 发布 · 1.2k 阅读

2 ·

CC 4.0 BY-SA版权

SQL 专栏收录该内容

11 篇文章

订阅专栏

本文介绍了一个复杂的SQL查询案例，该查询涉及多个表的连接、分组、子查询及窗口函数等高级特性，用于处理气象站降水数据并进行区域降雨量统计。

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