当前位置：首页 > article >正文

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

article 2024/12/24 7:45:36

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
(SELECT
		stcd,
    TO_CHAR(tm, 'YYYY-MM-DD HH24') AS time_period, 
    SUM(drp) AS total_value                                       
FROM
    (SELECT
	stcd, tm - INTERVAL '10 minute' AS tm, drp, intv --intv时长字段
FROM
	st_pptn_r 
WHERE
  stcd 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 BY
    stcd, TO_CHAR(tm, 'YYYY-MM-DD HH24')) a
	LEFT JOIN model_engineering_rel_unit_wata_pptn b on a.stcd = b.stcd and b.wscd = 'WEA00000021k0000' AND b.engr_id = 137
ORDER BY
    time_period) c 
		GROUP BY tm 
		ORDER BY tm asc