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

1.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

2.SQL语句实现：将水库、水文站、水闸按照分钟时间插值到小时

小于30分钟，为当前整点；大于30分钟，为+1小时的整点；并进行数据去重。

WITH tableaaa AS (
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY stcd, tm ORDER BY (SELECT NULL)) AS rownum
    FROM (SELECT stcd, rz,
w,
inq,
blrz,
otq, update_time,
CASE
             WHEN EXTRACT(MINUTE FROM tm) >= 30 THEN DATE_TRUNC('hour', tm) + INTERVAL '1 hour'
             ELSE DATE_TRUNC('hour', tm)
         END as tm
                                        from ST_rsvr_R_hi) a)
SELECT * FROM tableaaa WHERE rownum = 1;


--水闸
WITH tableaaa AS (
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY stcd, tm ORDER BY (SELECT NULL)) AS rownum
    FROM (SELECT stcd, upz,
dwz,
tgtq,
update_time,
CASE
             WHEN EXTRACT(MINUTE FROM tm) >= 30 THEN DATE_TRUNC('hour', tm) + INTERVAL '1 hour'
             ELSE DATE_TRUNC('hour', tm)
         END as tm
                                        from ST_was_R_hi) a)
SELECT * FROM tableaaa WHERE rownum = 1;