PostgreSQL中按15分钟间隔统计数字水表用量的SQL查询实现

无需在应用程序中编写额外逻辑，利用PostgreSQL的时间函数、generate_series和窗口函数即可完成15分钟间隔的水表用量统计，具体实现如下：

完整SQL语句 #

WITH time_intervals AS (
    -- 生成覆盖数据时间范围的所有15分钟间隔时间点
    SELECT generate_series(
        -- 起始点：小于等于最早记录时间的最近15分钟整点
        date_trunc('hour', min(timestamp)) + interval '15 minutes' * floor(date_part('minute', min(timestamp)) / 15),
        -- 结束点：大于等于最晚记录时间的最近15分钟整点
        date_trunc('hour', max(timestamp)) + interval '15 minutes' * ceil(date_part('minute', max(timestamp)) / 15),
        interval '15 minutes'
    ) AS interval_end
),
interval_totals AS (
    -- 每个15分钟时间点截止的最大累计用水量
    SELECT
        ti.interval_end,
        max(wm.total_m3) AS max_total
    FROM time_intervals ti
    LEFT JOIN water_meter wm ON wm.timestamp <= ti.interval_end
    GROUP BY ti.interval_end
)
-- 计算每个15分钟区间的用水量
SELECT
    interval_end,
    round(max_total - lag(max_total) OVER (ORDER BY interval_end), 3) AS usage_m3
FROM interval_totals
-- 过滤无前置区间的起始点（无历史数据无法计算用量）
WHERE lag(max_total) OVER (ORDER BY interval_end) IS NOT NULL
-- 过滤无用量的区间（可选）
AND max_total - lag(max_total) OVER (ORDER BY interval_end) > 0
ORDER BY interval_end DESC;

逻辑说明 #

1. 生成时间区间：generate_series自动生成所有需要的15分钟整点时间点，确保覆盖所有水表记录的时间范围。
2. 获取区间累计最大值：通过左关联水表数据，取每个时间点截止的最大total_m3（因total_m3是递增的，该值即为该区间结束时的最终累计值）。
3. 计算区间用量：使用LAG窗口函数获取上一个15分钟区间的累计最大值，两者的差值就是当前区间的用水量，用round保证小数精度。

示例运行结果 #

针对你提供的输入数据，执行上述SQL后会输出：

2023-05-11 21:00:00.000000, 0.4
2023-05-11 20:45:00.000000, 0.25

完全符合预期结果。

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