Postgres 11

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE test (
    dev int,
    key text,
    ts int,
    val int
);

INSERT INTO test VALUES 
(1, 'u', 100000, 50),
(1, 'u', 130100, 30050),
(1, 'u', 160100, 60050), -- really single local maximum
(1, 'u', 190200, 100),
(1, 'u', 220200, 30100), -- local maximum together with next value
(1, 'u', 250200, 30100),
(1, 'u', 300000, 300),
(1, 'u', 500000, 100),
(1, 'u', 550000, 1000),  -- on local maximum if only 1 before and 1 after is used
(1, 'u', 600000, 1000),
(1, 'u', 650000, 2000),  -- real local maximum together with 2 next rows
(1, 'u', 700000, 2000),
(1, 'u', 720000, 2000),
(1, 'u', 750000, 300);

SELECT * FROM test;

14 rows affected



dev
key
ts
val


1
u
100000
50

1
u
130100
30050

1
u
160100
60050

1
u
190200
100

1
u
220200
30100

1
u
250200
30100

1
u
300000
300

1
u
500000
100

1
u
550000
1000

1
u
600000
1000

1
u
650000
2000

1
u
700000
2000

1
u
720000
2000

1
u
750000
300


-- solution of Gordon Linoff which works great if values are distinct

select t.*
from (select t.*,
             lead(val) over (partition by dev order by ts) as next_val,
             lag(val) over (partition by dev order by ts) as prev_val
      from test t
      where key = 'u'
     ) t
where val > prev_val and val > next_val;


dev
key
ts
val
next_val
prev_val


1
u
160100
60050
100
30050


-- Postgres 11 feature GROUPS: working for tied local maxima

SELECT 
    dev, key, ts, val 
FROM (
     SELECT
        *, 
        max(val) over (order by sum, val GROUPS BETWEEN 1 PRECEDING AND 1 FOLLOWING) as local_max 
     FROM (
        SELECT 
            *, 
            sum(is_diff) over (order by ts) 
        FROM (
            SELECT 
                *,
                CASE WHEN val = lag(val) over (order by ts) THEN 0 ELSE 1 END as is_diff
            FROM test t
            WHERE key = 'u'
        )s
    )s
)s 
WHERE val = local_max


dev
key
ts
val


1
u
160100
60050

1
u
220200
30100

1
u
250200
30100

1
u
650000
2000

1
u
700000
2000

1
u
720000
2000


-- Same solution but with distinct values (if more than one local maximum chose one randomly)

SELECT DISTINCT ON (val)
    dev, key, ts, val 
FROM (
     SELECT
        *, 
        max(val) over (order by sum, val GROUPS BETWEEN 1 PRECEDING AND 1 FOLLOWING) as local_max
     FROM (
        SELECT 
            *, 
            sum(is_diff) over (order by ts) 
        FROM (
            SELECT 
                *,
                CASE WHEN val = lag(val) over (order by ts) THEN 0 ELSE 1 END as is_diff
            FROM test t
            WHERE key = 'u'
        )s
    )s
)s 
WHERE val = local_max


dev
key
ts
val


1
u
650000
2000

1
u
220200
30100

1
u
160100
60050
dev	key	ts	val
1	u	100000	50
1	u	130100	30050
1	u	160100	60050
1	u	190200	100
1	u	220200	30100
1	u	250200	30100
1	u	300000	300
1	u	500000	100
1	u	550000	1000
1	u	600000	1000
1	u	650000	2000
1	u	700000	2000
1	u	720000	2000
1	u	750000	300