Oracle SQL分析函数lag、lead解决相邻记录比较问题
来源:互联网 发布:mp3统一音量软件 编辑:程序博客网 时间:2024/06/05 19:44
在网上看到一个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 aswith 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 dualconnect by level <= 10) t2,--构造10个通话时间段,开始时间相差 power(n+1,2)-power(n,2)= 2n+1秒,10个时间段前半部分两次通话时间都是10s以内,后面部分超过10s,通话时长简单处理,统一为2t_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 twhere (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 twhere 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
阅读全文
1 0
- Oracle SQL分析函数lag、lead解决相邻记录比较问题
- Oracle分析函数Lead(),Lag()
- oracle 分析函数 LAG、LEAD
- ORACLE分析函数 lag lead
- Oracle lag()/lead() over()分析函数
- oracle下lag和lead分析函数
- oracle 分析函数lag 和 lead 简介
- oracle lag与lead分析函数简介
- Oracle的LAG和LEAD分析函数
- Oracle下lag和lead分析函数
- Oracle的LAG和LEAD分析函数
- oracle lag与lead分析函数简介
- oracle的LAG和LEAD分析函数
- oracle下lag和lead分析函数
- oracle lag与lead分析函数简介
- oracle lag与lead分析函数简介
- oracle lag与lead分析函数简介
- oracle下lag和lead分析函数
- Shell echo命令
- java用poi实现对word读取和修改操作
- order by与group by区别
- 【原】MIT在线课程学习笔记之《Introduction to C Memory Management and C++ Object-Oriented Programming》
- 深入理解java虚拟机读书笔记(二): 运行时数据区域
- Oracle SQL分析函数lag、lead解决相邻记录比较问题
- 关于算法
- 474. Ones and Zeroes
- Deep Learning 最优化方法之SGD
- oracle 11g 和pl/sql安装时的小问题
- springboot-01工程搭建
- Linux基础(5)
- poj 1269 Intersecting Lines
- 第十二周leetcode题