MySQL returns first and last record for consecutive identical results
I am using MySQL and have a table called "results" that stores a monitor result that determines whether the service will be up or down at a specific time.
+-----------+------------+---------------------+--------+
| result_id | service_id | time_stamp | result |
+-----------+------------+---------------------+--------+
| 1 | 1 | 0000-00-00 00:01:00 | down |
| 2 | 1 | 0000-00-00 00:02:00 | up |
| 3 | 1 | 0000-00-00 00:03:00 | up |
| 4 | 1 | 0000-00-00 00:04:00 | up |
| 5 | 1 | 0000-00-00 00:05:00 | down |
| 6 | 1 | 0000-00-00 00:06:00 | down |
| 7 | 1 | 0000-00-00 00:07:00 | up |
| 8 | 1 | 0000-00-00 00:08:00 | down |
| 9 | 1 | 0000-00-00 00:09:00 | up |
| 10 | 2 | 0000-00-00 00:03:00 | up |
+-----------+------------+---------------------+--------+
I want to get a results table that looks at the results for a given service and returns the time during which it was first recorded, both down and last (sequentially), and vice versa for that. This will help me record the duration of the service downtime.
The results I'm looking for are as follows.
For service_id 1 ...
+-----------------------+---------------------+--------+
| start_time | end_time | result |
+-----------------------+---------------------+--------+
| 0000-00-00 00:01:00 | 0000-00-00 00:01:00 | down |
| 0000-00-00 00:02:00 | 0000-00-00 00:04:00 | up |
| 0000-00-00 00:05:00 | 0000-00-00 00:06:00 | down |
| 0000-00-00 00:07:00 | 0000-00-00 00:07:00 | up |
| 0000-00-00 00:08:00 | 0000-00-00 00:08:00 | down |
| 0000-00-00 00:09:00 | 0000-00-00 00:09:00 | up |
+-----------------------+---------------------+--------+
I could get this information in Java or PHP quite easily, but I would rather use a SQL query. My SQL skills are not particularly advanced. How do I approach this?
source to share
The easiest way to approach this is to use variables, and I think the simplest approach is to add two variables, one being "up" and the other one being "down" to any given string. This sequence of ups has a constant value for the number of previous "down" s and vice versa. This logic can be used for aggregation.
Final query:
select result, min(time_stamp) as start_time, max(time_stamp) as end_time
from (select r.*,
(@ups := @ups + (result = 'up')) as ups,
(@downs := @downs + (result = 'down')) as downs
from results r cross join
(select @ups := 0, @downs := 0) vars
where service_id = 1
order by time_stamp
) r
group by result, (case when result = 'up' then downs else ups end);
source to share