如何简化PostgreSQL聚合窗口函数计算站点9点后累计降雨量？

需求说明 #

基于10分钟间隔的记录计算站点当日9点后的累计降雨量，现有嵌套SQL存在冗余，希望提升可维护性。目标是不借助CASE语句，直接通过SUM结合PARTITION BY的单次调用来实现。

当前查询通过CASE生成day_9am_val字段划分窗口：9点整的记录归为前一天累计，9点01分及以后重置累计（从当日9点开始计算），需兼容分钟级数据。原查询如下：

SELECT
    site_name,
    date_time_utc AT TIME ZONE 'Australia/Hobart' AS date_time_local,
    precip_10min,
    precip_since_9am,
    day_9am_val,
    SUM(precip_10min) OVER 
       (PARTITION BY site_name, day_9am_val ORDER BY date_time_utc) AS precip_since_9am_cal
FROM
    (
        SELECT *,
            CASE
                WHEN DATE_PART('hour', date_time_utc AT TIME ZONE 'Australia/Hobart') > 9 THEN (date_time_utc AT TIME ZONE 'Australia/Hobart')::date
                WHEN DATE_PART('hour', date_time_utc AT TIME ZONE 'Australia/Hobart') < 9 THEN ((date_time_utc AT TIME ZONE 'Australia/Hobart')::date - INTERVAL '1 day')::date
                WHEN DATE_PART('minutes', date_time_utc AT TIME ZONE 'Australia/Hobart') > 0 THEN (date_time_utc AT TIME ZONE 'Australia/Hobart')::date
                ELSE ((date_time_utc AT TIME ZONE 'Australia/Hobart')::date - INTERVAL '1 day')::date
            END AS day_9am_val
        FROM
            temp_export
    ) tbl

ORDER BY
    site_name, date_time_utc DESC

优化方案 #

通过时间偏移计算直接生成分区键，替代CASE逻辑，将查询简化为单层级：

SELECT
    site_name,
    date_time_utc AT TIME ZONE 'Australia/Hobart' AS date_time_local,
    precip_10min,
    precip_since_9am,
    -- 直接计算分区日期，替代原CASE生成的day_9am_val
    (date_time_utc AT TIME ZONE 'Australia/Hobart' - INTERVAL '9 hours')::date AS day_9am_val,
    SUM(precip_10min) OVER (
        PARTITION BY site_name, (date_time_utc AT TIME ZONE 'Australia/Hobart' - INTERVAL '9 hours')::date
        ORDER BY date_time_utc
    ) AS precip_since_9am_cal
FROM temp_export
ORDER BY site_name, date_time_utc DESC;

逻辑说明 #

核心技巧是将本地时间提前9小时后取日期，完全匹配原CASE的分区规则：

• 本地时间≥9:01时，减去9小时后仍在当日，分区日期为当日
• 本地时间=9:00时，减去9小时后为前一天00:00，分区日期为前一天
• 本地时间<9:00时，减去9小时后为前一天，分区日期为前一天

示例验证数据 #

内容的提问来源于stack exchange，提问作者samuelf