我的创作纪念日

快速插值数据到小时ST_was_R_hi

小于30分钟的向前插值到整点；大于30分钟的向后插值到整点。

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;

快速插值数据到小时ST_RIVER_R_hi

WITH tableaaa AS (
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY stcd, tm ORDER BY (SELECT NULL)) AS rownum
    FROM (SELECT stcd, z, q, xsavv, 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_RIVER_R_hi) a)
SELECT * FROM tableaaa WHERE rownum = 1;

快速插值数据到小时ST_rsvr_R_hi

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;