如何计算非连续行之间经过的时间？

我有一张table如下：

CREATE TABLE useraudit(
    `id` INT NOT NULL AUTO_INCREMENT,
    `event` INT(1) unsigned,
    `datetime` DATETIME,
    `computer` VARCHAR(24) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci,
    `username` VARCHAR(15) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci,
    `session` VARCHAR(24) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci,
    `server` VARCHAR(24) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB;

INSERT INTO `useraudit` (`id`, `event`, `datetime`, `computer`, `username`, `session`, `server`) VALUES
(1, 0, '2020-07-24 20:21:04', 'DC1', 'testuser', '', '\\\\DC1 '),
(2, 0, '2020-07-24 20:21:04', 'DC1', 'testuser', 'Console', '\\\\DC1 '),
(3, 1, '2020-07-24 20:49:19', 'DC1', 'testuser', 'Console', '\\\\DC1 '),
(4, 1, '2020-07-24 21:19:33', 'TEST-PC-2', 'testuser', 'Console', '\\\\DC1 '),
(5, 0, '2020-07-24 21:21:35', 'TEST-PC-2', 'testuser', '', '\\\\DC1 '),
(6, 1, '2020-07-24 21:22:28', 'TEST-PC-2', 'testuser', 'Console', '\\\\DC1 '),
(7, 0, '2020-07-24 21:25:48', 'TEST-PC-2', 'testuser', '', '\\\\DC1 '),
(8, 1, '2020-07-24 21:29:16', 'TEST-PC-2', 'testuser', 'Console', '\\\\DC1 '),
(9, 1, '2020-07-24 21:29:18', 'TEST-PC-2', 'testuser', 'Console', '\\\\DC1 '),
(10, 0, '2020-07-24 21:30:06', 'TEST-PC-2', 'testuser', '', '\\\\DC1 '),
(11, 0, '2020-07-24 21:30:07', 'TEST-PC-2', 'testuser', 'Console', '\\\\DC1 '),
(12, 1, '2020-07-24 21:30:56', 'TM-PC', 'testuser', 'Console', '\\\\DC1 '),
(13, 1, '2020-07-24 21:31:07', 'TM-PC', 'testuser', 'Console', '\\\\DC1 '),
(14, 0, '2020-07-24 21:33:30', 'TM-PC', 'testuser', '', '\\\\DC1 '),
(15, 0, '2020-07-24 21:33:31', 'TM-PC', 'testuser', 'Console', '\\\\DC1 '),
(16, 1, '2020-07-25 14:32:25', 'TEST-PC-1', 'testuser', 'RDP-Tcp#7', '\\\\DC1 '),
(17, 1, '2020-07-25 14:32:25', 'TEST-PC-1', 'testuser', 'RDP-Tcp#7', '\\\\DC1 '),
(18, 0, '2020-07-25 14:32:37', 'TEST-PC-1', 'testuser', '', '\\\\DC1 '),
(19, 0, '2020-07-25 14:32:38', 'TEST-PC-1', 'testuser', 'RDP-Tcp#7', '\\\\DC1 '),
(20, 1, '2020-07-25 14:39:46', 'TEST-PC-1', 'testuser', 'RDP-Tcp#8', '\\\\DC1 '),
(21, 1, '2020-07-25 14:39:46', 'TEST-PC-1', 'testuser', 'RDP-Tcp#8', '\\\\DC1 '),
(22, 0, '2020-07-25 15:02:10', 'TEST-PC-1', 'testuser', '', '\\\\DC1 '),
(23, 0, '2020-07-25 15:02:11', 'TEST-PC-1', 'testuser', 'RDP-Tcp#8', '\\\\DC1 '),
(24, 0, '2020-07-25 15:02:28', 'DC1', 'testuser', '', '\\\\DC1 '),
(25, 0, '2020-07-25 15:02:28', 'DC1', 'testuser', 'Console', '\\\\DC1 ');

我想做的是能够计算登录时间-每个会话和每个用户的累计时间。这有点复杂，因为有些事件似乎生成2个事件（特别是在发生远程会话时）。
我很难做到这一点；我的第一个爱好是 GROUP BY datetime ，但有些重复事件不是在同一秒发生的，而是在前一秒或后一秒发生的，因此不太起作用。
就目前而言，我认为通过按顺序排序，获得所有登录会话及其持续时间的列表会更容易 datetime ASC 然后对每个 event = 1 （表示登录而不是注销），搜索直到找到下一个 event = 0 同样的道理 computer 以及 username . 从技术上讲，这些重复的日志是filo（先进后出），但由于它们彼此之间的间隔在几秒钟之内，所以其实并不重要。
如果我可以查询这个，我想过滤掉那些秒数基本相同的结果不会太困难；除此之外，我将如何处理这种查询？我基本上希望有这样的事情：

username | computer | logon_time           | duration`
testuser   DC1         2020-07-24 20:49:19  (seconds between 2020-07-24 20:49:19 and 2020-07-25 15:02:28)
testuser  TEST-PC-2    2020-07-24 21:19:19   2 minutes, 2 seconds
testuser  TEST-PC-2    2020-07-24 21:22:28   3 minutes, 20 seconds
testuser  TEST-PC-2    2020-07-24 21:29:16   50 seconds
testuser  TEST-PC-2    2020-07-24 21:29:18   49 seconds
testuser  TM-PC        2020-07-24 21:30:56   2 minutes, 24 seconds
etc.

我用的是人类友好的时间，但实际上这些时间都是以秒为单位的持续时间。
（并非所有行都可以使用；这里的前两条记录是0，因此可以忽略。）
因为我们不得不这样做，所以可以假设在登录事件之后，特定用户/计算机的下一个注销事件与该事件匹配（在这种情况下，不要再将其用于其他登录事件）。换句话说，有些数据可能是我们必须忽略的“噪音”，并且登录/注销对不能被“重用”。
我开始在php中这样做，但这意味着我失去了排序和聚合数据的能力，这并不理想。不确定这是否是尖叫窗口在某种程度上，但我正在使用mariadb 10.1，它不支持他们很好/在所有。

这将是要使用的正确查询（除非您也有不同的服务器，否则您必须扩展联接条件）。

SELECT lon.`username`, 
       lon.`computer`, 
       lon.`session` ,
       lon.`datetime`                  AS logontime, 
       lof.`datetime`                  AS logofftime, 
       lof.`datetime` - lon.`datetime` AS duration 
FROM   `useraudit` lon 
       INNER JOIN `useraudit` lof 
               ON lon.`computer` = lof.`computer` 
                  AND lon.`username` = lof.`username` 
                  AND lon.`session` = lof.`session` 
                  AND lon.`id` < lof.`id` 
WHERE  lon.`event` = 1 
       AND lof.`event` = 0 
       AND NOT EXISTS (SELECT lon1.id 
                       FROM   `useraudit` lon1 
                       WHERE  lon1.`event` = 1 
                          AND lon1.`computer` = lon.`computer` 
                          AND lon1.`username` = lon.`username` 
                          AND lon1.`session` = lon.`session` 
                              AND lon1.`id` < lon.`id` 
                              AND NOT EXISTS (SELECT lof1.id 
                                              FROM   `useraudit` lof1 
                                              WHERE  lof1.`event` = 0 
                                                     AND lof1.`computer` = lon.`computer` 
                                                     AND lof1.`username` = lon.`username` 
                                                     AND lof1.`session` = lon.`session` 
                                                     AND lon1.`id` < 
                                                         lof1.`id` 
                                                     AND lof1.`id` < 
                                                         lon.`id`))
        AND NOT EXISTS (SELECT lof2.id 
                       FROM   `useraudit` lof2 
                       WHERE  lof2.`event` = 0 
                          AND lof2.`computer` = lon.`computer` 
                          AND lof2.`username` = lon.`username` 
                          AND lof2.`session` = lon.`session` 
                              AND lon.`id` < lof2.`id` AND lof2.`id` < lof.`id`) 

ORDER  BY lon.`datetime`;

如何计算非连续行之间经过的时间？

1条答案

相关问题

热门标签

最新问答