mysqli 返回一条结果,MySQL返回第一条和最后一条记录以获取连续相同的结果

I'm using MySQL and have a table called 'results' which stores the outcome of a monitor which determines whether a service is 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 table of results that looks at the results for a given service and return the time it was first recorded as being down and the last time (consecutively) and vice versa for it being up. This will help me record the duration of downtime for a service.

The results I am looking for would look like this.

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 would be able to obtain this information in Java or PHP fairly easily but I would prefer to use a SQL query. My SQL skills are not particularly advanced. How would I approach this?

解决方案

The easiest way to approach this is using variables and I think the easiest approach is to add two variables, one the number of "up"s and the other the number of "down"s up to any given row. A given sequence of ups has a constant value for the number of preceding "down"s, and vice versa. This logic can be used for aggregation.

The resulting query is:

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);