postgreSql对分钟级的降雨数据进行插值为整小时

2024/12/25 1:10:00 来源：https://blog.csdn.net/yinchoushi8780/article/details/144661977 浏览: 次关键词：postgreSql对分钟级的降雨数据进行插值为整小时

postgreSql对分钟级的降雨数据进行插值为整小时

SQL语句实现

SQL语句实现

 --核查某个小流域的降雨量小时插值是否正确SELECT tm, sum(drp) as sum, round(sum(drp), 2) as drp2 from(SELECT a.stcd, (TO_TIMESTAMP(time_period, 'YYYY-MM-DD HH24:MI:SS') + INTERVAL '1 HOUR') as tm, total_value,b.pw, (total_value * b.pw) as drp from
(SELECTstcd,TO_CHAR(tm, 'YYYY-MM-DD HH24') AS time_period, SUM(drp) AS total_value                                       
FROM(SELECTstcd, tm - INTERVAL '10 minute' AS tm, drp, intv --intv时长字段
FROMst_pptn_r 
WHEREstcd IN ( SELECT stcd FROM model_engineering_rel_unit_wata_pptn WHERE wscd = 'WEA00000021k0000' AND engr_id = 137 ) AND tm >= '2024-07-10 08:00:00' AND tm <= '2024-07-17 08:00:00' ) T
GROUP BYstcd, TO_CHAR(tm, 'YYYY-MM-DD HH24')) aLEFT JOIN model_engineering_rel_unit_wata_pptn b on a.stcd = b.stcd and b.wscd = 'WEA00000021k0000' AND b.engr_id = 137
ORDER BYtime_period) c GROUP BY tm ORDER BY tm asc