Finding a local maximum between null values ​​using SQL

Question

I have a table with minute data from an IOT device. Every minute a new line appears with a time stamp and a value that represents the metric. The metric starts at 0 and increases for a while before being reset and starting.

When I draw it, it looks like a picture. I want to find the local maximum value of each run, as the blue circles indicate.

Is it possible to find and group consecutive lines where the metric is> 0, and then find the maximum of each group?

Update

Table structure:

+-------------+------------------+
| Field       | Type             |
+-------------+------------------+
| id          | int(10) unsigned |
| timestamp   | timestamp        |
| metric_name | varchar(32)      |
| value       | int(10)          |
+-------------+------------------+

Answer 1

This is based on the following assumptions:

• Id - ( )
• , 0

:

SELECT *
  FROM metrics m1
 WHERE m.id IN (    
   SELECT m2.id - 1
     FROM metrics m2
    WHERE m1.value = 0)

Answer 2

, , , , 0 .

SELECT  
    value,
    timestamp 
FROM
    metrics 
    LEFT JOIN metrics zeros
        on metrics.time < zeros.time
        and zeros.value = 0
    LEFT JOIN metrics betweenZero
        on metrics.time < betweenZero.time
        and betweenZero.time < zeros.time
    INNER JOIN metrics  noBetweens
        on table.id = noBetweens.id
        and betweenZero.id IS NULL

metric_name, WHERE metric_name = the_metric_name .

Answer 3

1 .

select metric_name, max(value) value, max(start_group) start_time, max(end_group) end_time from(
select metric_name, value,
       case when @prev_ts is not null then @prev_ts end prev_ts,
       case when value = 0 then @ts := timestamp end as start_group,
       @ts as grouping,
       @prev_ts := timestamp end_group
from metric join (select @prev_ts := null as p) prev
order by timestamp
) q
group by metric_name, grouping;

1000 , .

insert into metric(timestamp, metric_name, value)
select now() - interval rn second, 'pressure', v
from(
    select @rn := @rn + 1 rn, mod(1000 - @rn,60) * pow(1000 - mod(@rn,121),1) v
    from   table_with_at_least_1000_rows
    join (select @rn := 0) rn
    limit 1000
) q
;

Answer 4

, , id , . (, id timestamp .) , , . , .

select t0.*
from
    T t0 inner join
    (
        select max_z, max(id) as max_id, max(value) as local_max
        from
            (
            select
                id, value,
                (
                select max(t2.id) as max_id from T t2
                where t2.id < t.id and t2.value = 0
                ) as max_z
            from T t
            where t.value <> 0
            ) p /* partitions */
        group by p.max_z
    ) x /* extrema */
        on t0.id between max_z and max_id and t0.value = x.local_max

Btw , .

http://sqlfiddle.com/#!9/de832/2

Answer 5

:

SELECT 
        T.min_id
        ,T.max_id
        ,MAX(M.value) as local_max
    FROM
        metrics M
        JOIN (
            SELECT
                    id as min_id
                    ,(
                        SELECT MIN(id) FROM Metrics MI
                            WHERE
                                MI.id > MO.id
                                AND MI.value = 0) as max_id

                FROM Metrics MO
                WHERE
                    value = 0
        )T ON M.id BETWEEN T.min_id AND T.max_id
    GROUP BY
        T.min_id, T.max_id