sql - Finding MySQL records that have at least 2 consective timestamp or ID records

Question

I need to run a report that finds the sum of records that have a value in a certain field ( >50 ), but only when there are at least 2 consecutive timestamps. Once the timestamps stop being consecutive, i then need to ignore the until we find the next 2 consecutive.

1  2021-01-26 09:45:58     50
2  2021-01-26 09:47:23     20
3  2021-01-26 09:47:29     50
4  2021-01-26 09:48:23     50

in the example above, The first record would (ID1) would fail (only 1 hit in the required timescale ) ID2 (value too low ) but records 3 and 4 would qualify for inclusion in the sum.

question from:https://stackoverflow.com/questions/65917754/finding-mysql-records-that-have-at-least-2-consective-timestamp-or-id-records

Answer 1

This is a type of gaps-and-islands problem. You can identify the groups by counting the number of non-50+ rows up to each row. Then, aggregate the groups with the conditions you want:

select grp, sum(value)
from (select t.*,
             sum(value < 50) over (order by timestamp) as grp
      from t
     ) t
where value >= 50
group by grp
having count(*) >= 2;

This produces a separate value for each adjacent values. If you want the total sum, then you can use a subquery or CTE based on this query.

If you actually just want the overall sum, you can use lead() and lag():

select sum(value)
from (select t.*,
             lag(value) over (order by timestamp) as prev_value,
             lead(value) over (order by timestamp) as next_value
      from t
     ) t
where value >= 50 and
      (prev_value >= 50 or next_value >= 50)