17,140
社区成员




with t as(
select 'A' username, '2016-09-08 09:10' opttime from dual union all
select 'B' username, '2016-09-08 09:11' opttime from dual union all
select 'A' username, '2016-09-08 09:11' opttime from dual union all
select 'A' username, '2016-09-08 09:12' opttime from dual union all
select 'C' username, '2016-09-08 09:13' opttime from dual union all
select 'A' username, '2016-09-08 09:15' opttime from dual union all
select 'A' username, '2016-09-08 09:17' opttime from dual union all
select 'B' username, '2016-09-08 09:17' opttime from dual union all
select 'B' username, '2016-09-08 09:17' opttime from dual union all
select 'A' username, '2016-09-08 09:18' opttime from dual union all
select 'B' username, '2016-09-08 09:19' opttime from dual
)
select * from t;
with t as(
select 'A' username, '2016-09-08 09:10' opttime from dual union all
select 'B' username, '2016-09-08 09:11' opttime from dual union all
select 'A' username, '2016-09-08 09:11' opttime from dual union all
select 'A' username, '2016-09-08 09:12' opttime from dual union all
select 'A' username, '2016-09-08 09:14' opttime from dual union all
select 'A' username, '2016-09-08 09:14' opttime from dual union all
select 'A' username, '2016-09-08 09:15' opttime from dual union all
select 'B' username, '2016-09-08 09:17' opttime from dual union all
select 'A' username, '2016-09-08 09:18' opttime from dual union all
select 'A' username, '2016-09-08 09:19' opttime from dual union all
select 'A' username, '2016-09-08 09:20' opttime from dual
)
select username,sum(a)
from (select username, max(floor(cnt / 3)) a
from (select username,
opttime,
opttime_dt,
count(1) over(partition by username, opttime_dt - flag / 1440) cnt,FLAG
from (select username,opttime,
to_date(t.opttime, 'yyyy-mm-dd hh24:mi') opttime_dt,
dense_rank() over(partition by t.username order by opttime) flag
from t group by username,opttime))
where cnt >= 3 group by username,opttime_dt - flag / 1440)
group by username;
with t as(
select 'A' username, '2016-09-08 09:10' opttime from dual union all
select 'B' username, '2016-09-08 09:11' opttime from dual union all
select 'A' username, '2016-09-08 09:11' opttime from dual union all
select 'A' username, '2016-09-08 09:12' opttime from dual union all
select 'C' username, '2016-09-08 09:13' opttime from dual union all
select 'A' username, '2016-09-08 09:15' opttime from dual union all
select 'A' username, '2016-09-08 09:17' opttime from dual union all
select 'B' username, '2016-09-08 09:17' opttime from dual union all
select 'B' username, '2016-09-08 09:17' opttime from dual union all
select 'A' username, '2016-09-08 09:18' opttime from dual union all
select 'B' username, '2016-09-08 09:19' opttime from dual),
t1 as (select t.*,count(1) over (partition by username order by opttime range interval '1' minute preceding) cc from t),
t2 as (select t1.*,sum(case cc when 2 then 1 else 0 end) over (partition by username order by opttime)+1 dd from t1),
t3 as (select t2.*,case when trunc(dd/3)*3=dd then 1 else 0 end ee from t2)
select username,sum(ee) from t3 group by username;
t1:先找出1分钟内连续操作的记录;
t2:统计连续的次数;
t3:将其中连续次数为3的倍数记录找出来即可。