mysql - MySQL时间序列操作

Question

我在 5.6 版中使用 MySQL。（提到是因为不存在可用的 mysql 函数）

给定一个带有“运动检测”时间戳的表。每一行都检测到运动。没有动作就意味着没有进入。

Id | Date Time
---------------
1  | 2018-01-01 15:00:01  // Start of activity phase 1
2  | 2018-01-01 15:00:03
3  | 2018-01-01 15:00:06  // Stop of activity phase 1
// Non-Activity phase
4  | 2018-01-01 17:01:06  // Start of activity phase 2
5  | 2018-01-01 17:02:06
6  | 2018-01-01 17:02:09  // Stop of activity phase 2
// Non-Activity phase, big one because of holiday
7  | 2018-01-10 19:40:06  // Start of activity phase 3
8  | 2018-01-10 19:41:06  // Stop of activity phase 3

我很难找到一个 SQL 查询来概述“活动阶段”。

我试图得到的是这样的：

Id | Activity starts     | Activity ends
---------------------------------------------
1  | 2018-01-01 15:00:01 | 2018-01-01 15:00:06
2  | 2018-01-01 17:01:06 | 2018-01-01 17:02:09
3  | 2018-01-10 19:40:06 | 2018-01-10 19:41:06

我想查看“活动阶段”的开始和结束时间戳。“活动阶段”的定义：“活动阶段”位于（例如）至少30分钟的两个“非活动阶段”之间。

先感谢您。

标签： mysqltime-series

Answer 1

我认为在 MySQL 5.6 中实现这一点的唯一方法是使用存储过程（尽管我很乐意看到有人证明我错了）。这个会做你想做的。请注意，它返回许多单行结果集，因此您需要在应用程序框架中处理这些结果。或者，您可以修改过程以将中间结果存储到临时表中，然后SELECT在过程结束时从临时表中存储所有内容（见下文）。

DELIMITER //
DROP PROCEDURE IF EXISTS get_activity //
CREATE PROCEDURE get_activity()
BEGIN
  DECLARE start, thistime, lasttime DATETIME;
  DECLARE activity_count INT DEFAULT 1;
  DECLARE finished INT DEFAULT 0;
  DECLARE activity_cursor CURSOR FOR SELECT atime FROM activity;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = 1;
  OPEN activity_cursor;
  FETCH activity_cursor INTO start;
  SET lasttime = start;
  act_loop: LOOP
    FETCH activity_cursor INTO thistime;
    IF finished = 1 THEN
      SELECT activity_count, start, lasttime AS end;
      LEAVE act_loop;
    END IF;
    IF thistime > lasttime + INTERVAL 30 MINUTE THEN
      SELECT activity_count, start, lasttime AS end;
      SET start = thistime;
      SET activity_count = activity_count + 1;
    END IF;
    SET lasttime = thistime;
  END LOOP;
END //

对于您的示例数据，此过程返回：

activity_count  start                   end     
1               2018-01-01 15:00:01     2018-01-01 15:00:06
activity_count  start                   end     
2               2018-01-01 17:01:06     2018-01-01 17:02:09
activity_count  start                   end     
3               2018-01-10 19:40:06     2018-01-10 19:41:06

这是使用临时表的过程：

DELIMITER //
DROP PROCEDURE IF EXISTS get_activity //
CREATE PROCEDURE get_activity()
BEGIN
  DECLARE start, thistime, lasttime DATETIME;
  DECLARE activity_count INT DEFAULT 1;
  DECLARE finished INT DEFAULT 0;
  DECLARE activity_cursor CURSOR FOR SELECT atime FROM activity;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = 1;
  CREATE TEMPORARY TABLE activity_temp (id INT, start DATETIME, end DATETIME);
  OPEN activity_cursor;
  FETCH activity_cursor INTO start;
  SET lasttime = start;
  act_loop: LOOP
    FETCH activity_cursor INTO thistime;
    IF finished = 1 THEN
      INSERT INTO activity_temp VALUES (activity_count, start, lasttime);
      LEAVE act_loop;
    END IF;
    IF thistime > lasttime + INTERVAL 30 MINUTE THEN
      INSERT INTO activity_temp VALUES (activity_count, start, lasttime);
      SET start = thistime;
      SET activity_count = activity_count + 1;
    END IF;
    SET lasttime = thistime;
  END LOOP;
  SELECT * FROM activity_temp;
  DROP TABLE activity_temp;
END //

输出（来自CALL get_activity()）：

id  start                   end     
1   2018-01-01 15:00:01     2018-01-01 15:00:06
2   2018-01-01 17:01:06     2018-01-01 17:02:09
3   2018-01-10 19:40:06     2018-01-10 19:41:06