mysql - 不同行上的事件之间的时间差

Question

我已经尝试了许多之前建议的方法来获得我正在寻找的结果，但没有运气。这方面确实很新。在过去建议的几个例子中，我遇到了一系列错误。使用 MySQL Workbench 5.2 CE。这是我开始的表格和我希望结束的表格。只是寻找“状态”何时变为 <>0 直到返回到 0。

ndx  status t_stamp              machine_name
1    0      2013-09-06 05:45:24  
2    0      2013-09-06 05:52:57  BM01
3    165    2013-09-06 06:09:36  BM01
4    0      2013-09-06 06:09:57  BM01
5    165    2013-09-06 06:16:55  BM01
6    0      2013-09-06 06:17:14  BM01
7    165    2013-09-06 07:54:08  BM01
8    0      2013-09-06 07:54:23  BM01
9    165    2013-09-06 08:08:52  BM01
10   13     2013-09-06 08:27:10  BM01
11   99     2013-09-06 08:31:52  BM01
12   84     2013-09-06 09:09:28  BM01
13   255    2013-09-06 09:19:29  BM01
14   0      2013-09-06 09:19:33  BM01
15   2      2013-09-06 09:29:17  BM01
16   3      2013-09-06 09:33:07  BM01
17   0      2013-09-06 09:33:08  BM01

对我来说最好的一些建议是使用不同的 JOIN，但是当我尝试它们时，我收到了红色下划线错误。使用 concat 允许我填充目标表，但不正确。这是一个子查询，因为它需要执行多台机器。

CREATE DEFINER=`root`@`localhost` PROCEDURE `pro_report_machine_status2`(IN StartDate DATETIME,IN EndDate DATETIME, IN Machine VARCHAR(50))
BEGIN

SET @table_name = Machine;
SET @sql_text = 
concat('Insert Into tbl_machine_status1
        (machine_name,t_stamp)
        Select machine_name,t_stamp,TIMESTAMPDIFF(second,ign_bm01_status1_ndx,ign_bm01_status1_ndx+1)
        FROM ',@table_name,
        ' WHERE t_stamp >= "',StartDate,
        '" AND t_stamp <= "',EndDate,
        ' AND status <> 0 '
        '" Group By t_stamp asc;');

Prepare stmt From @sql_text;
Execute stmt;
Deallocate prepare stmt;

END

忘记添加实际问题和结果表...太糟糕了

我想返回到表格的是“停机时间”的量，即机器不为零的时间量。因此，寻找这样的代码:评估 t_stamp(非零时的最小值)- t_stamp(返回零时的值)。这会将该时间值插入到每个事件的表中。

ndx    downtime    machine_name
1      00:00:21    bm01
2      00:00:19    bm01
3      00:00:15    bm01
4      01:10:41    bm01
5      00:03:51    bm01

希望这比泥巴更清晰。非常感谢您的帮助和感谢!!!

Answer 1

我对此做了一些假设，所以如果我有问题，请给我评论。

基本上，您的大问题是您假设了一系列事件，而没有确定可以在之间进行的“开始”或“停止”事件。这是一种常见的设计缺陷，您可以通过施加序列和一些逻辑来解决该缺陷。您可以使用用户变量 hackery 来做到这一点，但这是一个更适合游标和/或应用程序逻辑的问题。

这是一个使用光标的解决方案:

DELIMITER //

CREATE PROCEDURE myproc(StartDate DATETIME,EndDate DATETIME, m VARCHAR(10))
BEGIN
    DECLARE cursorend INT DEFAULT FALSE;
    DECLARE evtime DATETIME;
    DECLARE n INT;
    DECLARE prevstat INT DEFAULT 0;
    DECLARE thisstat INT;
    DECLARE dur TIME;
    DECLARE start DATETIME;
    DECLARE mycursor CURSOR FOR 
       SELECT status, t_stamp, ndx
       FROM Machine 
       WHERE machine_name=m
         AND t_stamp BETWEEN StartDate AND EndDate
       ORDER BY t_stamp;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET cursorend = TRUE;

    CREATE TEMPORARY TABLE outputtable(ndx INT, downtime TIME, machine_name VARCHAR(10));

    OPEN mycursor;

    myloop: LOOP
       FETCH mycursor INTO thisstat, evtime, n;
       IF cursorend THEN LEAVE myloop; END IF;
       IF thisstat = 0 AND prevstat <> 0 THEN 
          SET dur = TIMEDIFF(evtime, start);
          INSERT INTO outputtable VALUES(n, dur, m);
       ELSEIF thisstat <> 0 AND prevstat=0 THEN
          SET dur=0;
          SET start = evtime;
       END IF;
       SET prevstat=thisstat;
    END LOOP;
    SELECT * FROM outputtable;
    DROP TABLE outputtable;
    CLOSE mycursor;
END; //

DELIMITER ;

它一次遍历一行(按 t_stamp 排序)，并触发两个事件:

• 当先前状态为 0 并且当前状态非零时，它假定为“开始”时间。
• 当先前状态非零且当前状态为 0 时，它假定“结束”时间。

当“开始”事件发生时，它会在变量start中标记该时间。

当“结束”事件发生时，它会获取该事件的时间与开始之间的差异，并计算持续时间，并将其插入到临时表中。

输出如下所示:

mysql> call myproc('2013-08-01', '2013-10-31','bm01');
+------+----------+--------------+
| ndx  | downtime | machine_name |
+------+----------+--------------+
|    4 | 00:00:21 | bm01         |
|    6 | 00:00:19 | bm01         |
|    8 | 00:00:15 | bm01         |
|   14 | 01:10:41 | bm01         |
|   17 | 00:03:51 | bm01         |
+------+----------+--------------+
5 rows in set (0.41 sec)

此处，ndx 值与状态为 0 的记录的值匹配。如果您希望它是连续的，如示例结果所示，您只需将输出表中的 ndx 列设置为自动递增。