mysql - 查找停机时间并计算停机时间长度

Question

我的问题与这里的问题非常相似:SQL - Find all down times and the lengths of the downtimes from MySQL data (set of rows with time stamps and status messages)

我的代码几乎可以工作。我有一个看起来像这样的数据集。我想计算“0”状态的时间长度。

+---------------------+-------------+--------+
| created_at          | fk_check_id | status |
+---------------------+-------------+--------+
| 2019-05-03 08:35:22 |          12 |      0 |
| 2019-05-03 08:36:21 |          12 |      1 |
| 2019-05-03 08:37:21 |          12 |      1 |
| 2019-05-03 08:38:21 |          12 |      1 |
| 2019-05-03 08:39:21 |          12 |      0 |
| 2019-05-03 08:40:21 |          12 |      0 |
| 2019-05-03 08:41:21 |          12 |      1 |
| 2019-05-03 08:42:21 |          12 |      1 |
| 2019-05-03 08:43:21 |          12 |      1 |
| 2019-05-03 08:51:21 |          12 |      1 |
| 2019-05-03 08:52:21 |          12 |      1 |
| 2019-05-03 08:53:21 |          12 |      1 |
| 2019-05-03 08:54:21 |          12 |      0 |
| 2019-05-03 08:55:21 |          12 |      0 |
| 2019-05-03 08:56:22 |          12 |      1 |
| 2019-05-03 08:57:22 |          12 |      1 |
| 2019-05-03 09:01:21 |          12 |      1 |
| 2019-05-03 09:02:21 |          12 |      1 |
| 2019-05-03 09:03:21 |          12 |      0 |
| 2019-05-03 09:04:21 |          12 |      0 |
| 2019-05-03 09:05:22 |          12 |      0 |
| 2019-05-03 09:06:21 |          12 |      0 |
| 2019-05-03 09:07:21 |          12 |      0 |
| 2019-05-03 09:08:21 |          12 |      0 |
+---------------------+-------------+--------+

我的 SQL 查询如下所示:

  SELECT d.start_time
     , d.ended_time
  FROM (SELECT @i := @i + 1 AS i
             , @start := IF(s.status = '0' AND (@status = '1' OR @i = 1), s.created_at, @start) AS start_time
             , @ended := IF(s.status = '1' AND @status = '0', s.created_at, NULL) AS ended_time
             , @status := s.status
         FROM (SELECT t.created_at
                    , t.status
                 FROM logs t
                WHERE t.status IN ('1','0') AND fk_check_id=12
                ORDER BY t.created_at ASC, t.status ASC
              ) s
         JOIN (SELECT @i := 0, @status := '1', @ended := NULL, @start := NULL) i
      ) d
WHERE d.start_time IS NOT NULL
  AND d.ended_time IS NOT NULL

它返回的结果如下:

Array
(
    [0] => Array
        (
            [start_time] => 2019-05-03 08:35:22
            [ended_time] => 2019-05-03 08:36:21
        )

    [1] => Array
        (
            [start_time] => 2019-05-03 08:39:21
            [ended_time] => 2019-05-03 08:41:21
        )

    [2] => Array
        (
            [start_time] => 2019-05-03 08:54:21
            [ended_time] => 2019-05-03 08:56:22
        )

)

一切正常，除了它没有返回最后一个“向下” block ，因为它后面还没有一个完整的“向上”条目。如何返回最近的行并将其用作我的“ending_time”，直到输入完整的“up” block ？

我正在尝试计算总停机时间，由于最后一项尚未列出，因此我的结果存在偏差。

提前致谢!

Answer 1

您使用的 MySQL 版本是什么？如果您使用 MySQL 8，您可能可以将当前结束的选择替换为如下内容:

, @ended := IF((s.status = '1' OR (s.created_at = MAX(s.created_at) OVER())) AND @status = '0', s.created_at, NULL) AS ended_time

如果您没有使用 MySQL 8，这对您没有帮助。 MySQL 8 引入了 OVER() 函数。