查询出N分钟内同一主叫拨打同一被叫次数超过M次的用户

有一张电话通话记录的表，字段有：id,主叫号码caller，被叫号码callee，开始时间begintime，结束时间endtime。
请问如何查询出N分钟内同一主叫拨打同一被叫次数超过M次的这些用户??

http://www.itpub.net/450873.html

[@more@]

By LastWinner

select caller,callee from(
select caller,callee,begintime-lag(begintime)over(partition by caller, callee order by begintime) call_interval from tab
) where call_interval begin 0 and 3/1440

如果是要超过M次的,M>=2,那么
select caller,callee from(
select caller,callee,begintime-lag(begintime,M-1)over(partition by caller, callee order by begintime) call_interval from tab
) where call_interval begin 0 and 3/1440

N分钟,则将
3/1440换成N/1440即可

By lodge
恩, 有意思, 记录和记录之间有关系, 自己和自己结合一下才能算出差异时间来
SQL1:
select t1.id, t1.caller, t1.callee, t1.begintime, t1.endtime, t2.begingtime-t1.begingtime as diff
from tab t1, tab t2
where t1.caller=t2.caller
and t1.callee=t2.callee
and t1.id<>t2.id
and t1.begintime<=t2.begintime
and t2.begintime-t1.begintime < N

有了SQL1, 求出每个特定的呼叫开始后的N时间里被呼叫了多少次就很简单啦

SELECT SQL1.id, SQL1.caller, SQL1.callee, SQL1.begintime, SQL1.endtime,COUNT(*) as CNT
from(
select t1.id, t1.caller, t1.callee, t1.begintime, t1.endtime, t2.begingtime-t1.begingtime as diff
from tab t1, tab t2
where t1.caller=t2.caller
and t1.callee=t2.callee
and t1.id<>t2.id
and t1.begintime<=t2.begintime
and t2.begintime-t1.begintime < N
) as SQL1
GROUP BY SQL1.id, SQL1.caller, SQL1.callee, SQL1.begintime, SQL1.endtime

By LastWinner(借用lodge的方法)

create view v_tab as
select id,caller,callee,begintime,endtime, rank()over(partition by caller,callee order by begintime) rk from tab
/

/*
* 查找出同一主叫同一被叫所有不同的通话记录的开始时间之差
*/
create view v2_tab as
select t1.id firstid, t2.id lastid, t1.caller, t1.callee, t1.begintime firstbt, t1.endtime firstet, t2.begintime lastbt, t2.endtime lastet, (t2.begingtime-t1.begingtime)*1440 as diff, t2.rk-t1.rk as times
from v_tab t1, v_tab t2
where t1.caller=t2.caller
and t1.callee=t2.callee
and t1.id<>t2.id
and t1.begintime<=t2.begintime
/
这样要找出同一主叫同一被叫N分钟之内,并且重复有M次通话的记录,就可很容易的得出
select firstid,lastid,firstbt,lastbt,firstet,lastet, diff, times from v2_tab
where diff between 0 and N
and times=M

类似其他一些统计也很容易得到,例如N分钟之内最多通话的同一主被叫的
select ...max(times) from v2_tab where diff between 0 and N
同一主被叫进行了M次通话,最少用的时间是
select ...trunc(diff)||'分'||trunc((diff-trunc(diff))*60)||'秒' from v2_tab where times = M
还有其他一些你自己可以慢慢发掘了:)

By LastWinner
试用分析函数进行构造
select u.rn+1 times, caller, callee,
(begintime-lag(begintime,u.rn)over(partition by u.rn, caller, callee order by begintime, id))*1440 call_interval
from tab,
(
select rownum rn from all_objects
where rownum < /*求出同一主叫同一被叫最多通话次数*/
( select max(count(*)) from tab group by caller,callee)
) u

从此结果集中去除call_interval is null的记录,就可以得到在call_interval分钟内通话次数为times的同一主叫同一被叫的结果集,这和上述的结果集应该是一致的,只不过少几个字段而已.这里lag考虑得更全面一些,order by中用到了id.因为可能出现多部分机同时call出去的现象(caller如此,callee亦然).
上面SQL中的begintime为此次呼叫的begintime,lag函数部分即为下u.rn次呼叫的begintime,若要获得其他字段,可用类似方式,如下u.rn次的caller为lag(caller,u.rn)over(partition by u.rn, caller, callee order by begintime, id)

要找出同一主叫同一被叫N分钟之内,并且重复有M次通话的记录,就可很容易的得出select * from
(select u.rn+1 times, caller, callee,
(begintime-lag(begintime,u.rn)over(partition by u.rn, caller, callee order by begintime, id))*1440 call_interval
from tab,
(
select rownum rn from all_objects
where rownum < /*求出同一主叫同一被叫最多通话次数*/
( select max(count(*)) from tab group by caller,callee)
) u
) where call_interval between 0 and N
and times = M

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29867/viewspace-809874/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29867/viewspace-809874/