根据时间字段，统计 相邻记录，时间间隔大于半小时的记录数，在线等

Question

比如一个表 有两个字段
ID datetime
1 2010-01-2 19:30:00
2 2010-01-2 18:30:00
3 2010-01-2 18:10:00
4 2010-01-2 20:10:00
5 2010-01-2 19:20:00数据如上，我现在想用SQL语句，对所有记录按照时间排序，计算出两个最近时间间隔大于半小时的次数比如上面数据
排序得出结果为
3 2010-01-2 18:10:00
2 2010-01-2 18:30:00
5 2010-01-2 19:20:00
1 2010-01-2 19:30:00
4 2010-01-2 20:10:00
时间升序两条相邻记录之间相隔超过 半小时的 共有2条  
即 （2 2010-01-2 18:30:00） 与 （5 2010-01-2 19:20:00）和
（1 2010-01-2 19:30:00）与（4 2010-01-2 20:10:00） 相隔半小时上

Answer 1

with tmp as
(
select 1 id, to_date('2010-01-02 19:30:00','yyyy-mm-dd hh24:mi:ss') datetime from dual union all
select 2 id, to_date('2010-01-02 18:30:00','yyyy-mm-dd hh24:mi:ss') datetime from dual union all
select 3 id, to_date('2010-01-02 18:10:00','yyyy-mm-dd hh24:mi:ss') datetime from dual union all
select 4 id, to_date('2010-01-02 20:10:00','yyyy-mm-dd hh24:mi:ss') datetime from dual union all
select 5 id, to_date('2010-01-02 19:20:00','yyyy-mm-dd hh24:mi:ss') datetime from dual
)
select datetime2, datetime from (
select id, datetime, lag(datetime,1) over (order by datetime) datetime2
from tmp
)
where datetime - datetime2 > 1/48
order by id;DATETIME2                    DATETIME                    
---------------------------- ----------------------------
2010-01-02 19:30:00          2010-01-02 20:10:00         
2010-01-02 18:30:00          2010-01-02 19:20:00 

Answer 2

with temp as(
select 1 id,to_date('2010-01-2 19:30:00','yyyy-mm-dd hh24:mi:ss') t from dual
union all
select 2 id,to_date('2010-01-2 18:30:00','yyyy-mm-dd hh24:mi:ss') t from dual
union all
select 3 id,to_date('2010-01-2 18:10:00','yyyy-mm-dd hh24:mi:ss') t from dual
union all
select 4 id,to_date('2010-01-2 20:10:00','yyyy-mm-dd hh24:mi:ss') t from dual
union all
select 5 id,to_date('2010-01-2 19:20:00','yyyy-mm-dd hh24:mi:ss') t from dual
)
select id,t,t1,t - t1,30/1440  from(
select id,t,lag(t) over(order by t) t1 from temp
) where t - t1 >= 30/1440

Answer 3

  1  select a.datetime,b.dt from t_temp a,
  2  (select id,lag(datetime,1) over(order by datetime) dt from t_temp) b
  3* where a.id=b.id and (a.datetime-b.dt)*24*60*60>=1800
SQL> /DATETIME            DT
------------------- -------------------
2010-01-02 19:20:00 2010-01-02 18:30:00
2010-01-02 20:10:00 2010-01-02 19:30:00