急求，怎么记录每分钟出现三次的帐号

[chenzhengcan]

怎么记录每分钟出现三次的帐号

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:52

48276937 2010-5-18 11:14:32
48276937 2010-5-18 11:14:59
48276937 2010-5-18 11:15:29

有如上数据，要记录TIME一分钟内出现3条记录的ACC账号。
如48276937 为符合要求的账号。
将符合要求的账号存入另一表中。

求大侠解答~~~~~

[minitoy]

select acc from tablea
group by acc,trunc(time,'mi')
having count(*)>=3

[minitoy]

是三条，改下having

select acc from tablea
group by acc,trunc(time,'mi')
having count(*)=3

[csuxp2008]

[Quote=引用 2 楼 minitoy 的回复:]
是三条，改下having

SQL code
select acc from tablea
group by acc,trunc(time,'mi')
having count(*)=3
[/Quote]

有点问题，按照你的sql

48276937 2010-5-18 11:14:32
48276937 2010-5-18 11:14:59
48276937 2010-5-18 11:15:29

15分的这条记录就不会跟14分的2条记录在同一个分组了，不符合LZ的要求

[minitoy]

还真是的。那就比较麻烦了[Quote=引用 3 楼 csuxp2008 的回复:]
引用 2 楼 minitoy 的回复:
是三条，改下having

SQL code
select acc from tablea
group by acc,trunc(time,'mi')
having count(*)=3


有点问题，按照你的sql

48276937 2010-5-18 11:14:32
48276937 2010-5-18 11:14:59
4……
[/Quote]

[ngx20080110]

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

[czfxwpy]

思路：计算出同一个acc下，其距离上一个的时间，距离下一个的时间，找出两个时间相加小于60s的，再distinct一下acc。

create table T4
(
  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;

[「已注销」]

学习分析函数

[xman_78tom]

  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

[caofaping]

 with test as(
    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; 

[herohuaxu]

给你一个不是分析函数的，很好看懂的

select acc
  from t
 group by acc
having count(*) >= 3 and (max(time) - min(time)) * 60 * 60 * 24 between 0 and 60

[scorpions_z]

楼上没有考虑到同一acc有超过三条记录的吧？

[herohuaxu]

你没有看到having count(*) >= 3吗

[luoyoumou]

[Quote=引用楼主 chenzhengcan 的回复:]
怎么记录每分钟出现三次的帐号

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:52……
[/Quote]


-- 你这个每分钟，应该有个时间段内吧？（比如：2010-08-24 12:12:00 到 2010-08-24 15:14:00)

[minitoy]

高[Quote=引用 10 楼 herohuaxu 的回复:]
给你一个不是分析函数的，很好看懂的

SQL code

select acc
from t
group by acc
having count(*) >= 3 and (max(time) - min(time)) * 60 * 60 * 24 between 0 and 60
[/Quote]

[landyshouguo]

[Quote=引用 10 楼 herohuaxu 的回复:]
给你一个不是分析函数的，很好看懂的

SQL code

select acc
from t
group by acc
having count(*) >= 3 and (max(time) - min(time)) * 60 * 60 * 24 between 0 and 60
[/Quote]
思路好啊

[jife9910]

10L的思路很好，不过也只能找出账号，无法定位到时间，给个直接得到楼主想要的结果的

create table t as(
   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

[jife9910]

1 48276937 2010-5-18 11:14:32
2 48276937 2010-5-18 11:14:59
3 48276937 2010-5-18 11:15:29


所得结果

[joywin]

如果一个ACC有四条记录，有三条在一分钟内，第四条是过了很久的，也就是max(time)很大，这样会误判吧？
[Quote=引用 12 楼 landyshouguo 的回复:]
引用 10 楼 herohuaxu 的回复:
给你一个不是分析函数的，很好看懂的

SQL code

select acc
from t
group by acc
having count(*) >= 3 and (max(time) - min(time)) * 60 * 60 * 24 between 0 and 60

思路好啊
[/Quote]