MySQL根据状态字段计算多行之间的时间差

Question

我正在尝试计算单个用户的总登录时间。我有以下 MySQL 表:

user_id | timelog             | status
------- | ------------------- | ------
472     | 2017-07-18 08:00:00 | login
472     | 2017-07-18 09:00:00 | break start
472     | 2017-07-18 09:30:00 | break end
472     | 2017-07-18 10:00:00 | logout
472     | 2017-07-18 11:00:00 | login
472     | 2017-07-18 14:00:00 | logout

客户端想要计算用户在选定日期内登录的时间。在做一些案例研究时，我能够计算出第一次登录/注销之间的时间:

SELECT
  TIMEDIFF(
    (SELECT timelog FROM qc_user_status_logs WHERE status = 'logout' AND user_id = '472' AND timelog LIKE '2017-07-18%' LIMIT 0,1),
    (SELECT timelog FROM qc_user_status_logs WHERE status = 'login' AND user_id = '472' AND timelog LIKE '2017-07-18%' LIMIT 0,1)
) as loggedInTime

但是，从示例数据中可以看出，用户在一天中可以进行多次登录/注销，以及多次休息时间。我如何仅使用 MySQL 聚合登录时间。我已经使用 PHP 完成了这一点，但是由于服务器性能问题(有很多记录)，我必须找出在 MySQL 中计算总时间的方法。

Answer 1

这不是一个简单的查询，因此，让我们分步进行:

场景

CREATE TABLE qc_user_status_logs
(
    user_id integer, 
    timelog datetime, 
    status  varchar(15)
) ;
INSERT INTO qc_user_status_logs
    (user_id, timelog, status)
VALUES
    -- Your example data
    (472, '2017-07-18 08:00:00', 'login'),
    (472, '2017-07-18 09:00:00', 'break start'),
    (472, '2017-07-18 09:30:00', 'break end'),
    (472, '2017-07-18 10:00:00', 'logout'),
    (472, '2017-07-18 11:00:00', 'login'),
    (472, '2017-07-18 14:00:00', 'logout'),
    -- An extra user
    (532, '2017-07-18 09:00:00', 'login'),
    (532, '2017-07-18 09:30:00', 'logout'),
    -- And another entry for a user that doesn't log out 
    -- (i.e.: it is *now* logged in)
    (654, now() - interval 33 minute, 'login');

<小时/>

第 1 步

对于每次登录，通过子查询找到相应的注销(在MariaDB中，您将使用窗口函数)

SELECT
    user_id, 
    timelog AS login_time, 
    coalesce(
      (SELECT timelog 
         FROM qc_user_status_logs t_out 
        WHERE     t_out.user_id = t_in.user_id 
              AND t_out.timelog >= t_in.timelog 
              AND t_out.status = 'logout'
      ORDER BY timelog 
        LIMIT 1
      ), 
      now()
    ) AS logout_time
FROM
    qc_user_status_logs t_in
WHERE
    status = 'login'
ORDER BY
    user_id, timelog ;

user_id | login_time          | logout_time        ------: | :------------------ | :------------------    472 | 2017-07-18 08:00:00 | 2017-07-18 10:00:00    472 | 2017-07-18 11:00:00 | 2017-07-18 14:00:00    532 | 2017-07-18 09:00:00 | 2017-07-18 09:30:00    654 | 2017-07-21 23:38:53 | 2017-07-22 00:11:53

---

Step 2

Convert 'login/logout' times to 'logged in' intervals. Best way is to convert times to unix_times and subtract. The result will be number of seconds between login and logout:

SELECT
    user_id, 
    login_time, 
    logout_time, 
    timediff(logout_time, login_time) AS logged_in_time,
    unix_timestamp(logout_time) - unix_timestamp(login_time) AS seconds_logged_in_time
FROM
    (SELECT
        user_id, 
        timelog AS login_time, 
        coalesce(
          (SELECT timelog 
             FROM qc_user_status_logs t_out 
            WHERE     t_out.user_id = t_in.user_id 
                  AND t_out.timelog >= t_in.timelog 
                  AND t_out.status = 'logout'
          ORDER BY timelog 
            LIMIT 1
          ), 
          now()
        ) AS logout_time
    FROM
        qc_user_status_logs t_in
    WHERE
        status = 'login'
    ) AS q1
ORDER BY
    user_id, login_time ;

