一道与时间差有关的SQL面试题
题目:
一组通话记录(总共500万条): 求其中同一个号码的两次通话之间间隔大于10秒的通话记录ID |
create table phone (
id number,
zph number,
bph number,
pbegin date,
pend date
);
insert into phone values(1,98290000,0215466546656,to_date('2007-02-01 09:49:53','YYYY-MM-DD HH24:MI:SS'),to_date('2007-02-01 09:50:16','YYYY-MM-DD HH24:MI:SS'));
insert into phone values(2,98290000,021546654666,to_date('2007-02-01 09:50:29','YYYY-MM-DD HH24:MI:SS'),to_date('2007-02-01 09:50:41','YYYY-MM-DD HH24:MI:SS'));
insert into phone values(3,98290000,021546654666,to_date('2007-02-01 09:50:58','YYYY-MM-DD HH24:MI:SS'),to_date('2007-02-01 09:51:12','YYYY-MM-DD HH24:MI:SS'));
insert into phone values(4,68290900,0755133329866,to_date('2007-02-01 10:04:31','YYYY-MM-DD HH24:MI:SS'),to_date('2007-02-01 10:07:13','YYYY-MM-DD HH24:MI:SS'));
insert into phone values(5,78290000,0755255708638,to_date('2007-02-01 10:48:26','YYYY-MM-DD HH24:MI:SS'),to_date('2007-02-01 10:49:23','YYYY-MM-DD HH24:MI:SS'));
insert into phone values(6,78290000,0755821119109,to_date('2007-02-01 10:49:39','YYYY-MM-DD HH24:MI:SS'),to_date('2007-02-01 10:52:55','YYYY-MM-DD HH24:MI:SS'));
insert into phone values(7,78290000,035730928370,to_date('2007-02-01 11:30:45','YYYY-MM-DD HH24:MI:SS'),to_date('2007-02-01 11:31:58','YYYY-MM-DD HH24:MI:SS'));
insert into phone values(8,78290000,0871138889904,to_date('2007-02-01 11:33:47','YYYY-MM-DD HH24:MI:SS'),to_date('2007-02-01 11:35:00','YYYY-MM-DD HH24:MI:SS'));
insert into phone values(9,68290000,035730928379,to_date('2007-02-01 11:52:20','YYYY-MM-DD HH24:MI:SS'),to_date('2007-02-01 11:54:56','YYYY-MM-DD HH24:MI:SS'));
insert into phone values(10,68290000,0298521811199,to_date('2007-02-01 12:44:45','YYYY-MM-DD HH24:MI:SS'),to_date('2007-02-01 12:45:04','YYYY-MM-DD HH24:MI:SS'));
commit;
SQL> select * from phone;
ID ZPH BPH PBEGIN PEND
---------- ---------- ---------- ----------- -----------
1 98290000 2154665466 2007/2/1 9: 2007/2/1 9:
2 98290000 2154665466 2007/2/1 9: 2007/2/1 9:
3 98290000 2154665466 2007/2/1 9: 2007/2/1 9:
4 68290900 7551333298 2007/2/1 10 2007/2/1 10
5 78290000 7552557086 2007/2/1 10 2007/2/1 10
6 78290000 7558211191 2007/2/1 10 2007/2/1 10
7 78290000 3573092837 2007/2/1 11 2007/2/1 11
8 78290000 8711388899 2007/2/1 11 2007/2/1 11
9 68290000 3573092837 2007/2/1 11 2007/2/1 11
10 68290000 2985218111 2007/2/1 12 2007/2/1 12
10 rows selected
SQL> select t1.id
2 from (select rownum rm, t.* from phone t) t1,
3 (select rownum rm, t.* from phone t) t2
4 where t1.zph = t2.zph
5 and t1.rm = t2.rm + 1
6 and (t1.pbegin - t2.pend)*24*60*60 > 10;
ID
----------
2
3
6
7
8
10
6 rows selected
说明:oracle中date类型数据“+、-”操作返回值单位为“day”。