MySQL 分组前瞻？

Question

我有一个事件日志，记录了设备何时启动或停止并显示故障代码，我正在尝试计算故障和启动之间的平均时间和平均时间。这是一个非常简单的示例数据表:

+----+-----------+---------------------+
| id | eventName | eventTime           |
+----+-----------+---------------------+
|  1 | start     | 2012-11-01 14:25:20 |
|  2 | fail A    | 2012-11-01 14:27:45 |
|  3 | start     | 2012-11-01 14:30:49 |
|  4 | fail B    | 2012-11-01 14:32:54 |
|  5 | start     | 2012-11-01 14:35:59 |
|  6 | fail A    | 2012-11-01 14:37:02 |
|  7 | start     | 2012-11-01 14:38:05 |
|  8 | fail A    | 2012-11-01 14:40:09 |
|  9 | start     | 2012-11-01 14:41:11 |
| 10 | fail C    | 2012-11-01 14:43:14 |
+----+-----------+---------------------+

创建代码:

CREATE TABLE `test` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `eventName` varchar(50) NOT NULL,
  `eventTime` datetime NOT NULL,
  PRIMARY KEY (`id`)
);
INSERT INTO `test` (`id`, `eventName`, `eventTime`) VALUES (1,'start','2012-11-01 14:25:20'),(2,'fail A','2012-11-01 14:27:45'),(3,'start','2012-11-01 14:30:49'),(4,'fail B','2012-11-01 14:32:54'),(5,'start','2012-11-01 14:35:59'),(6,'fail A','2012-11-01 14:37:02'),(7,'start','2012-11-01 14:38:05'),(8,'fail A','2012-11-01 14:40:09'),(9,'start','2012-11-01 14:41:11'),(10,'fail C','2012-11-01 14:43:14');

我可以使用类似这样的方法获取开始和失败之间的时间:

SET @time_prev := -1;
SELECT
 *
FROM
(
  SELECT
    eventName
  , eventTime
  , @ts := UNIX_TIMESTAMP(eventTime) AS ts
  , @started := IF(eventName = 'start', 1, 0) AS started
  , @failed := IF(eventName <> 'start', 1, 0) AS failed
  , @time_diff := IF(@time_prev > -1, @ts - @time_prev, 0) AS time_diff
  , @time_prev := @ts AS time_prev
  , @time_to_fail := IF(@failed, @time_diff, 0) AS time_to_fail
  , @time_to_start := IF(@started, @time_diff, 0) AS time_to_start
  FROM
    test
) AS t1;

+-----------+---------------------+------------+---------+--------+-----------+------------+--------------+---------------+
| eventName | eventTime           | ts         | started | failed | time_diff | time_prev  | time_to_fail | time_to_start |
+-----------+---------------------+------------+---------+--------+-----------+------------+--------------+---------------+
| start     | 2012-11-01 14:25:20 | 1351805120 |       1 |      0 |         0 | 1351805120 | 0            | 0             |
| fail A    | 2012-11-01 14:27:45 | 1351805265 |       0 |      1 |       145 | 1351805265 | 0            | 145           |
| start     | 2012-11-01 14:30:49 | 1351805449 |       1 |      0 |       184 | 1351805449 | 184          | 0             |
| fail B    | 2012-11-01 14:32:54 | 1351805574 |       0 |      1 |       125 | 1351805574 | 0            | 125           |
| start     | 2012-11-01 14:35:59 | 1351805759 |       1 |      0 |       185 | 1351805759 | 185          | 0             |
| fail A    | 2012-11-01 14:37:02 | 1351805822 |       0 |      1 |        63 | 1351805822 | 0            | 63            |
| start     | 2012-11-01 14:38:05 | 1351805885 |       1 |      0 |        63 | 1351805885 | 63           | 0             |
| fail A    | 2012-11-01 14:40:09 | 1351806009 |       0 |      1 |       124 | 1351806009 | 0            | 124           |
| start     | 2012-11-01 14:41:11 | 1351806071 |       1 |      0 |        62 | 1351806071 | 62           | 0             |
| fail C    | 2012-11-01 14:43:14 | 1351806194 |       0 |      1 |       123 | 1351806194 | 0            | 123           |
+-----------+---------------------+------------+---------+--------+-----------+------------+--------------+---------------+

但是为了在失败和开始之间获得时间，我必须前进到下一条记录并丢失该失败代码的分组。我如何才能将其提升到一个新的水平，并将 future 时间开始合并到一个失败的记录中，以便对其进行分组？

最终，在计算平均值和中位数之后，我最终会得到这样的结果集:

+-----------+-------------+----------------+--------------+-----------------+
| eventName | avg_to_fail | median_to_fail | avg_to_start | median_to_start |
+-----------+-------------+----------------+--------------+-----------------+
|    fail A |      110.66 |         124.00 |       103.00 |           63.00 |
|    fail B |      125.00 |         125.00 |       185.00 |          185.00 |
+-----------+-------------+----------------+--------------+-----------------+

Answer 1

这给出了平均值中位数是 SQL 中的一个难题。 Simple way to calculate median with MySQL给出了一些想法。两个内部查询给出的结果集为中位数超过有中位数聚合。

Select
  times.eventName,
  avg(times.timelapse) as avg_to_fail,
  avg(times2.timelapse) as avg_to_start
From (
  Select
    starts.id,
    starts.eventName,
    TimestampDiff(SECOND, starts.eventTime, Min(ends.eventTime)) as timelapse
  From
    Test as starts,
    Test as ends
  Where
    starts.eventName != 'start' And
    ends.eventName = 'start' And
    ends.eventTime > starts.eventTime
  Group By
    starts.id
) as times2
  Right Outer Join (
  Select
    starts.id,
    ends.eventName,
    TimestampDiff(SECOND, starts.eventTime, Min(ends.eventTime)) as timelapse
  From
    Test as starts,
    Test as ends
  Where
    starts.eventName = 'start' And
    ends.eventName != 'start' And
    ends.eventTime > starts.eventTime
  Group By
    starts.id
) as times
  On times2.EventName = times.EventName
Group By
  Times.eventName

为了帮助理解，我首先考虑

Select
  starts.id,
  ends.eventName,
  starts.eventTime, 
  ends.eventTime
From
  Test as starts,
  Test as ends
Where
  starts.eventName = 'start' And
  ends.eventName != 'start' And
  ends.eventTime > starts.eventTime

这就是没有group by和min语句的内部查询times的本质。您会看到它有一行将每个开始事件与每个结束事件组合在一起，其中结束事件在开始事件之后。称之为 X。

接下来是

Select
  X.startid,
  X.endeventname,
  TimestampDiff(SECOND, X.starttime, Min(x.endTime)) as timelapse
From
  X
Group By
  X.startid

这里的关键是 Min(x.endTime) 与 group by 相结合。所以我们在开始时间之后得到最早的结束时间(因为 X 已经将它限制在之后)。虽然我只选择了我们需要使用的列，但我们可以在这里访问开始时间 id、结束时间 id 开始事件、结束事件、开始时间、分钟(结束时间)。您可以调整它以找到 avg_to_start 的原因是因为我们选择了有趣的事件名称，因为我们都有。

SQL fiddle :http://sqlfiddle.com/#!2/90465/6