某一段时间，在小于五秒钟内出现2次以上

最新推荐文章于 2022-08-03 14:40:38 发布

原创最新推荐文章于 2022-08-03 14:40:38 发布 · 106 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#Oracle

oracle 专栏收录该内容

19 篇文章

订阅专栏

本文介绍如何使用SQL查询在指定时间段内，同一连锁店同一会员卡号在相同终端连续录入两次以上且时间间隔不超过2分钟的数据。

查询
某一段时间，a在小于五秒钟内出现2次以上

with t as (
select t.a,t.b
from c_table t
where 1 = 1
and b>= to_date('2012-5-29 00:00:00', 'yyyy-MM-dd hh24:mi:ss')
and b<= to_date('2012-5-30 00:00:00', 'yyyy-MM-dd hh24:mi:ss') and t.a>'0' order by t.a
)

select t.* from t where exists(
select 1 from t a where t.a=a.a and
abs(t.b-a.b)<=5/(24*60*60) and abs(t.b-a.b)>0
)

--表信息

连锁店编号会员卡号录入员工号录入终端录入时间
2012001001 12345678 2012000001 00000001 20120510 16:29:23
2012001001 12345678 2012000001 00000001 20120510 16:30:44
2012001001 12345678 2012000001 00000001 20120510 16:39:12
2012001004 12345678 2012000002 00000001 20120510 16:41:21
2012001004 32345678 2012000002 00000002 20120510 16:43:56
2012001004 22345678 2012000002 00000001 20120510 16:46:49
2012001004 52345678 2012000002 00000002 20120510 16:47:23
2012001001 32345678 2012000003 00000001 20120510 16:49:10
2012001002 82345678 2012000003 00000002 20120510 16:53:08
2012001004 92345678 2012000003 00000003 20120510 16:57:00
2012001005 92345678 2012000003 00000002 20120510 17:01:51
2012001003 82345678 2012000003 00000003 20120510 17:02:23
查询
同一连锁店当日同一会员卡号，在相同的终端连续2次以上（含2次）录入时间=<2分钟

with t as (
select
'2012001001' a,'12345678' b,'2012000001' c,'1' d,to_date('20120510162923','yyyymmddhh24miss') e from dual union all select
'2012001001','12345678','2012000001','1',to_date('20120510163044','yyyymmddhh24miss') from dual union all select
'2012001001','12345678','2012000001','1',to_date('20120510163912','yyyymmddhh24miss')from dual union all select
'2012001004','12345678','2012000002','1',to_date('20120510164121','yyyymmddhh24miss')from dual union all select
'2012001004','32345678','2012000002','2',to_date('20120510164356','yyyymmddhh24miss')from dual union all select
'2012001004','22345678','2012000002','1',to_date('20120510164649','yyyymmddhh24miss')from dual union all select
'2012001004','52345678','2012000002','2',to_date('20120510164723','yyyymmddhh24miss')from dual union all select
'2012001001','32345678','2012000003','1',to_date('20120510164910','yyyymmddhh24miss')from dual union all select
'2012001002','82345678','2012000003','2',to_date('20120510165308','yyyymmddhh24miss')from dual union all select
'2012001004','92345678','2012000003','3',to_date('20120510165700','yyyymmddhh24miss')from dual union all select
'2012001005','92345678','2012000003','2',to_date('20120510170151','yyyymmddhh24miss')from dual union all select
'2012001003','82345678','2012000003','3',to_date('20120510170223','yyyymmddhh24miss') from dual

)

select t.* from t where exists(
select 1 from t a where t.a=a.a and t.b=a.b and t.d= a.d
and abs(t.e-a.e)<=2/(24*60) and abs(t.e-a.e)>0
)

---------------------------------
2012001001 12345678 2012000001 1 2012-5-10 16:29:23
2012001001 12345678 2012000001 1 2012-5-10 16:30:44

/*
oracle 时间相减为天数：

SELECT( to_date('20120510162923','yyyymmddhh24miss') - to_date('20120510163044','yyyymmddhh24miss'))*24 *60 *60 FROM dual
结果为天数：-0.0009375 天
化为分钟：-0.0009375 * 24 *60 = -1.35 分钟
化为秒：-1.35 分钟= -1.35 * 60 = -81秒

*/