mysql - 根据时间戳计算出总秒数

Question

我有一个包含三列(用户、时间戳、事件)的表。事件基本上是 checkin 或 checkout 。如何生成查询来查看用户每天打卡的总秒数？

系统存在一个问题，有时有两次 checkin ，但只有一次 checkout ...在这种情况下，我只想采用最低的 checkin 时间戳。

我想出了这样的方法(事件:1 = checkin ，0 = checkout ):

select a.user_id, a.d, time_to_sec(TIMEDIFF(b.created_at, a.created_at)) total_secs,
 a.created_at check_in, b.created_at check_out
from (select user_id, created_at, date(created_at) d, @rownum := @rownum + 1 AS num from table, (SELECT @rownum := 0) r where activity = 1 order by user_id, created_at) a
join (select user_id, created_at, date(created_at) d, @rownum2 := @rownum2 + 1 AS num from table, (SELECT @rownum2 := 0) r where activity = 0 order by user_id, created_at) b 
on a.user_id = b.user_id and a.d = b.d and a.num = b.num

但是，我认为依赖rownum并不准确

Answer 1

如果我正确理解您的要求，您需要的是结帐与之前最早 checkin (但在上一次结帐之后)之间的秒数。

因此，您需要一个查询，将结帐与之前的结帐连接起来，然后与这两个事件之间最早的 checkin 连接起来。

select
curr_co.user_id,
curr_co.created_at,
max(prev_co.created_at)
from
table as curr_co
left outer join
table as prev_co
on (curr_co.user_id = prev_co.user_id and curr_co.created_at > prev_co.created_at and curr_co.activity = _checkout_ and prev_co.activity = _checkout_)<br/>
group by curr_co.user_id, curr_co.created_at

此查询应该为您提供一个结账列表，其中包含每个用户之前的结账记录。

现在选择之间的所有签到和最小的签到并计算差异。

select ... min(ci.created_at), time_to_sec(TIMEDIFF(min(ci.created_at), curr_co.created_at))
... join
table as ci
on (ci.user_id = curr_co.user_id and ci.created_at < curr_co.created_at and ci.created_at > prev_co.created_at and ci.activity = _check-in_)