时间间隔计算

[xiangyu120]

表格式和数据内容如下：
insettime status
2010-2-23 8:45:56 1
2010-2-23 8:50:52 1
2010-2-23 8:56:19 2
2010-2-23 8:59:34 1
2010-2-23 9:42:11 2
。 。
。 。
。 。

我想计算 状态2与 状态2之前的第一个状态1时间差的和，
例如： 状态2（2010-2-23 8:56:19）-（2010-2-23 8:45:56）时间差
状态2（2010-2-23 9:42:11）-（2010-2-23 8:59:34 ）时间差
最后将他们时间差的和计算出来！

[tangren]

--计算时间差的和，单位是天，其它可以自行转换
with test as(
  select to_date('2010-2-23 8:45:56','yyyy-mm-dd hh24:mi:ss') insettime,  1 status from dual union  
  select to_date('2010-2-23 8:50:52','yyyy-mm-dd hh24:mi:ss') insettime,  1 status from dual union  
  select to_date('2010-2-23 8:56:19','yyyy-mm-dd hh24:mi:ss') insettime,  2 status from dual union  
  select to_date('2010-2-23 8:59:34','yyyy-mm-dd hh24:mi:ss') insettime,  1 status from dual union  
  select to_date('2010-2-23 9:42:11','yyyy-mm-dd hh24:mi:ss') insettime,  2 status from dual
)
SELECT SUM(sub_time) total_day
  FROM (SELECT insettime, insettime - lag(insettime) over(ORDER BY insettime) sub_time, status
          FROM (SELECT insettime, status, lag(status) over(ORDER BY insettime) pre_status FROM test)
         WHERE pre_status IS NULL OR
               status <> pre_status);

[laoding_dmf1982]

with time_test as
(
select '2010-2-23 8:45:56' inserttime,1 status from dual
union
select '2010-2-23 8:50:52',1 from dual
union
select '2010-2-23 8:56:19',2 from dual
union
select '2010-2-23 8:59:34',1 from dual
union
select '2010-2-23 9:42:11',2 from dual)
select sum(round(to_number(inserttime_next-inserttime)*24*60*60)) total_gap from (
select to_date(inserttime,'yyyy-mm-dd hh24:mi:ss') inserttime,
to_date(lead(inserttime) over(order by inserttime asc),'yyyy-mm-dd hh24:mi:ss') inserttime_next,status from time_test)
where status=1

[tangren]

--对不起上面有点小问题，这个
with test as(
  select to_date('2010-2-23 8:45:56','yyyy-mm-dd hh24:mi:ss') insettime,  1 status from dual union  
  select to_date('2010-2-23 8:50:52','yyyy-mm-dd hh24:mi:ss') insettime,  1 status from dual union  
  select to_date('2010-2-23 8:56:19','yyyy-mm-dd hh24:mi:ss') insettime,  2 status from dual union  
  select to_date('2010-2-23 8:59:34','yyyy-mm-dd hh24:mi:ss') insettime,  1 status from dual union  
  select to_date('2010-2-23 9:42:11','yyyy-mm-dd hh24:mi:ss') insettime,  2 status from dual
)
SELECT SUM(sub_time)*86400 "时间差之和(秒)"
  FROM (SELECT insettime, insettime - lag(insettime) over(ORDER BY insettime) sub_time, status
          FROM (SELECT insettime, status, lag(status) over(ORDER BY insettime) pre_status FROM test)
         WHERE pre_status IS NULL OR
               status <> pre_status)
 WHERE status = '2';

[47522341]

with test as(
select to_date('2010-2-23 8:45:56','yyyy-mm-dd hh24:mi:ss') insettime, 1 status from dual union
select to_date('2010-2-23 8:50:52','yyyy-mm-dd hh24:mi:ss') insettime, 1 status from dual union
select to_date('2010-2-23 8:56:19','yyyy-mm-dd hh24:mi:ss') insettime, 2 status from dual union
select to_date('2010-2-23 8:59:34','yyyy-mm-dd hh24:mi:ss') insettime, 1 status from dual union
select to_date('2010-2-23 9:42:11','yyyy-mm-dd hh24:mi:ss') insettime, 2 status from dual
)

SELECT insettime, status, lag(insettime) over(partition by status ORDER BY insettime),
(insettime - lag(insettime) over(partition by status ORDER BY insettime)) * 24 * 60 * 60
pre_status FROM test

[dahai686123]

select sum(a.insettime-min(b.insettime)) from test a,test b
where a.insettime>b.insettime and b.insettime>(select max(c.insettime) from test c where c.insettime<a.insettime and c.status=2 ) and a.status=2 and b.status=1 group by a.insettime order by a.insettime

假设上面得出来的值是abc
不过得出来的结构还需要加上第一个间隔的时间to_date('2010-2-23 8:56:19','yyyy-mm-dd hh24:mi:ss')-to_date('2010-2-23 8:45:56','yyyy-mm-dd hh24:mi:ss')的值（假设为def）。

按照相应的转换，计算出结果，计算的最终结果为两者之和，即需要的值为=abc+def 给分，呵呵

[byron_chenc]

表名是：test_time

select sum(in_time) sum_in_time
from (select b.insettime last2,
min(a.insettime) last1,
(b.insettime - min(a.insettime)) * 86400 in_time
from (select *
from (select insettime,
case
when status = '2' then
lag(insettime)
over(partition by status order by insettime)
else
null
end lasttime,
status
from test_time)
where status = '2') b,
test_time a
where a.insettime > nvl(b.lasttime, a.insettime - 1)
and a.insettime < b.insettime
group by b.insettime, b.status)

[iqlife]

可以理解为1,2相连的时间差么

[iqlife]

就是说是
1
1
1
2
1
1
2
1
2
没有
1
1
2
2
1
2
2这样的记录,理解没有错的话,状态1是运行,2是停止?