oracle数据查询问题

[romantictravel]

有500万条通话记录
编号 号码 被叫号码 开始时间 结束时间 通话时长
1 10010 20020 12:00 12:01 1
2 10010 20045 12:05 12:06 1
3 10012 30045 12:45 12:55 10
数据不是很正确
查询要求: 查询同一电话号码两次通话间隔大于10秒的所有编号 (数据没有秒自己添加)
分析:10010可能有五次通话,加入第四次结束与第五次开始时间间隔大于10秒那么这条数据就符合

[shiyiwan]

第四次结束，第五次开始在哪里呢？

你给的数据和说明都没解释清楚哦，还是我看不懂。

[iqlife]

数据没有秒自己添加
怎么个添加规则？

[crazylaa]

[Quote=引用楼主 romantictravel 的回复:]
有500万条通话记录
  编号      号码          被叫号码          开始时间          结束时间          通话时长
    1        10010        20020              12:00              12:01            1
    2        10010        20045                12:05            12:06            1
    3        10012        30045                12:45            12:55            10
  数据不是很正确
  查询要求: 查询同一电话号码两次通话间隔大于10秒的所有编号 (数据没有秒自己添加)
  分析:10010可能有五次通话,加入第四次结束与第五次开始时间间隔大于10秒那么这条数据就符合
[/Quote]

我来帮楼主描叙需求

有500万条通话记录
编号 号码 被叫号码 开始时间 结束时间 通话时长
1 10010 20020 12:00:00 12:01:32 1
2 10010 20045 12:05:37 12:06:08 1
3 10012 30045 12:45:11 12:55:33 10
4 10010 20045 12:06:10 12:06:58 1
5 10010 20045 12:15:37 12:16:08 1
6 10010 20045 12:16:10 12:16:28 1
查询要求: 查询同一电话号码两次通话间隔大于10秒的所有编号 (数据没有秒自己添加)
分析:10010可能有五次通话,假如第四次结束与第五次开始时间间隔大于10秒那么这条数据就符合

比如：
号码 10010 在 编号1的结束时间12:01:32 ，下次通话编号2，开始时间 12:05:37 ,两者相差大于10秒，那么编号2 符合要求。
号码 10010 在 编号2的结束时间12:06:08 ，下次通话编号4，开始时间 12:06:10 ,两者相差小于10秒，那么编号4 不符合要求。
号码 10010 在 编号4的结束时间12:06:58 ，下次通话编号5，开始时间 12:15:37 ,两者相差大于10秒，那么编号5 符合要求。
号码 10010 在 编号5的结束时间12:16:08 ，下次通话编号6，开始时间 12:16:10 ,两者相差小于10秒，那么编号6 不符合要求。
输出结果就是
号码 编号
10010 2,5
其他号码按相同方式处理。

数据没有秒自己添加 ，就是要我们给他弄假数据，应该是这个意思。否则没秒怎么算到秒？

我是菜鸟不会写sql，只会描述需求，不知道对不对。请楼主看看是不是这意思。

[小灰狼W]

按楼上描述的需求，用lead函数就行了
select * from(
select t.*,lead(开始时间)over(partition by 号码 order by 编号)-结束时间 time
from table1 t)
where time*24*60*60>10

[小灰狼W]

或者
select * from table1 t
where not exists(
select 1 from table1
where 号码=t.号码 and 开始时间-t.结束时间 between 0 and 10/24/3600)
and exists(
select 1 from table1
where 号码=t.号码 and 编号>t.编号)

[yonghengdizhen]

select 编号,
号码,
被叫号码,
开始时间,
结束时间,
通话时长

from
(
select a.*,
to_number(replace(开始时间,':',''))
- last_value(to_number(replace(结束时间,':',''))) over(partition by 被叫号码 order by 结束时间 ROWS PRECEDING 1 and PRECEDING 1) as 通话间隔1,
first_value(to_number(replace(开始时间,':',''))) over(partition by 被叫号码 order by 开始时间 ROWS 1 FOLLOWING and 1 FOLLOWING)
- to_number(replace(结束时间,':','')) as 通话间隔2
from 通话记录 a
)
where 通话间隔1>=10 or 通话间隔2>=10

[yonghengdizhen]

窗口聚合函数是很消耗sort工作区的,内存量不足的话,如果实现为磁盘排序代价较高,建议分步实现.

[romantictravel]

drop table tb_mumu;
alter session set nls_date_formate='yyyy-mm-dd hh24:mi:dd';
create table tb_mumu (
ID number primary key not null,
主叫号码 varchar2(11) not null,
被叫号码 varchar2(11) not null,
通话开始时间 date not null,
通话结束时间 date not null,
通话时长 number not null
);




