Postgres 16

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

select setseed(.42);

create schema schema1;
create table schema1.table1(field1 float, timestamp timestamp);

insert into schema1.table1 select (random()*10)::int,now()-random()*interval '10 hours'
from generate_series(1,40);
/*
SELECT timestamp, avg(a.field1)
   OVER(ORDER BY a.timestamp ROWS BETWEEN 29 PRECEDING AND CURRENT ROW)
   AS output_name
   FROM schema1."table1" a;*/


setseed






SELECT 1



CREATE SCHEMA



CREATE TABLE



INSERT 0 40


SELECT timestamp, 
       a.field1,
       avg(a.field1)over w1 AS output_name,
       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
output_name
min
max


2024-02-29 02:52:29.352841
6
6
6
6

2024-02-29 03:06:52.759728
2
4
2
6

2024-02-29 03:10:21.047632
9
5.666666666666667
2
9

2024-02-29 03:37:06.321381
0
4.25
0
9

2024-02-29 03:45:06.147972
10
5.4
0
10

2024-02-29 03:48:36.642597
3
5
0
10

2024-02-29 04:17:40.280552
8
5.25
0
10

2024-02-29 04:19:19.055374
10
6.2
0
10

2024-02-29 04:27:43.887064
1
5.333333333333333
0
10

2024-02-29 04:54:47.489977
9
7
1
10



SELECT 10


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



SELECT 10

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