File Count Time in MYSQL

Question

Suppose I have a FileLog table whose data looks like this.

+-----+------+---------------------+-----------+
|logId|fileId|timestamp            |status     |
+-----+------+---------------------+-----------+
|1    |1     |"2014-03-04 01:00:00"|inProgress |
+-----+------+---------------------+-----------+
|2    |1     |"2014-03-04 01:30:00"|onHold     |
+-----+------+---------------------+-----------+
|3    |1     |"2014-03-05 01:00:00"|inProgress |
+-----+------+---------------------+-----------+
|4    |1     |"2014-03-05 02:00:00"|onHold     |
+-----+------+---------------------+-----------+
|5    |1     |"2014-03-06 01:00:00"|inProgress |
+-----+------+---------------------+-----------+
|6    |1     |"2014-03-06 01:30:00"|complete   |
+-----+------+---------------------+-----------+
|7    |2     |"2014-03-05 01:00:00"|inProgress |
+-----+------+---------------------+-----------+
|8    |2     |"2014-03-05 02:00:00"|complete   |
+-----+------+---------------------+-----------+

In this table, I save the status of the file ( Running or Standby or Completed ) with a timestamp. Now I want the total number of files in which the file was in inProgress state .

 Thus, the expected result should be something similar for the data.

+------+----------+
|fileId| time(min)|
+------+----------+
|1     |120       |
+------+----------+
|2     |60        |
+------+----------+

If possible, I want to get the result without a subquery due to performance issues.

Answer 1

, -. . SQL Fiddle:

SELECT a.fileId, 
SUM(TIME_TO_SEC(TIMEDIFF(COALESCE(b.t,UTC_TIMESTAMP),a.t))/60) as fileTimeInMin
    FROM FileLog a
    LEFT JOIN FileLog b
    ON a.fileId = b.fileid
    AND b.status IN ('onHold', 'complete')
    AND b.t > a.t
    LEFT JOIN FileLog c ON a.fileId = c.fileId
     AND c.t > a.t AND c.t < b.t
WHERE a.status = 'inProgress'
AND c.t is null
    GROUP BY a.fileId;

Answer 2

, , (.. inProgress onHold complete).

, : -

SELECT fileId, SUM(TIMESTAMPDIFF(MINUTE, time_inProgress, time_onHold)) AS TotalTime
FROM
(
    SELECT a.fileId, a.t AS time_inProgress, MIN(b.t) AS time_onHold
    FROM FileLog a
    INNER JOIN FileLog b
    ON a.fileId = b.fileid
    AND a.status = 'inProgress'
    AND b.status IN ('onHold', 'complete')
    AND b.t > a.t
    GROUP BY a.fileId, a.t
) Sub1
GROUP BY fileId;

SQL : -

http://www.sqlfiddle.com/#!2/a766b2/1

, , , , onHold complete. inProgress /: -

SELECT fileId, SUM(TIMESTAMPDIFF(MINUTE, time_inProgress, time_onHold)) AS TotalTime
FROM
(
    SELECT a.fileId, a.t AS time_inProgress, MIN(IFNULL(b.t, NOW())) AS time_onHold
    FROM FileLog a
    LEFT OUTER JOIN FileLog b
    ON a.fileId = b.fileid
    AND b.status IN ('onHold', 'complete')
    AND b.t > a.t
    WHERE a.status = 'inProgress'
    GROUP BY a.fileId, a.t
) Sub1
GROUP BY fileId;

Answer 3

. , , .

, , . . ( fileId), . timestamp stamp, .

(http://sqlfiddle.com/#!2/53fe8/3/0). :

SELECT TIMESTAMPDIFF(MINUTE, @prevStamp, stamp) AS timediff,
       @prevStamp AS stamp,
       @prevStatus AS status,
       @prevFileId AS fileId,
       @prevStamp := stamp AS newstamp,
       @prevStatus := status AS newstatus,
       @prevFileId := fileId AS newfileid
  FROM FileLog,
       (SELECT @prevStamp := NULL,
               @prevStatus := NULL,
               @prevFileId := NULL) AS r
 ORDER BY fileId, stamp

, , , .

, "inProgress". , , , (http://sqlfiddle.com/#!2/53fe8/6/0).

SELECT SUM(timediff) in_progress_time, 
       MAX(newstamp) latest_time, 
       fileid
  FROM (
          /* time interval subquery */
          SELECT TIMESTAMPDIFF(MINUTE, @prevStamp, stamp) AS timediff,
                 @prevStamp AS stamp,
                 @prevStatus AS status,
                 @prevFileId AS fileId,
                 @prevStamp := stamp AS newstamp,
                 @prevStatus := status AS newstatus,
                 @prevFileId := fileId AS newfileid
            FROM FileLog,
                 (SELECT @prevStamp := NULL,
                         @prevStatus := NULL,
                         @prevFileId := NULL) AS r
           ORDER BY fileId, stamp
       ) AS tis
 WHERE status = 'inProgress'
 GROUP BY status, fileId 
 ORDER BY fileId

, .

@Kickstart . , fileId inProgress ? in_progress_time. !

, . sqlfiddle: http://sqlfiddle.com/#!2/ef1e8/23/0

SELECT SUM(timediff) in_progress_time, 
       MAX(newstamp) latest_time, 
       fileid
  FROM (
          /* time interval subquery */
          SELECT IF(fileId = @prevFileId, 
                    TIMESTAMPDIFF(MINUTE, @prevStamp, stamp),
                    TIMESTAMPDIFF(MINUTE, @prevStamp, CURRENT_TIMESTAMP))
                             AS timediff,
                 @prevStamp AS stamp,
                 @prevStatus AS status,
                 @prevFileId AS fileId,
                 @prevStamp := stamp AS newstamp,
                 @prevStatus := status AS newstatus,
                 @prevFileId := fileId AS newfileid
            FROM FileLog,
                 (SELECT @prevStamp := NULL,
                         @prevStatus := NULL,
                         @prevFileId := NULL) AS r
           ORDER BY fileId, stamp
       ) AS tis
 WHERE status = 'inProgress'
 GROUP BY status, fileId 
 ORDER BY fileId

0 CURRENT_TIMESTAMP, , , .

Here about this question. Despite the fact that it looks complicated, it does one pass over the data. Other approaches to this kind of reporting can do self-learning, which leads to the MySQL server having to complete the query.

Performance indicator

It may or may not help fulfill this query to create a coverage index on (fileId, stamp, status). This may allow the subquery that runs ORDER BY fileId, stampto work in a natural way, rather than requiring so-called file management.