时间戳

首页 > 解决方案 > MySQL - 计算事件之间的时间差 - LEFT JOIN、TIMEDIFF、MAX

mysql - MySQL - 计算事件之间的时间差 - LEFT JOIN、TIMEDIFF、MAX

问题描述

我有一个名为“日志”的表，其中包含以下示例内容：

table log
# id, event, client, starttime, endtime, response_time
'1', 'CALL', 'CLIENT 1', '2019-01-01 08:00:00', '2019-01-01 08:00:00', NULL
'2', 'SUPPORT', 'CLIENT 1', '2019-01-01 08:10:00', '2019-01-01 08:30:00', '00:10:00'
'3', 'CALL', 'CLIENT 2', '2019-01-01 08:20:00', '2019-01-01 08:20:00', NULL
'4', 'SUPPORT', 'CLIENT 1', '2019-01-01 08:45:00', '2019-01-01 08:55:00', NULL

目标是计算最后一个 CALL 事件和实际 SUPPORT 事件之间的 response_time。我想出了以下更新声明：

UPDATE IGNORE log A
  LEFT JOIN log B
  ON B.id = A.id - 1
  SET A.response_time = TIMEDIFF(A.starttime, B.endtime)
  WHERE A.client = B.client
  AND B.event = 'CALL'
  AND A.status = 'SUPPORT'
  AND A.responsetime is NULL

但也有没有先前 CALL 事件的 SUPPORT 事件（例如，计划中的 SUPPORT 没有 CALL 事件）。这些事件的响应时间应保持为 NULL。

因此，对于每个支持记录，我需要计算与前一个 CALL 事件时间的时间差，除非最后一个事件也是一个支持事件。

我尝试通过添加以下内容来检查上一个事件是否不是 SUPPORT 事件：

AND B.id > (SELECT * FROM (SELECT MAX(id)
          FROM log B
          WHERE client = A.client
              AND event = 'SUPPORT') as temp)

但这会引发错误，因为 A.client 在 WHERE 子句中是未知的。

非常感谢所有帮助。

标签： mysql

解决方案

我想出了一个 select 语句，它可以将先前的 CALL 记录加入到以下 SUPPORT 记录中，并保留其他 SUPPORT 记录而没有先前的 CALL 事件不变：

SELECT * 
FROM log A
LEFT JOIN log B 
ON 
A.client = B.client 
AND B.status = 'CALL'
AND B.starttime < a.starttime
AND B.starttime > (IFNULL((SELECT MAX(C.starttime)
                           FROM log C
                           WHERE C.status = 'SUPPORT'
                           AND C.client = A.client
                           AND C.starttime > B.starttime 
                           AND C.starttime < A.starttime
                   ), '1999-01-01 00:00:00'))
WHERE A.Client = 'CLIENT 1'
AND A.status = 'SUPPORT'
AND A.response_time IS NULL
ORDER BY A.starttime;

但是如何将其转换为所有客户端的有效 UPDATE 语句？我无法在子查询的 WHERE 子句中使用 A.client 和 B.starttime（错误代码：1054。“where 子句”中的未知列“A.client”）

UPDATE IGNORE log A
LEFT JOIN log B 
ON 
A.client = B.client 
AND B.status = 'CALL'
AND B.starttime < a.starttime
AND B.starttime > ( IFNULL((SELECT * FROM (SELECT MAX(C.starttime)
                                           FROM log C 
                                           WHERE C.status = 'SUPPORT' 
                                           AND C.client = A.client 
                                           AND C.starttime > B.starttime
                                           AND C.starttime < A.starttime) 
                            AS temp),
                           '1999-01-01 00:00:00'))

SET A.wachttijd = TIMEDIFF(A.starttime, B.starttime), A.type = B.status

WHERE A.status = 'SUPPORT'
AND A.wachttijd IS NULL;

推荐阅读