如何在PostgreSQL中查询滑动时间窗口内符合条件的行占比

要实现基于时间的滚动窗口内满足field1 > crit的行占比，核心是把你之前用的固定行数窗口（ROWS）换成时间范围窗口（RANGE），再结合条件聚合函数计算占比，具体实现如下：

核心逻辑 #

用CASE语句把符合条件的行标记为1，不符合的标记为0，然后对这个标记值求窗口内的平均值——平均值就是符合条件的行占总窗口行数的比例。同时把窗口范围从固定行数改成基于时间的区间。

示例代码（过去1分钟窗口） #

SELECT 
  timestamp,
  -- 计算窗口内满足条件的行占比
  AVG(CASE WHEN field1 > crit THEN 1.0 ELSE 0.0 END) 
    OVER(
      ORDER BY timestamp 
      RANGE BETWEEN INTERVAL '1 minute' PRECEDING AND CURRENT ROW
    ) AS field1_crit_ratio
FROM schema."table" a;

不同时间窗口的调整 #

只需要修改INTERVAL的参数即可适配不同窗口：

• 过去5小时：INTERVAL '5 hours'
• 过去1天：INTERVAL '1 day'

为什么不用固定行数窗口？ #

你之前用的ROWS BETWEEN 29 PRECEDING AND CURRENT ROW是固定30行的窗口，但如果数据的时间间隔不均匀（比如某段时间数据密集、某段稀疏），这个窗口的实际时间跨度会波动，而用RANGE基于时间的窗口能严格保证是你需要的1分钟/5小时等固定时间范围，更符合需求。

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