mysql - 来自不同表的 SQL 时间间隔

Question

我有两个不同的表格，分别称为观察值和间隔。

观察结果:

id, type, start
----------
1 classroom 2017-06-07 16:18:40
2 classroom 2017-06-01 15:12:00
----------

间隔:

id, observation id, number, task, time
----------
1 1 1 1 2017-06-07 16:18:48
2 1 2 0 2017-06-07 16:18:55
3 1 3 1 2017-06-07 16:19:00
4 2 1 3 2017-06-01 15:12:10
5 2 2 1 2017-06-01 15:12:15
----------

我想要一个将显示的 View :

观察_id

time_on_task (total time in seconds where task = 1)
----------
1 13
2 5
----------

因此，我必须首先检查第一个观察是否有任务 = 1，如果是，我必须记录当前间隔与观察表中开始时间之间的差异，然后将其添加到总时间中。从那时起，如果任务 = 1，我只需添加当前间隔和上一个间隔的时间差。

我知道我可以使用 TIMESTAMPDIFF() 或 TIMEDIFF() 来获取日期差异。

我相信我将不得不使用内部联接，但不知道处理此查询的最佳方法。任何帮助将不胜感激。

Answer 1

您可以使用此查询获得预期的输出。

select observation_id, TIME_TO_SEC(TIMEDIFF(max(time),min(time)))
from your_table
group by observation_id

根据添加的评论进行编辑:

set @preObservationid= 0; 
set @row_number= 0;
set @preTime = '';
select observation_id , 
sum(if(rownumber = 1, TIME_TO_SEC(TIMEDIFF(time,start)) ,TIME_TO_SEC(TIMEDIFF(time,pretime))))
from 
( select observation_id, task, rownumber, time, start ,pretime
from ( select observation_id, task, time, start, @preTime as pretime, @preTime:= time,
if(@preObservationid = observation_id, @row_number:= @row_number + 1, @row_number:= 1) as rownumber ,

@preObservationid := observation_id 
from observations o join intervals i on o.id = i.observation_id
order by observation_id,time ) innertable
where task = 1 ) final
group by observation_id

希望这对您有帮助。让我解释一下逻辑。

1. 添加行号以便按照您所说的第一条记录查找第一条记录记录的任务= 1，那么我们必须使用开始作为时间。
2. 再添加一列前置时间，这只不过是额外提供一列前置时间包含先前日期的列用于您的第二种情况。如果它任务 = 1 但不是第一个记录。
3. 现在您已掌握了用于计算时间的所有数据。
4. 您可以通过删除每个子查询并运行它来自行调试。
5. 不需要最终子查询，您可以将其删除。