Postgres 14

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

create table test (rowid serial,device text, t timestamp, v float,suspect bool);

insert into test (device,t,v) values
('a','2022-07-13 10:00:00',0.5),
('b','2022-07-13 10:00:00',0.4),
('a','2022-07-13 11:00:00',0.6),
('b','2022-07-13 11:00:00',0.45),
('a','2022-07-13 12:00:00',0.5),
('b','2022-07-13 12:00:00',0.4),
('a','2022-07-13 13:00:00',0.6),
('b','2022-07-13 13:00:00',0.45),
('a','2022-07-13 14:00:00',99),
('b','2022-07-13 14:00:00',0.3),
('a','2022-07-13 15:00:00',0.5),
('a','2022-07-13 16:00:00',0.6),
('a','2022-07-13 17:00:00',0.4),
('a','2022-07-13 18:00:00',99),
('a','2022-07-13 19:00:00',0.5),
('a','2022-07-13 20:00:00',0.4),
('a','2022-07-13 21:00:00',0.35),
('a','2022-07-13 22:00:00',99),
('a','2022-07-13 23:00:00',0.4),
('a','2022-07-14 00:00:00',0.3),
('a','2022-07-14 01:00:00',0.45),
('a','2022-07-14 02:00:00',0.5),
('a','2022-07-14 03:00:00',0.3);

select * from test order by device,t asc;

CREATE TABLE



INSERT 0 23




rowid
device
t
v
suspect


1
a
2022-07-13 10:00:00
0.5
null

3
a
2022-07-13 11:00:00
0.6
null

5
a
2022-07-13 12:00:00
0.5
null

7
a
2022-07-13 13:00:00
0.6
null

9
a
2022-07-13 14:00:00
99
null

11
a
2022-07-13 15:00:00
0.5
null

12
a
2022-07-13 16:00:00
0.6
null

13
a
2022-07-13 17:00:00
0.4
null

14
a
2022-07-13 18:00:00
99
null

15
a
2022-07-13 19:00:00
0.5
null

16
a
2022-07-13 20:00:00
0.4
null

17
a
2022-07-13 21:00:00
0.35
null

18
a
2022-07-13 22:00:00
99
null

19
a
2022-07-13 23:00:00
0.4
null

20
a
2022-07-14 00:00:00
0.3
null

21
a
2022-07-14 01:00:00
0.45
null

22
a
2022-07-14 02:00:00
0.5
null

23
a
2022-07-14 03:00:00
0.3
null

2
b
2022-07-13 10:00:00
0.4
null

4
b
2022-07-13 11:00:00
0.45
null

6
b
2022-07-13 12:00:00
0.4
null

8
b
2022-07-13 13:00:00
0.45
null

10
b
2022-07-13 14:00:00
0.3
null



SELECT 23


SELECT
 SUM((v = 99)::int) OVER (PARTITION BY device ORDER BY t ASC),
 *
FROM test


sum
rowid
device
t
v
suspect


0
1
a
2022-07-13 10:00:00
0.5
null

0
3
a
2022-07-13 11:00:00
0.6
null

0
5
a
2022-07-13 12:00:00
0.5
null

0
7
a
2022-07-13 13:00:00
0.6
null

1
9
a
2022-07-13 14:00:00
99
null

1
11
a
2022-07-13 15:00:00
0.5
null

1
12
a
2022-07-13 16:00:00
0.6
null

1
13
a
2022-07-13 17:00:00
0.4
null

2
14
a
2022-07-13 18:00:00
99
null

2
15
a
2022-07-13 19:00:00
0.5
null

2
16
a
2022-07-13 20:00:00
0.4
null

2
17
a
2022-07-13 21:00:00
0.35
null

3
18
a
2022-07-13 22:00:00
99
null

3
19
a
2022-07-13 23:00:00
0.4
null

3
20
a
2022-07-14 00:00:00
0.3
null

3
21
a
2022-07-14 01:00:00
0.45
null

3
22
a
2022-07-14 02:00:00
0.5
null

3
23
a
2022-07-14 03:00:00
0.3
null

0
2
b
2022-07-13 10:00:00
0.4
null

0
4
b
2022-07-13 11:00:00
0.45
null

0
6
b
2022-07-13 12:00:00
0.4
null

0
8
b
2022-07-13 13:00:00
0.45
null

0
10
b
2022-07-13 14:00:00
0.3
null



SELECT 23


UPDATE test
SET suspect = (marker_count % 2) = 1
FROM (
  SELECT
    rowid,
    SUM((v = 99)::int) OVER (PARTITION BY device ORDER BY t ASC) AS marker_count
  FROM test
) m
WHERE m.rowid = test.rowid;

SELECT * FROM test ORDER BY device, t ASC;

UPDATE 23




rowid
device
t
v
suspect


1
a
2022-07-13 10:00:00
0.5
f

3
a
2022-07-13 11:00:00
0.6
f

5
a
2022-07-13 12:00:00
0.5
f

7
a
2022-07-13 13:00:00
0.6
f

9
a
2022-07-13 14:00:00
99
t

11
a
2022-07-13 15:00:00
0.5
t

12
a
2022-07-13 16:00:00
0.6
t

13
a
2022-07-13 17:00:00
0.4
t

14
a
2022-07-13 18:00:00
99
f

15
a
2022-07-13 19:00:00
0.5
f

16
a
2022-07-13 20:00:00
0.4
f

17
a
2022-07-13 21:00:00
0.35
f

18
a
2022-07-13 22:00:00
99
t

19
a
2022-07-13 23:00:00
0.4
t

20
a
2022-07-14 00:00:00
0.3
t

21
a
2022-07-14 01:00:00
0.45
t

22
a
2022-07-14 02:00:00
0.5
t

23
a
2022-07-14 03:00:00
0.3
t

2
b
2022-07-13 10:00:00
0.4
f

4
b
2022-07-13 11:00:00
0.45
f

6
b
2022-07-13 12:00:00
0.4
f

8
b
2022-07-13 13:00:00
0.45
f

10
b
2022-07-13 14:00:00
0.3
f



SELECT 23

rowid	device	t	v	suspect
1	a	2022-07-13 10:00:00	0.5	null
3	a	2022-07-13 11:00:00	0.6	null
5	a	2022-07-13 12:00:00	0.5	null
7	a	2022-07-13 13:00:00	0.6	null
9	a	2022-07-13 14:00:00	99	null
11	a	2022-07-13 15:00:00	0.5	null
12	a	2022-07-13 16:00:00	0.6	null
13	a	2022-07-13 17:00:00	0.4	null
14	a	2022-07-13 18:00:00	99	null
15	a	2022-07-13 19:00:00	0.5	null
16	a	2022-07-13 20:00:00	0.4	null
17	a	2022-07-13 21:00:00	0.35	null
18	a	2022-07-13 22:00:00	99	null
19	a	2022-07-13 23:00:00	0.4	null
20	a	2022-07-14 00:00:00	0.3	null
21	a	2022-07-14 01:00:00	0.45	null
22	a	2022-07-14 02:00:00	0.5	null
23	a	2022-07-14 03:00:00	0.3	null
2	b	2022-07-13 10:00:00	0.4	null
4	b	2022-07-13 11:00:00	0.45	null
6	b	2022-07-13 12:00:00	0.4	null
8	b	2022-07-13 13:00:00	0.45	null
10	b	2022-07-13 14:00:00	0.3	null