一个关于sql的面试题 面倒了一片人，求解

Question

一组通话记录（总共500万条）:
ID 主叫号码 被叫号码 通话起始时间   通话结束时间           通话时长
1  98290000 0215466546656 2007-02-01 09:49:53.000 2007-02-01 09:50:16.000 23
2  98290000 021546654666  2007-02-01 09:50:29.000 2007-02-01 09:50:41.000 12
3  98290000 021546654666  2007-02-01 09:50:58.000 2007-02-01 09:51:12.000 14
4  68290900 0755133329866 2007-02-01 10:04:31.000 2007-02-01 10:07:13.000 162
5  78290000 0755255708638 2007-02-01 10:48:26.000 2007-02-01 10:49:23.000 57
6  78290000 0755821119109 2007-02-01 10:49:39.000 2007-02-01 10:52:55.000 196
7  78290000 035730928370  2007-02-01 11:30:45.000 2007-02-01 11:31:58.000 73
8  78290000 0871138889904 2007-02-01 11:33:47.000 2007-02-01 11:35:00.000 73
9  68290000 035730928379  2007-02-01 11:52:20.000 2007-02-01 11:54:56.000 156
10 68290000 0298521811199 2007-02-01 12:44:45.000 2007-02-01 12:45:04.000 19
求其中同一个号码的两次通话之间间隔大于10秒的通话记录ID
这个sql应该怎么写呢 ?
无论是主叫还是被叫都算一次通话  
----------------------------------
这个是我们班同学的一 个面试题目  帮忙看看

Answer 1

