时间戳

首页 > 解决方案 > MySQL 总时间正在增加

mysql - MySQL 总时间正在增加

问题描述

我有这个疑问，

SELECT
    testTimes.reportId,
    testTimes.userID,
--  diff,
    SUM(diff) AS total
FROM
    (
    SELECT
        start_log.reportID,
        start_log.userID,
        start_log.testID,
        MAX(start_log.eventDateTime) AS start_time,
        end_log.eventDateTime AS end_time,
        TIMESTAMPDIFF(MINUTE,
        MAX(start_log.eventDateTime),
        end_log.eventDateTime) AS diff
    FROM
        testtracker_event AS start_log
    INNER JOIN testtracker_event AS end_log ON
        ( start_log.userID = end_log.userID
        AND start_log.reportID = end_log.reportID
        AND start_log.testID = end_log.testID
        AND end_log.eventDateTime > start_log.eventDateTime)
    WHERE
        (start_log.eventType = 'start'
        OR start_log.eventType = 'resume')
        AND (end_log.eventType = 'finish'
        OR end_log.eventType = 'pause')
        AND start_log.reportID = '20466'
    GROUP BY
        start_log.userID,
        start_log.testID,
        start_log.eventDateTime,
        start_log.reportID
    ORDER BY
        start_time DESC) testTimes

当针对该数据集运行时；

|reportID|testID|eventDateTime|userID|eventType|workstation|note|  
|--------|------|-------------|------|---------|-----------|----|  
|20466|1|2018-04-26 14:41:42|msharpe|start| |           |  
|20466|1|2018-04-26 15:17:10|msharpe|finish| |            |  
|20466|1|2018-04-26 15:17:20|msharpe|reset| |"MIS-CLICK"|  
|20466|1|2018-04-26 15:17:21|msharpe|start| |           |  
|20466|1|2018-04-27 08:11:02|msharpe|finish| |            |  
|20466|2|2018-04-26 14:41:43|msharpe|start| |           |  
|20466|2|2018-04-27 08:11:02|msharpe|finish| |            |  
|20466|3|2018-04-26 14:42:15|msharpe|start| |           |  
|20466|3|2018-04-26 15:17:23|msharpe|finish| |            |  
|20466|4|2018-04-26 15:19:25|msharpe|start| |           |  
|20466|4|2018-04-26 15:34:59|msharpe|finish| |            |  
|20466|5|2018-04-26 14:42:21|msharpe|start| |           |  
|20466|5|2018-04-26 15:17:29|msharpe|finish| |            |  
|20466|7|2018-04-26 16:32:57|msharpe|start| |           |  
|20466|7|2018-04-27 08:11:03|msharpe|finish| |            |  
|20466|8|2018-04-26 15:38:29|msharpe|markna| |            |  
|20466|10|2018-04-27 12:05:21|msharpe|start| |           |  
|20466|10|2018-04-27 12:05:22|msharpe|finish| |           |  
|20466|16|2018-04-26 14:42:17|msharpe|start| |           |  
|20466|16|2018-04-26 15:17:28|msharpe|finish| |           |  
|20466|40|2018-04-26 14:42:17|msharpe|start| |           |  
|20466|40|2018-04-26 15:17:26|msharpe|finish| |           |  
|20466|50|2018-04-27 12:05:22|msharpe|start| |           |  
|20466|50|2018-04-27 12:05:23|msharpe|finish| |           |  
|20466|52|2018-04-27 08:50:54|msharpe|start| |           |  
|20466|52|2018-04-27 10:00:27|msharpe|finish| |           |  
|20466|53|2018-04-26 15:58:56|msharpe|start| |           |  
|20466|53|2018-04-26 16:32:56|msharpe|finish| |           |  
|20466|54|2018-04-26 15:38:24|msharpe|markna| |           |  

RESULT - 20466  msharpe 3293

结果，我目前得到了 3,293 分钟，即使有并发调度和 2 个看起来像是在一夜之间签署了偶数的情况，我预计结果会非常大约 1845

我可以看到发生了一些重复，我猜这与join我缺乏获得正确答案的经验或知识有关，在这种情况下，我找到的答案，我很难与这种情况相关联。

虽然我知道有些人可能会想引用我的一个较旧的问题 How would I update this MySQL query to get total elapsed time from start, stop, pause and resume events and this question Calculating Difference on datetime row betwen rows on the same table 作为重复项，我要问的是改进现有查询以不产生复合效果，和/或尽量减少重复。

SQLFiddle 很好的衡量标准，http: //www.sqlfiddle.com/#!9/0dd34d/1

标签： mysql

解决方案

在您现有的连接中，您将每个起点与多个终点相匹配，因此您将总数相乘。您需要将每个起点与下一个终点相匹配（一个起点，一个终点）。除非您正在运行支持的数据库版本，否则您lead() over()可以使用相关子查询来实现这一点，如下所示：

SELECT
    start_log.reportID
  , start_log.userID
  , start_log.testID
  , start_log.eventDateTime AS start_time
  , start_log.end_time
  , TIMESTAMPDIFF(MINUTE,start_log.eventDateTime,start_log.end_time) AS diff
FROM (
        select
            s.*
            , (select f.eventDateTime from mytable f
                    WHERE s.userID = f.userID
                    AND s.reportID = f.reportID
                    AND s.testID = f.testID
                    AND f.eventType = 'finish'
                    AND f.eventDateTime > s.eventDateTime
               order by f.eventDateTime
               limit 1
               ) end_time
        from mytable as s
        where s.eventType = 'start'
       ) AS start_log

该查询的结果（使用提供的示例）是：

       reportID   userID    testID       start_time             end_time         diff  
 ---- ---------- --------- -------- --------------------- --------------------- ------ 
   1      20466   msharpe        1   26.04.2018 14:41:42   26.04.2018 15:17:10     35  
   2      20466   msharpe        1   26.04.2018 15:17:21   27.04.2018 08:11:02   1013  
   3      20466   msharpe        2   26.04.2018 14:41:43   27.04.2018 08:11:02   1049  
   4      20466   msharpe        3   26.04.2018 14:42:15   26.04.2018 15:17:23     35  
   5      20466   msharpe        4   26.04.2018 15:19:25   26.04.2018 15:34:59     15  
   6      20466   msharpe        5   26.04.2018 14:42:21   26.04.2018 15:17:29     35  
   7      20466   msharpe        7   26.04.2018 16:32:57   27.04.2018 08:11:03    938  
   8      20466   msharpe       10   27.04.2018 12:05:21   27.04.2018 12:05:22      0  
   9      20466   msharpe       16   26.04.2018 14:42:17   26.04.2018 15:17:28     35  
  10      20466   msharpe       40   26.04.2018 14:42:17   26.04.2018 15:17:26     35  
  11      20466   msharpe       50   27.04.2018 12:05:22   27.04.2018 12:05:23      0  
  12      20466   msharpe       52   27.04.2018 08:50:54   27.04.2018 10:00:27     69  
  13      20466   msharpe       53   26.04.2018 15:58:56   26.04.2018 16:32:56     34

虽然可能需要进一步改进，但这应该可以让您继续前进。

见：http ://rextester.com/CWB43023

推荐阅读