怎么记录每分钟出现三次的帐号ACC TIME
47554950 2010-5-18 11:29:26
47554950 2010-5-18 11:30:16
47554950 2010-5-18 11:31:42
48179048 2010-5-18 16:10:32
48179048 2010-5-18 16:11:19
48179048 2010-5-18 16:20:5248276937 2010-5-18 11:14:32
48276937 2010-5-18 11:14:59
48276937 2010-5-18 11:15:29有如上数据,要记录TIME一分钟内出现3条记录的ACC账号。
如48276937 为符合要求的账号。
将符合要求的账号存入另一表中。求大侠解答~~~~~
47554950 2010-5-18 11:29:26
47554950 2010-5-18 11:30:16
47554950 2010-5-18 11:31:42
48179048 2010-5-18 16:10:32
48179048 2010-5-18 16:11:19
48179048 2010-5-18 16:20:5248276937 2010-5-18 11:14:32
48276937 2010-5-18 11:14:59
48276937 2010-5-18 11:15:29有如上数据,要记录TIME一分钟内出现3条记录的ACC账号。
如48276937 为符合要求的账号。
将符合要求的账号存入另一表中。求大侠解答~~~~~
group by acc,trunc(time,'mi')
having count(*)>=3
select acc from tablea
group by acc,trunc(time,'mi')
having count(*)=3
有点问题,按照你的sql48276937 2010-5-18 11:14:32
48276937 2010-5-18 11:14:59
48276937 2010-5-18 11:15:2915分的这条记录就不会跟14分的2条记录在同一个分组了,不符合LZ的要求
create table records (acc number, dt date);-- 1 time
insert into records values (123, to_date('20100518112926', 'yyyymmddhh24miss'));
-- 2 times
insert into records values (124, to_date('20100518112926', 'yyyymmddhh24miss'));
insert into records values (124, to_date('20100518113005', 'yyyymmddhh24miss'));
-- 3 times not in 1 minute
insert into records values (125, to_date('20100518112926', 'yyyymmddhh24miss'));
insert into records values (125, to_date('20100518113005', 'yyyymmddhh24miss'));
insert into records values (125, to_date('20100518113058', 'yyyymmddhh24miss'));
-- 3 times in 1 minute
insert into records values (126, to_date('20100518112926', 'yyyymmddhh24miss'));
insert into records values (126, to_date('20100518113005', 'yyyymmddhh24miss'));
insert into records values (126, to_date('20100518113025', 'yyyymmddhh24miss'));
-- 4 times in 1 minute
insert into records values (127, to_date('20100518112955', 'yyyymmddhh24miss'));
insert into records values (127, to_date('20100518113005', 'yyyymmddhh24miss'));
insert into records values (127, to_date('20100518113025', 'yyyymmddhh24miss'));
insert into records values (127, to_date('20100518113048', 'yyyymmddhh24miss'));
-- 4 times not in 1 minute
insert into records values (128, to_date('20100518112955', 'yyyymmddhh24miss'));
insert into records values (128, to_date('20100518113005', 'yyyymmddhh24miss'));
insert into records values (128, to_date('20100518113058', 'yyyymmddhh24miss'));
insert into records values (128, to_date('20100518113148', 'yyyymmddhh24miss'));commit;select distinct acc from (
select acc, dt, dt - lag(dt, 2) over (partition by acc order by dt) vlag
from records
) where vlag is not null and vlag < 1 / (24 * 60);
-- 1分鐘 = 1 / (24 * 60) ACC
----------
126
127
(
ACC VARCHAR2(8),
TIME DATE
)SELECT * FROM T4;insert into t4 values(
'47554950', to_date('20100518 11:29:26','yyyymmdd hh:mi:ss'));
INSERT INTO t4 VALUES(
'47554950', to_date('20100518 11:30:16','yyyymmdd hh:mi:ss'));
INSERT INTO t4 VALUES(
'47554950', to_date('20100518 11:31:42','yyyymmdd hh:mi:ss'));
INSERT INTO t4 VALUES(
'48179048', to_date('20100518 16:10:32','yyyymmdd hh24:mi:ss'));
INSERT INTO t4 VALUES(
'48179048', to_date('20100518 16:11:19','yyyymmdd hh24:mi:ss'));
INSERT INTO t4 VALUES(
'48179048', to_date('20100518 16:20:52','yyyymmdd hh24:mi:ss'));
INSERT INTO t4 VALUES(
'48276937', to_date('20100518 11:14:32','yyyymmdd hh24:mi:ss'));
INSERT INTO t4 VALUES(
'48276937', to_date('20100518 11:14:59','yyyymmdd hh24:mi:ss'));
INSERT INTO t4 VALUES(
'48276937', to_date('20100518 11:15:29','yyyymmdd hh24:mi:ss'));SELECT distinct acc FROM (
SELECT acc,time,lag(time,1,null) over(partition by acc order by time) last_time,
(time-lag(time,1,null)over(partition by acc order by time) )*24*60*60 times_last,
lead(time,1,null)over(partition by acc order by time) next_time,
(lead(time,1,null)over(partition by acc order by time)-time )*24*60*60 times_next
from t4
order by acc,time ) t
WHERE (times_last+times_next)<=60;
1 with t as(
2 select 47554950 acc,timestamp'2010-5-18 11:29:26' time from dual
3 union all
4 select 47554950,timestamp'2010-5-18 11:30:16' from dual
5 union all
6 select 47554950,timestamp'2010-5-18 11:31:42' from dual
7 union all
8 select 48179048,timestamp'2010-5-18 16:10:32' from dual
9 union all
10 select 48179048,timestamp'2010-5-18 16:11:19' from dual
11 union all
12 select 48179048,timestamp'2010-5-18 16:20:52' from dual
13 union all
14 select 48276937,timestamp'2010-5-18 11:14:32' from dual
15 union all
16 select 48276937,timestamp'2010-5-18 11:14:59' from dual
17 union all
18 select 48276937,timestamp'2010-5-18 11:15:29' from dual
19 )
20 select distinct acc from t t1
21* where (select count(*) from t where acc=t1.acc and time between t1.time and t1.time+interval '1' minute)=3
SQL> / ACC
----------
48276937
select 47554950 acc,'2010-5-18 11:29:26' time from dual
union all
select 47554950 acc,'2010-5-18 11:30:16'time from dual
union all
select 47554950 acc,'2010-5-18 11:31:42'time from dual
union all
select 48179048 acc,'2010-5-18 16:10:32'time from dual
union all
select 48179048 acc,'2010-5-18 16:11:19'time from dual
union all
select 48179048 acc,'2010-5-18 16:20:52'time from dual
union all
select 48276937 acc,'2010-5-18 11:14:32'time from dual
union all
select 48276937 acc,'2010-5-18 11:14:59'time from dual
union all
select 48276937 acc,'2010-5-18 11:15:29'time from dual
)select t.acc, t.time from test t, (select t1.acc,count(*) num,
(to_date(max(t1.time),'yyyy-mm-dd hh24:mi:ss') -to_date(min(t1.time),'yyyy-mm-dd hh24:mi:ss'))*1000*60 tm
from test t1 group by t1.acc) t2
where t2.acc = t.acc
and t2.tm <= 60
and t2.num = 3;
from t
group by acc
having count(*) >= 3 and (max(time) - min(time)) * 60 * 60 * 24 between 0 and 60
select 47554950 acc,to_date('2010-5-18 11:29:26','yyyy-mm-dd hh24:mi:ss') time from dual
union all
select 47554950,to_date('2010-5-18 11:30:16','yyyy-mm-dd hh24:mi:ss') from dual
union all
select 47554950,to_date('2010-5-18 11:31:42','yyyy-mm-dd hh24:mi:ss') from dual
union all
select 48179048,to_date('2010-5-18 16:10:32','yyyy-mm-dd hh24:mi:ss') from dual
union all
select 48179048,to_date('2010-5-18 16:11:19','yyyy-mm-dd hh24:mi:ss') from dual
union all
select 48179048,to_date('2010-5-18 16:20:52','yyyy-mm-dd hh24:mi:ss') from dual
union all
select 48276937,to_date('2010-5-18 11:14:32','yyyy-mm-dd hh24:mi:ss') from dual
union all
select 48276937,to_date('2010-5-18 11:14:59','yyyy-mm-dd hh24:mi:ss') from dual
union all
select 48276937,to_date('2010-5-18 11:15:29','yyyy-mm-dd hh24:mi:ss') from dual
) select acc,time from (select acc,time,(lead(time,2) over(partition by acc order by time)-time)*60*24*60 as dif from t) tt where tt.dif<60
union all
select acc,time from (select acc,time,((lead(time) over(partition by acc order by time))-(lag(time) over(partition by acc order by time)))*24*60*60 as dif from t) tt where tt.dif<60
union all
select acc,time from (select acc,time,(time-lag(time,2) over(partition by acc order by time))*24*60*60 as dif from t) tt where tt.dif<60