How to query a Postgresql DB for a fraction of rows fulfilling a criterion in a sliding time window?

Question

This is a follow-up on my previous post How calculate the fraction of rows fulfilling a criterion

I have a time-stamped table of a kind:

timestamp: timestamp
field1: double
...
more fields

What I would like to achieve is a time stamped, rolling fraction of rows fulfilling a criterion in a certain time window. For example, the fraction of rows fulfilling "field1 > crit" in the last minute/5 hours/day.

I already found a way to calculate the average of field1 over a sliding window of rows:

SELECT timestamp, avg(a.field1)
   OVER(ORDER BY a.timestamp ROWS BETWEEN 29 PRECEDING AND CURRENT ROW)
   AS output_name
   FROM schema."table" a;

But I do not manage to extend this to what I need.

Answer 1

You're looking for the range window frame clause: demo

SELECT timestamp, 
       a.field1,
       count(*)filter(where a.field1>5)over w1
        / (count(*)over w1)::float AS "fraction of field1 > crit",
       avg(a.field1)over w1,
       min(a.field1)over w1,
       max(a.field1)over w1
FROM schema1."table1" AS a
WINDOW w1 AS (ORDER BY a.timestamp 
              RANGE BETWEEN '1 hour' PRECEDING 
                        AND CURRENT ROW)
LIMIT 10;

timestamp	field1	fraction of field1 > crit	avg	min	max
2024-02-29 02:52:29.352841	6	1	6	6	6
2024-02-29 03:06:52.759728	2	0.5	4	2	6
2024-02-29 03:10:21.047632	9	0.6666666666666666	5.666666666666667	2	9
2024-02-29 03:37:06.321381	0	0.5	4.25	0	9
2024-02-29 03:45:06.147972	10	0.6	5.4	0	10
2024-02-29 03:48:36.642597	3	0.5	5	0	10
2024-02-29 04:17:40.280552	8	0.5	5.25	0	10
2024-02-29 04:19:19.055374	10	0.6	6.2	0	10
2024-02-29 04:27:43.887064	1	0.5	5.333333333333333	0	10
2024-02-29 04:54:47.489977	9	0.75	7	1	10