mysql - 对日期/时间事件的 "Blocks"组进行 SQL 查询？

Question

我的表中有数据是服务器事件的日志数据。它看起来像这样(# 列不是数据库或输出的一部分，但可以在下面的注释中引用该数据):

# | DateStamp           | Server  
- | ------------------- | ---------  
1 | 2016-12-01 03:15:19 | Server 1
2 | 2016-12-01 03:17:19 | Server 2
3 | 2016-12-01 03:17:24 | Server 2
4 | 2016-12-01 03:18:01 | Server 1
5 | 2016-12-01 03:18:07 | Server 3
6 | 2016-12-01 04:01:03 | Server 3
7 | 2016-12-01 07:18:47 | Server 1
8 | 2016-12-01 07:19:23 | Server 1
9 | 2016-12-01 09:19:39 | Server 2
10| 2016-12-01 11:19:54 | Server 3

我想写一个输出的查询:

# | Server   | Online              | Offline
- | -------- | ------------------- | -------------------
1 | Server 1 | 2016-12-01 03:15:19 | 2016-12-01 03:18:01
2 | Server 2 | 2016-12-01 03:17:19 | 2016-12-01 03:17:24
3 | Server 3 | 2016-12-01 03:18:07 | 2016-12-01 03:18:07
4 | Server 1 | 2016-12-01 07:18:47 | 2016-12-01 07:19:23
5 | Server 2 | 2016-12-01 09:19:39 | 2016-12-01 09:19:39
6 | Server 3 | 2016-12-01 11:19:54 | (still online)

注意事项:

• 这基本上是对这些服务器何时“活跃”以及持续多长时间的统计。
• 如果下一个服务器的事件间隔大于一个小时，则它被认为是一个新 session 并换行。 (即输出的第 1 行和第 4 行，基于上面的数据第 4 行和第 7 行)
• 澄清一下:输出的第 1 行决定 03:18:01 为“离线”，因为服务器 1 的下一个条目(数据第 7 行的 07:18:47)晚了一个多小时。
• 输出的第 5 行显示离线，因为一个多小时过去了，服务器 2 没有新的条目出现

我很想知道如何查询这个，并根据上面的输出和注释对我的结果进行分组。如果您需要更多信息来建议解决方案，请告诉我。

Answer 1

1) 首先，您应该将日志加载到 MySQL 数据库中:

# Optionally
#drop table if exists srv_logs;

create table srv_logs (
        `id` INT(10) NOT NULL AUTO_INCREMENT,
        `datetime` DATETIME ,
        `server` VARCHAR(300),
    PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
LOAD DATA INFILE 'yourfile.log'
    INTO TABLE srv_logs
    CHARSET utf8
    FIELDS TERMINATED BY '|'  
    OPTIONALLY ENCLOSED BY '"'   
    LINES TERMINATED BY '\n'  IGNORE 2 LINES (
        `id`,
        `datetime`,
        `server`
    );

2) 创建/填写停机时间表的初始化数据:

create table srv_downtime (
        `id` INT(10) NOT NULL AUTO_INCREMENT,
        `server` VARCHAR(300),
        `online` DATETIME ,
        `offline` DATETIME ,
    PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert into  srv_downtime (`server`, `online`, `offline`)
SELECT l.server, MAX(l.datetime), null
FROM srv_logs l
left join srv_logs l2 
   on l.server = l2.server 
   and l.datetime > l2.datetime
   and TIMESTAMPDIFF(MINUTE,l2.datetime,l.datetime) < 60
where l2.id is null 
GROUP BY l.server

3) 重复调用这个insert，直到没有新行被添加，它会在底部添加新行，(之前的工作时间)

    insert into  srv_downtime (`server`, `online`, `offline`)
    (select a.server, min(l2.datetime), offline from
      (SELECT d.server, max(l.datetime) as offline
         FROM srv_downtime d
         left join srv_logs l 
            on l.server = d.server 
            and d.online > l.datetime
         group by l.server
      ) a
      left join srv_logs l2 
        on a.offline > l2.datetime 
        and l2.server = a.server
        and TIMESTAMPDIFF(MINUTE, l2.datetime, a.offline) < 60
      group by a.server
   )

所以在这 3 个步骤之后的示例数据集上，结果似乎是正确的:

Server 1  | 2016-12-01 03:15:19 | 2016-12-01 03:18:01
Server 1  | 2016-12-01 07:18:47 | NULL
Server 2  | 2016-12-01 03:17:19 | 2016-12-01 03:17:24
Server 2  | 2016-12-01 09:19:39 | NULL
Server 3  | 2016-12-01 03:18:07 | 2016-12-01 04:01:03
Server 3  | 2016-12-01 11:19:54 | NULL