sql - 如何合并两行并计算MySQL中两个时间戳值之间的时间差？

Question

我有一种情况，我确信这种情况很常见，但我不知道该怎么做，也不知道要搜索什么来找到相关的示例/解决方案，这让我很困扰。我对 MySQL 比较陌生(之前一直在使用 MSSQL 和 PostgreSQL)，我能想到的每一种方法都被 MySQL 中缺少的某些功能所阻止。

我有一个“日志”表，它简单地列出了许多不同的事件及其时间戳(存储为日期时间类型)。表中有很多与这个问题无关的数据和列，所以假设我们有一个像这样的简单表:

CREATE TABLE log (  
  id INT NOT NULL AUTO_INCREMENT,  
  name VARCHAR(16),  
  ts DATETIME NOT NULL,  
  eventtype VARCHAR(25),  
  PRIMARY KEY  (id)  
)

假设某些行的 eventtype = 'start' 而其他行的 eventtype = 'stop'。我想要做的是以某种方式将每个“startrow”与每个“stoprow”耦合并找到两者之间的时间差(然后将每个名称的持续时间相加，但这不是问题所在)。每个“开始”事件都应该在“开始”事件之后的某个阶段发生相应的“停止”事件，但是由于数据收集器出现问题/错误/崩溃，可能会丢失一些。在那种情况下，我想无视没有“合作伙伴”的事件。这意味着给定数据:

foo, 2010-06-10 19:45, start  
foo, 2010-06-10 19:47, start  
foo, 2010-06-10 20:13, stop

..我想忽略 19:45 开始事件，而不仅仅是使用 20:13 停止事件作为停止时间来获得两个结果行。

我尝试以不同的方式将表与自身连接起来，但对我来说，关键问题似乎是找到一种方法来正确识别给定“名称”的“开始”事件对应的“停止”事件”。问题与您在餐 table 上的员工在上类和下类时遇到的问题完全相同，并且想了解他们实际工作了多少。我确信必须有众所周知的解决方案，但我似乎找不到它们......

Answer 1

我相信这可能是实现目标的更简单方法:

SELECT
    start_log.name,
    MAX(start_log.ts) AS start_time,
    end_log.ts AS end_time,
    TIMEDIFF(MAX(start_log.ts), end_log.ts)
FROM
    log AS start_log
INNER JOIN
    log AS end_log ON (
            start_log.name = end_log.name
        AND
            end_log.ts > start_log.ts)
WHERE start_log.eventtype = 'start'
AND end_log.eventtype = 'stop'
GROUP BY start_log.name

它应该运行得更快，因为它消除了一个子查询。