在网上看到一个sql面试问题,说是一个500w条记录的通话清单表,取两次通话时间间隔大于10s的通话记录,尝试求解过程如下:
通话清单表500w条记录,表结构大致如下
table T_CALL_QD
(
id NUMBER, --递增id,按插入时间(start_time)递增的数字
call_nbr VARCHAR2(11), --主叫号码
called_nbr VARCHAR2(11), --被叫号码
start_time DATE, --开始时间
end_time DATE, --结束时间,大于开始时间
duration NUMBER --通话时长(秒)
)
求同一个号码两次通话时间之间间隔大于10s的通话记录id
--仅比较两次号码都是主叫的通话
--两次通话之间间隔是指 两次通话开始时间的间隔?还是上次通话的结束时间和本次通话的开始时间之间的间隔? 假设为前者。
--假设此表无索引
测试数据准备 Oracle 11gR2
/*
500w=50w*10
50w个号码
10个开始结束时间段
笛卡尔连接
id 是递增的
同一个号码start_time 是递增的
*/
create table t_call_qd nologging as
with t_nbr as
(
select cast('15305'||lpad(level,6,'0') as varchar2(11)) nbr from dual connect by level<=500000
--构造50w个手机号码
)
select rownum id,
t1.nbr call_nbr,
cast('15305531836' as varchar2(11)) called_nbr,
t2.start_time,
t2.end_time,
2 duration
from
(select sysdate + numtodsinterval(power(level, 2), 'second') start_time,sysdate + numtodsinterval(power(level, 2) + 2, 'second') end_time
from dual
connect by level <= 10) t2,
--构造10个通话时间段,开始时间相差 power(n+1,2)-power(n,2)= 2n+1秒,10个时间段前半部分两次通话时间都是10s以内,后面部分超过10s,通话时长简单处理,统一为2
t_nbr t1
;
select * from t_call_qd t where t.call_nbr='15305000001';
**************************无任何索引的情况*************************
全表扫描是必须的
排序是必须的,按call_nbr和start_time排序
begin dbms_stats.gather_table_stats(user,tabname => 'T_CALL_QD');end;
select segment_name,bytes/1024/1024 mb,blocks from user_segmentS t where t.segment_name='T_CALL_QD';
主要是通过分析函数lag、lead把前后的通话记录时间取出来作对比
避免自连接,全表扫描1次
--脚本
with tab as (
select t.id,
t.call_nbr,
t.start_time,
lag(t.start_time,1) over(partition by t.call_nbr order by t.start_time) before_time,
lead(t.start_time,1) over(partition by t.call_nbr order by t.start_time) after_time
from t_call_qd t
)
select id from tab t
where (t.after_time-t.start_time>10/(24*60*60) or t.start_time-t.before_time>10/(24*60*60));
--验证
with tab as (
select t.id,
t.call_nbr,
t.start_time,
lag(t.start_time,1) over(partition by t.call_nbr order by t.start_time) before_time,
lead(t.start_time,1) over(partition by t.call_nbr order by t.start_time) after_time
from t_call_qd t
)
select * from tab t
where t.call_nbr='15305000001'
and (t.after_time-t.start_time>10/(24*60*60) or t.start_time-t.before_time>10/(24*60*60));
总结,在做相邻记录比较的时候,充分利用分析函数有时候能避免将表扫描、排序2次再关联
问题出处:http://kiddymeet.blog.51cto.com/20194/23366