delete from tb_mumu;
insert into tb_mumu (ID, 主叫号码, 被叫号码, 通话开始时间, 通话结束时间, 通话时长) values (2, 'HPD9JNQOG3C', 'T0HME2XWIBF', '379-4-27', '1-1-1', 6);

insert into tb_mumu (ID, 主叫号码, 被叫号码, 通话开始时间, 通话结束时间, 通话时长) values (9, 'YF3 CIJKK4', 'CTAF8Y9PJ6Q', '865-6-20 9:25:9', '1899-9-27 2:18:20', 9);

insert into tb_mumu (ID, 主叫号码, 被叫号码, 通话开始时间, 通话结束时间, 通话时长) values (3, 'MFB1KRRH9UP', 'TDHV YJ0I5T', '134-7-9 12:57:4', '280-9-15 6:23:18', 1);

insert into tb_mumu (ID, 主叫号码, 被叫号码, 通话开始时间, 通话结束时间, 通话时长) values (0, 'XWI4F7U6RTY', 'LM6T32PAW9B', '1-1-1 0:0:0', '743-4-2 23:49:42', 8);

insert into tb_mumu (ID, 主叫号码, 被叫号码, 通话开始时间, 通话结束时间, 通话时长) values (6, 'YLD89T C2N1', '4PTOKP84 1S', '1145-1-11 22:17:42', '500-9-2 17:33:12', 5);

insert into tb_mumu (ID, 主叫号码, 被叫号码, 通话开始时间, 通话结束时间, 通话时长) values (1, ' W88KNTPG6P', 'T63I 3FQNI1', '1010-8-31 19:43:4', '848-5-6 20:26:26', 7);

insert into tb_mumu (ID, 主叫号码, 被叫号码, 通话开始时间, 通话结束时间, 通话时长) values (5, 'FFXP2KCPKJC', 'E6O9E7JIL4H', '587-4-3 19:5:16', '1078-11-22 22:42:55', 0);

insert into tb_mumu (ID, 主叫号码, 被叫号码, 通话开始时间, 通话结束时间, 通话时长) values (4, '47ERHYQB5MG', 'XOW5B5YFY2L', '1599-8-20 0:17:43', '1371-6-17 1:17:59', 2);

insert into tb_mumu (ID, 主叫号码, 被叫号码, 通话开始时间, 通话结束时间, 通话时长) values (7, '1OH4X4VN9E1', '2EW0V97QD6L', '1362-11-30 5:44:58', '1531-2-23 11:46:56', 3);

insert into tb_mumu (ID, 主叫号码, 被叫号码, 通话开始时间, 通话结束时间, 通话时长) values (8, 'XN4USJ55SG4', 'KFFKLTSLOJC', '1813-06-22 19:38:51', '1741-11-22 14:23:41', 4);
修改下: 将这些数据插入 目前数据插入不了 附加留言请确保正确性然后回帖然后回答问题 O(∩_∩)O哈哈~

[NJL_ILY_2009]

真是的 ！！！

[dawugui]

--sql server写法.

select m.* from
(select t.* , px = (select count(1) from tb where 号码 = t.号码 and 开始时间 < t.开始时间) + 1 from tb) m ,
(select t.* , px = (select count(1) from tb where 号码 = t.号码 and 开始时间 < t.开始时间) + 1 from tb) n
where m.号码 = n.号码 and m.px = n.px - 1 and datediff(ss , m.结束时间 , n.开始时间 ) . 10

[tsaniki]

select 电话号码, ltrim(max(sys_connect_by_path(序号, ',')), ',') 序号
from (select 电话号码,
序号,
row_number() over(partition by 电话号码 order by 电话号码) id1,
row_number() over(order by 电话号码) + dense_rank() over(order by 电话号码) id2
from (select 电话号码, 序号 + 1 序号
from (select t.*,
(lead(开始时间, 1)
over(partition by 电话号码 order by 开始时间) - 结束时间) * 24 * 60 * 60 time
from tatata t)
where time > 10))
start with id1 = 1
connect by prior id2 = id2 - 1
group by 电话号码
order by 电话号码

[tsaniki]

不好意思 上面的有错误 下面是正解

select 电话号码, ltrim(max(sys_connect_by_path(序号, ',')), ',') 序号
from (select 电话号码,
序号,
row_number() over(partition by 电话号码 order by 电话号码) id1,
row_number() over(order by 电话号码) + dense_rank() over(order by 电话号码) id2
from (select 电话号码, 下次拨打序号 序号
from (select t.*,
lead(序号) over(partition by 电话号码 order by 开始时间) 下次拨打序号,
(lead(开始时间, 1)
over(partition by 电话号码 order by 开始时间) - 结束时间)*24*60*60 time
from 数据表 t)
where time > 10))
start with id1 = 1
connect by prior id2 = id2 - 1
group by 电话号码
order by 电话号码