我有一个包含制造装配数据的表，包括时间戳。我试图确定“作业”开始之间的平均间隔（以分钟为单位）。
我返回 id 和时间的查询如下所示：
select job_id, job_started from JobTable where job_started >= '2016-07-01' and job_started <= '2016-07-31';
我正在寻找每行之间的时间差的输出：
15
18
21
14
13
获取平均间隔（以秒为单位）：
select (to_seconds(max(job_started)) - to_seconds(min(job_started))) / (count(*) - 1) as average_interval_seconds
from JobTable
where date(job_started) >= '2016-07-01'
and date(job_started) <= '2016-07-31'
;
获取所有间隔（以秒为单位）：
select to_seconds((
select t2.job_started
from JobTable t2
where t2.job_started > t1.job_started
and date(t2.job_started) <= '2016-07-31'
limit 1
)) - to_seconds(t1.job_started) as interval_seconds
from JobTable t1
where date(t1.job_started) >= '2016-07-01'
and date(t1.job_started) <= '2016-07-31'
and t1.job_started <> (
select job_started
from JobTable
where date(job_started) <= '2016-07-31'
order by job_started desc
limit 1
)
;
http://sqlfiddle.com/#!9/1f8dc3/2 http://sqlfiddle.com/#!9/1f8dc3/2
本文内容由网友自发贡献，版权归原作者所有，本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容，请联系:hwhale#tublm.com(使用前将#替换为@)
