mysql - 再次出现行时间差异

Question

我正在尝试计算两行之间的时间差 - 但这比平常有点棘手。用户进入时会收到一个 temporally_id。此 ID 不是唯一的 - 这意味着该用户离开后，新用户可能会获得相同的 temporally_id，但通常(请参阅下面的说明)仅在第一个用户之后已经离开了。入口表如下所示(entrance_states - 1:进入，2:退出):

| temporally_id | entrance_state | entrance_date    |
-----------------------------------------------------
|    111        |       1        | 2017-01-01 10:20 |
|    222        |       1        | 2017-01-01 11:00 |
|    222        |       2        | 2017-01-01 11:20 |
|    222        |       1        | 2017-01-01 18:00 |
|    111        |       2        | 2017-01-01 21:00 |
|    111        |       1        | 2017-01-01 22:15 |
|    222        |       2        | 2017-01-02 01:00 |
|    111        |       2        | 2017-01-02 02:15 |

如您所见 - 用户也可以留在室内直到第二天。我的愿望是以下结果:

| temporally_id | entrance_date    | timediff |
-----------------------------------------------
|    111        | 2017-01-01 10:20 | 11,50 h  |
|    222        | 2017-01-01 11:00 |  0,33 h  |
|    222        | 2017-01-01 18:00 |  7,00 h  |
|    111        | 2017-01-01 22:15 |  4,00 h  |

所以基本上我想要一个时差。最棘手的部分是，有时，“退出”没有被记录 - 所以在表中，对于相同的temporally_id，状态1看起来像是在另一个状态1之后(这又是不正常的，也不应该是正常的)。因此，我必须考虑此故障并检查状态 1 后面是否跟随状态 2。

这是我的 SQL 查询:

SELECT avg(TIMESTAMPDIFF(MINUTE,A.date,B.date)/60 ) as `diff`
FROM (SELECT 
        entrance_date as date,
        temporally_id
        FROM `entrance` 
        WHERE entrance_state = 1
        order by temporally_id asc, entrance_date asc ) A
LEFT JOIN        
     (SELECT 
        entrance_date as date, 
        temporally_id
        FROM `entrance` 
        WHERE  action_id = 2
        order by temporally_id asc, activity_date asc ) 
        B ON A.temporally_id = B.temporally_id

不幸的是，这并没有给我我需要的东西。

Answer 1

“temporally_id”是对英语的误用。你的意思是“temporary_id”。

您将无法使这个“temporary_id”想法发挥作用，因为您无法自动确定“temporary_id”未使用并重用它。因此，您将遇到竞争条件，即大约同时进入的两个用户收到相同的“temporary_id”。

无论如何，如果你坚持尝试，那么你需要通过消除“退出”尚未被记录的情况来降低这个问题的复杂性。因此，遍历您的表，选择未记录“退出”的所有行，并将其“退出”时间设置为“进入”时间加上 20 分钟。 (通常的网络 session 超时。)