你朋友面试什么岗位呀？
考这个。。select id from 
(select ID, 主叫号码 as num,通话起始时间 as sj,通话时长  from yourtable
union 
select ID,  被叫号码  as num,通话起始时间 as sj,通话时长  from yourtable
union
select ID, 主叫号码 as num,通话结束时间 as sj,通话时长  from yourtable
union
select ID, 主叫号码 as num,通话结束时间 as sj,通话时长  from yourtable
where (lag(sj,1) over(partition by num order by sj )+ interval '10' second< sj) 

Answer 2

有点错~~
select id from 
(select ID, 主叫号码 as num,通话起始时间 as sj,通话时长  from yourtable
union 
select ID,  被叫号码  as num,通话起始时间 as sj,通话时长  from yourtable
union
select ID, 主叫号码 as num,通话结束时间 as sj,通话时长  from yourtable
union
select ID, 被叫号码 as num,通话结束时间 as sj,通话时长  from yourtable
where (lag(sj,1) over(partition by num order by sj )+ interval '10' second< sj)

Answer 3

这个用分析函数lag或者lead就能解决

Answer 4

select m.id from
(
select id , 号码 , 通话时长 , row_number() over(order by 号码 , 时间) px from
(
  select id , 主叫号码 号码 , 通话起始时间 时间 , 通话时长 from tb
  union all
  select id , 被叫号码 号码 , 通话结束时间 时间 , 通话时长 from tb
) t
) m ,
(
select id , 号码 , 通话时长 , row_number() over(order by 号码 , 时间) px from
(
  select id , 主叫号码 号码 , 通话起始时间 时间 , 通话时长 from tb
  union all
  select id , 被叫号码 号码 , 通话结束时间 时间 , 通话时长 from tb
) t
) n
where m.号码 = n.号码 and m.px = n.px - 1 and (m.通话时长 > 10 or n.通话时长 > 10)

Answer 5

主叫 callman
被叫 called
结束时间 endtime
通话时间 intervaltimeselect * from 
(
select callman,called,begintime,endtime,lead(begintime) over(partition by callman order by begintime) nextbegintime,intervaltime, bexchange from
(
select callman,called,endtime-interval begintime,endtime,intervaltime,1 bexchange from phone_record
union 
select called,callman,endtime-interval begintime,endtime,intervaltime,0 bexchange from phone_record
)
)
where (nextbegintime- endtime)>10

Answer 6

用的是oracle9
没有lag 和lead函数
给一个思路（比较麻烦的）：
1、建表
create table test_call(id number(9) not null,
                       masterphone varchar2(15) not null ,
                       calledphone varchar2(15) not null , 
                       begindate date,
                       enddate date,
                       ttime  number(5)
                       );
2、构建数据insert into test_call_last values(1,'0215466546656', to_date('2007-2-1 9:49:53', 'yyyy-mm-dd hh24:mi:ss'),23);    
insert into test_call_last values(2,'021546654666 ', to_date('2007-2-1 9:50:29', 'yyyy-mm-dd hh24:mi:ss'),12);    
insert into test_call_last values(3,'021546654666 ', to_date('2007-2-1 9:50:58', 'yyyy-mm-dd hh24:mi:ss'),14);    
insert into test_call_last values(4,'0298521811199', to_date('2007-2-1 12:44:45','yyyy-mm-dd hh24:mi:ss'),19);    
insert into test_call_last values(5,'035730928370 ', to_date('2007-2-1 11:30:45','yyyy-mm-dd hh24:mi:ss'),73);    
insert into test_call_last values(6,'035730928379 ', to_date('2007-2-1 11:52:20','yyyy-mm-dd hh24:mi:ss'),156);   
insert into test_call_last values(7,'0755133329866', to_date('2007-2-1 10:04:31','yyyy-mm-dd hh24:mi:ss'),162);   
insert into test_call_last values(8,'0755255708638', to_date('2007-2-1 10:48:26','yyyy-mm-dd hh24:mi:ss'),57);    
insert into test_call_last values(9,'0755821119109', to_date('2007-2-1 10:49:39','yyyy-mm-dd hh24:mi:ss'),196);   
insert into test_call_last values(10,'0871138889904',to_date('2007-2-1 11:33:47','yyyy-mm-dd hh24:mi:ss'),73 ); 
insert into test_call_last values(11,'68290000',to_date('2007-2-1 11:52:20','yyyy-mm-dd hh24:mi:ss'),156);     
insert into test_call_last values(12,'68290000',to_date('2007-2-1 12:44:45','yyyy-mm-dd hh24:mi:ss'),19 );     
insert into test_call_last values(13,'68290900',to_date('2007-2-1 10:04:31','yyyy-mm-dd hh24:mi:ss'),162 );    
insert into test_call_last values(14,'78290000',to_date('2007-2-1 10:48:26','yyyy-mm-dd hh24:mi:ss'),57 );     
insert into test_call_last values(15,'78290000',to_date('2007-2-1 10:49:39','yyyy-mm-dd hh24:mi:ss'),196 );    
insert into test_call_last values(16,'78290000',to_date('2007-2-1 11:30:45','yyyy-mm-dd hh24:mi:ss'),73);      
insert into test_call_last values(17,'78290000',to_date('2007-2-1 11:33:47','yyyy-mm-dd hh24:mi:ss'),73  );    
insert into test_call_last values(18,'98290000',to_date('2007-2-1 9:49:53','yyyy-mm-dd hh24:mi:ss'),23);      
insert into test_call_last values(19,'98290000',to_date('2007-2-1 9:50:29','yyyy-mm-dd hh24:mi:ss'),12 );     
insert into test_call_last values(20,'98290000',to_date('2007-2-1 9:50:58','yyyy-mm-dd hh24:mi:ss'),14 ); 
3、构建sequence
create sequence S_CALL
minvalue 1
maxvalue 99999999999999
start with 1
increment by 1
cache 20;
4、构建临时表（按号码和通话开始时间，形成一个新的序号sequence）
CREATE TABLE test_call_last as 
SELECT bb.*,S_CALL.nextval FROM test_call_next bb ORDER BY num,sj5、统计脚本
SELECT cc.* FROM 
 (SELECT aa.ID,aa.num,aa.sj,aa.ttime,
 (SELECT bb.sj FROM test_call_last bb WHERE aa.num=bb.num
 AND bb.Id=(SELECT min(cc.ID) FROM test_call_last cc WHERE cc.ID>aa.ID  )) sj2
  FROM test_call_last aa)cc
  WHERE cc.sj2 IS NOT NULL
  AND sj+(ttime+10)/(24*60*60) <sj2结果如下：
    ID NUM SJ TTIME SJ2
1 2 021546654666  2007-2-1 9:50:29 12 2007-2-1 9:50:58
2 11 68290000 2007-2-1 11:52:20 156 2007-2-1 12:44:45
3 14 78290000 2007-2-1 10:48:26 57 2007-2-1 10:49:39
4 15 78290000 2007-2-1 10:49:39 196 2007-2-1 11:30:45
5 16 78290000 2007-2-1 11:30:45 73 2007-2-1 11:33:47
6 18 98290000 2007-2-1 9:49:53 23 2007-2-1 9:50:29
7 19 98290000 2007-2-1 9:50:29 12 2007-2-1 9:50:58