mysql - 选择持续时间超过 1 秒的状态

Question

我有一个问题看起来很简单，但我找不到解决方案。

所以，我有一个包含两个列的表格:

Time               Status00:00:00.111       Off00:00:00.222       On00:00:00.345       On00:00:01.555       On00:00:01.666       Off00:00:02.222       On00:00:02.422       On00:00:02.622       Off00:00:05.888       Off00:00:05.999       Off

我想选择所有持续时间超过1秒的On状态，在这个例子中，我想要序列:

00:00:00.222       On00:00:00.345       On00:00:01.555       On

你们能给我一些线索吗？非常感谢!

Answer 1

一个简单的 GROUP BY 和 SUM 无法在您当前的数据集上执行此操作，因此我的想法是添加一个辅助列:

CREATE TABLE someTable(
`time` DATETIME,
status CHAR(3),
helperCol INT
);

helperCol 是一个 INT，将被设置如下:

CREATE PROCEDURE setHelperCol() 
BEGIN
  DECLARE finished,v_helperCol INT;
  DECLARE status CHAR(3);
  DECLARE ts DATETIME;
  DECLARE CURSOR st FOR SELECT `time`,status,helperCol FROM someTable WHERE helperCol IS NOT NULL; -- Handy for re-use: No need to go over all data, so you can save the helperCol as permanent value.
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = 1;
  SELECT @maxVal:=MAX(helperCol) FROM helperCol;

  SET finished=0;
  SET helperCol=@maxVal;
  IF(!helperCol>0) SET helperCol=1;
  OPEN st;
  FETCH ts,status,v_helperCol FROM st;
  WHILE(finished=0) DO
    IF(status='Off') v_helperCol=v_helperCol+1;
    UPDATE someTable SET helperCol=v_helperCol WHERE `time`=ts; -- Assuming `time` is unique;
    FETCH ts,status,v_helperCol FROM st;
  END WHILE;
  CLOSE st;
END;

执行程序，结果为:

Time               Status  helperCol
00:00:00.111       Off      2
00:00:00.222       On       2
00:00:00.345       On       2
00:00:01.555       On       2
00:00:01.666       Off      3
00:00:02.222       On       3
00:00:02.422       On       3
00:00:02.622       Off      4

现在可以对其进行分组和处理:

SELECT MAX(`time`)-MIN(`time`) AS diffTime
FROM someTable
WHERE status='ON'
GROUP BY helperCol
HAVING MAX(`time`)-MIN(`time`)>1;

结果是(您需要搜索正确的 datetime 函数以应用于 MAX-MIN 部分):

1.333

替代方案:

您还可以在存储过程中处理 MAX-MIN，但这不会像 helperColumn 解决方案那样有效地重复。