时间间隔计算

Question

表格式和数据内容如下：
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 ）时间差  
最后将他们时间差的和计算出来！

Answer 1

--计算时间差的和，单位是天，其它可以自行转换
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);

Answer 2

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

Answer 3

--对不起上面有点小问题，这个
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';

Answer 4

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

Answer 5

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  给分，呵呵

Answer 6

表名是：test_timeselect 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)