user_id | login_time          | logout_time         | logged_in_time | seconds_logged_in_time------: | :------------------ | :------------------ | :------------- | ---------------------:    472 | 2017-07-18 08:00:00 | 2017-07-18 10:00:00 | 02:00:00       |                   7200    472 | 2017-07-18 11:00:00 | 2017-07-18 14:00:00 | 03:00:00       |                  10800    532 | 2017-07-18 09:00:00 | 2017-07-18 09:30:00 | 00:30:00       |                   1800    654 | 2017-07-21 23:38:53 | 2017-07-22 00:11:53 | 00:33:00       |                   1980

---

Step 3

From the previous query, aggregate (add) logged in intervals, grouping by user

SELECT
    user_id, 
    sum(unix_timestamp(logout_time) - unix_timestamp(login_time)) AS total_seconds_logged_in_time
FROM
    (SELECT
        user_id, 
        timelog AS login_time, 
        coalesce(
          (SELECT timelog 
             FROM qc_user_status_logs t_out 
            WHERE     t_out.user_id = t_in.user_id 
                  AND t_out.timelog >= t_in.timelog 
                  AND t_out.status = 'logout'
          ORDER BY timelog 
            LIMIT 1
          ), 
          now()
        ) AS logout_time
    FROM
        qc_user_status_logs t_in
    WHERE
        status = 'login'
    ) AS q1
GROUP BY
    user_id
ORDER BY
    user_id ;

user_id | total_seconds_logged_in_time------: | ---------------------------:    472 |                        18000    532 |                         1800    654 |                         1980

---

Step 4

We perform the same thing for breaks

SELECT
    user_id, 
    sum(unix_timestamp(logout_time) - unix_timestamp(login_time)) AS total_seconds_break_time
FROM
    (SELECT
        user_id, 
        timelog AS login_time, 
        coalesce(
          (SELECT timelog 
             FROM qc_user_status_logs t_out 
            WHERE     t_out.user_id = t_in.user_id 
                  AND t_out.timelog >= t_in.timelog 
                  AND t_out.status = 'break end'
          ORDER BY timelog 
            LIMIT 1
          ), 
          now()
        ) AS logout_time
    FROM
        qc_user_status_logs t_in
    WHERE
        status = 'break start'
    ) AS q1
GROUP BY
    user_id
ORDER BY
    user_id ;

user_id | total_seconds_break_time------: | -----------------------:    472 |                     1800

---

Final step:

Take the (step 3 query) and LEFT JOIN it with the (step 4 query) ON user_id, so that we have all the information corresponding to every user_id together.

Subtract the total_seconds_break_time (or 0, if there isn't any break; by using coalesce).

That will give you the final result:

SELECT
    q10.user_id, 
    q10.total_seconds_logged_in_time - 
        coalesce(q20.total_seconds_break_time, 0) AS net_total_seconds_logged_in_time
FROM    
    (SELECT
        user_id, 
        sum(unix_timestamp(logout_time) - unix_timestamp(login_time)) AS total_seconds_logged_in_time
    FROM
        (SELECT
            user_id, 
            timelog AS login_time, 
            coalesce(
              (SELECT timelog 
                 FROM qc_user_status_logs t_out 
                WHERE     t_out.user_id = t_in.user_id 
                      AND t_out.timelog >= t_in.timelog 
                      AND t_out.status = 'logout'
              ORDER BY timelog 
                LIMIT 1
              ), 
              now()
            ) AS logout_time
        FROM
            qc_user_status_logs t_in
        WHERE
            status = 'login'
        ) AS q1
    GROUP BY
        user_id
    ) AS q10
    LEFT JOIN
    (SELECT
        user_id, 
        sum(unix_timestamp(logout_time) - unix_timestamp(login_time)) AS total_seconds_break_time
    FROM
        (SELECT
            user_id, 
            timelog AS login_time, 
            coalesce(
              (SELECT timelog 
                 FROM qc_user_status_logs t_out 
                WHERE     t_out.user_id = t_in.user_id 
                      AND t_out.timelog >= t_in.timelog 
                      AND t_out.status = 'break end'
              ORDER BY timelog 
                LIMIT 1
              ), 
              now()
            ) AS logout_time
        FROM
            qc_user_status_logs t_in
        WHERE
            status = 'break start'
        ) AS q1
    GROUP BY
        user_id
    ) AS q20 ON q20.user_id = q10.user_id
ORDER BY
    q10.user_id ;

user_id | net_total_seconds_logged_in_time------: | -------------------------------:    472 |                            16200    532 |                             1800    654 |                             1980

可以在 dbfiddle here 找到所有内容