MYSQL - 使用下一个日期更新临时表

Question

我有一个表，其中包含 EquipmentNo (VARCHAR)、ActionType (VARCHAR) 和 ActionDate (DateTime) 列

当用户 checkout 一些设备时，会像这样添加一行:

EquipmentNo: 123
ActionType: 'checkout'
ActionDate: '2017-02-03 09:05:27'

当他们检查设备时:

EquipmentNo: 123
ActionType: 'checkin'
ActionDate: '2017-02-03 10:32:46'

一天可以多次 check out /检入一件设备，所以假设 123 号设备在同一天晚些时候进行了另一次 check out /检入。

EquipmentNo: 123
ActionType: 'checkout'
ActionDate: '2017-02-03 11:15:27'

EquipmentNo: 123
ActionType: 'checkout'
ActionDate: '2017-02-03 11:30:55'

我需要编写一个查询来计算所有 session 的持续时间(结帐时间和相应的签到时间之间的差异)。查询还需要总结该设备有多少个 session ，在我们的例子中它有 2 个 session ，持续时间为 102 分钟。

这是我目前的情况

CREATE TEMPORARY TABLE IF NOT EXISTS tmp1 AS (
SELECT EquipmentNo, MIN( ActionDate ) AS CheckOutDate, 
NULL AS CheckInDate, COUNT( * ) AS Sessions
FROM EquipmentSessions WHERE ActionType =  'checkout' GROUP BY EquipmentNo, ActionDate);

这会产生类似的东西

123 | 2017-02-03 09:05:27 | NULL | 1
123 | 2017-02-03 11:15:27 } NULL | 1

我似乎无法做的是锻炼如何构建我的更新语句以使用此表作为源，并且基本上说“在 sourceTable.CheckOutDate 之后为每个 EquipmentNo 获取下一个 checkin 日期”。

Answer 1

假设表格内容如下所示:

CREATE TABLE eqp (eno int, action_type varchar(20), action_date timestamp);

INSERT INTO eqp VALUES(124, 'checkout', '2017-02-03 09:00:00');
INSERT INTO eqp VALUES(123, 'checkout', '2017-02-03 09:05:27');
INSERT INTO eqp VALUES(124, 'checkin',  '2017-02-03 10:00:00');
INSERT INTO eqp VALUES(123, 'checkin',  '2017-02-03 10:32:46');
INSERT INTO eqp VALUES(123, 'checkout', '2017-02-03 11:15:27');
INSERT INTO eqp VALUES(123, 'checkin',  '2017-02-03 11:30:55');

INSERT INTO eqp VALUES(123, 'checkout', '2017-02-04 09:00:00');
INSERT INTO eqp VALUES(123, 'checkin',  '2017-02-04 10:00:00');
INSERT INTO eqp VALUES(123, 'checkout', '2017-02-04 15:00:00');
INSERT INTO eqp VALUES(123, 'checkin',  '2017-02-04 17:00:00');
INSERT INTO eqp VALUES(123, 'checkout', '2017-02-04 18:30:00');
INSERT INTO eqp VALUES(123, 'checkin',  '2017-02-04 19:00:00');

因此，假设如下:

• checkout 总是在设备的checkin 之前
• 这两项操作将在同一天内完成，并且
• (一个隐含的假设)每个被 checkout 的设备都会在表中有一个 checkin 记录(在执行此所需操作之前)

我们可以编写一个查询来获取所有 checkout 事件，这些事件首先按设备排序，然后按时间排序，按照它们发生的顺序，并为每个事件分配一个 rank获取记录:

SELECT @checkoutrank := @checkoutrank + 1 AS rank, eno, action_type, action_date
    FROM eqp, (SELECT @checkoutrank := 0) r
WHERE action_type='checkout'
ORDER BY eno ASC, action_date ASC

这给出:

 rank   |   eno     | action_type   |   action_date
----------------------------------------------------------
1       |   123     |  checkout     | 2017-02-03 09:05:27
2       |   123     |  checkout     | 2017-02-03 11:15:27   
3       |   123     |  checkout     | 2017-02-04 09:00:00
4       |   123     |  checkout     | 2017-02-04 15:00:00   
5       |   123     |  checkout     | 2017-02-04 18:30:00
6       |   124     |  checkout     | 2017-02-03 09:00:00

然后也可以为 checkin 编写类似的查询。

然后我们将有两个表——具有相应的 checkout 和 checkin 可比较的顺序——并且按照它们发生的顺序，从而为我们提供开始和结束时间设备的每个单独 session 。这两个对应的表现在可以简单地通过 rank 和 eno(设备编号)连接，我们可以在其中计算 sessions 的计数以及 SUM 计算每次checkout 和checkin 之间的时间差:

SELECT checkin.eno, DATE(checkin.action_date) AS session_date, 
    COUNT(*) AS sessions, 
    SUM(TIMESTAMPDIFF(SECOND, checkout.action_date, checkin.action_date)) 
             AS sesssion_duration
FROM
    (
        SELECT @checkoutrank := @checkoutrank + 1 AS rank, eno, action_type, action_date
            FROM eqp, (SELECT @checkoutrank := 0) r
        WHERE action_type='checkout'
        ORDER BY eno ASC, action_date ASC
    ) checkout

INNER JOIN
    (
        SELECT @checkinrank := @checkinrank + 1 AS rank, eno, action_type, action_date
            FROM eqp, (SELECT @checkinrank := 0) r
        WHERE action_type='checkin'
        ORDER BY eno ASC, action_date ASC
    ) checkin

    ON checkout.rank = checkin.rank
    AND checkout.eno = checkin.eno

GROUP BY checkin.eno, DATE(checkin.action_date)

给我们输出如下:

eno     | session_date  |   sessions    |   session_duration
--------------------------------------------------------------
123     |   2017-02-03  |       2       |   6167
123     |   2017-02-04  |       3       |   12600
124     |   2017-02-03  |       1       |   3600

为了简单和准确起见，上面的 session_duration 在 SECOND 中计算。

Demo link .