Postgres 15

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

CREATE TABLE foo (
  id int PRIMARY KEY
, value int NOT NULL
);

INSERT INTO foo
SELECT g, (random() * 10000)::int
FROM   generate_series(1, 100) g;

CREATE TABLE



INSERT 0 100


-- compute delta to previous row (small sample)
SELECT *, value - lag(value) OVER (ORDER BY value) AS delta
FROM   foo
ORDER  BY value
LIMIT  10;


id
value
delta


21
480
null

52
573
93

80
617
44

94
656
39

4
702
46

33
1090
388

30
1108
18

68
1195
87

12
1320
125

86
1678
358



SELECT 10


-- form groups by counting steps > theshold (small sample)
-- threshold being 150 in the example
SELECT count(gap) OVER (ORDER BY value) AS grp, *
FROM  (
   SELECT value, value - lag(value) OVER (ORDER BY value) > 150 OR null AS gap
   FROM   foo
   ORDER  BY value
   LIMIT  10
   ) sub1;


grp
value
gap


0
480
null

0
573
null

0
617
null

0
656
null

0
702
null

1
1090
t

1
1108
null

1
1195
null

1
1320
null

2
1678
t



SELECT 10


-- compute aggregates per group (full set)
SELECT grp, count(*) AS grp_count, round(avg(value), 2) AS grp_avg
FROM  (
   SELECT count(gap) OVER (ORDER BY value) AS grp, *
   FROM  (
      SELECT value, value - lag(value) OVER (ORDER BY value) > 150 OR null AS gap
      FROM   foo
      ) sub1
   ) sub2
GROUP  BY grp
ORDER  BY grp;


grp
grp_count
grp_avg


0
5
605.60

1
4
1178.25

2
4
1747.25

3
3
2193.67

4
9
2612.44

5
3
3209.00

6
2
3550.00

7
1
3905.00

8
6
4248.83

9
1
4537.00

10
1
4810.00

11
4
5013.00

12
20
5826.95

13
1
6439.00

14
12
7057.33

15
1
7543.00

16
1
7717.00

17
5
8030.60

18
4
8517.00

19
4
8899.00

20
9
9474.22



SELECT 21

id	value	delta
21	480	null
52	573	93
80	617	44
94	656	39
4	702	46
33	1090	388
30	1108	18
68	1195	87
12	1320	125
86	1678	358

grp	grp_count	grp_avg
0	5	605.60
1	4	1178.25
2	4	1747.25
3	3	2193.67
4	9	2612.44
5	3	3209.00
6	2	3550.00
7	1	3905.00
8	6	4248.83
9	1	4537.00
10	1	4810.00
11	4	5013.00
12	20	5826.95
13	1	6439.00
14	12	7057.33
15	1	7543.00
16	1	7717.00
17	5	8030.60
18	4	8517.00
19	4	8899.00
20	9	9474.22