MySQL返回第一条和最后一条记录以获取连续相同的结果 [英] MySQL return first and last record for consecutive identical results

问题描述

我正在使用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.

对于service_id 1 ...

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     |
+-----------------------+---------------------+--------+

我可以很容易地用Java或PHP获得此信息，但我更喜欢使用SQL查询.我的SQL技能不是特别高级.我将如何处理?

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?

推荐答案

最简单的方法是使用变量，我认为最简单的方法是添加两个变量，一个是"up"的数量，另一个是到任何给定行的下降"数.给定的升序序列对于前面的降"次数具有恒定值，反之亦然.此逻辑可用于聚合.

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.

结果查询为:

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

这篇关于MySQL返回第一条和最后一条记录以获取连续相同的结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！