请教一条SQL查询语句。。。。

查询条件：同一用户每连续3分钟都有操作记录算该用户违规1次，以此类推，即连续6次每分钟都有操作记录算违规2次，请将这些操作记录查出来，并算出用户每次的违规次数。
数据：

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;

14 个解决方案

#1

有点歧义
每连续3分钟都有操作记录，指3分钟内有2次操作就算违规？
即连续6次每分钟都有操作记录算违规2次，指6分钟，每分钟都有操作记录才算违规，就是最少有6次？

#2

引用 1 楼 js14982 的回复:

有点歧义
每连续3分钟都有操作记录，指3分钟内有2次操作就算违规？
即连续6次每分钟都有操作记录算违规2次，指6分钟，每分钟都有操作记录才算违规，就是最少有6次？

是指：连续3次每分钟都操作记录算违规1次。例如：9：10，9：11，9：12都有操作记录，算违规1次。

#3

select username, opttime, floor(cnt / 3) times
  from (select username,
               opttime,
               opttime_dt,
               count(1) over(partition by username, opttime_dt - flag / 1440) cnt
          from (select t.*,
                       to_date(t.opttime, 'yyyy-mm-dd hh24:mi') opttime_dt,
                       row_number() over(partition by t.username order by opttime) flag
                  from t))
where cnt >= 3;

手头没有环境，没法验证。试试看吧
输出的是存在违规的相关记录，times为违规次数

#4

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的倍数记录找出来即可。

#5

抱歉，要将 select 'A' username, '2016-09-08 09:10' opttime from dual
改成 select 'A' username, to_date('2016-09-08 09:10','yyyy-mm-dd hh24:mi) opttime from dual

#6

3楼很巧妙，我测试了一下，对于全连续不间断的结果正确，但对不全连续不对，如
A有 9：10，9：11，9：12 及 10：10，10：11，10：12，那么A应该有二次违规，但却只得出了1 次违规。

#7

引用 6 楼 mayanzs 的回复:

3楼很巧妙，我测试了一下，对于全连续不间断的结果正确，但对不全连续不对，如
A有 9：10，9：11，9：12 及 10：10，10：11，10：12，那么A应该有二次违规，但却只得出了1 次违规。

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 union all

  select 'A' username, '2016-09-08 10:10' opttime from dual union all
  select 'A' username, '2016-09-08 10:11' opttime from dual union all
  select 'A' username, '2016-09-08 10:12' opttime from dual

)
select username,opttime,back,front,sum(case when to_date(opttime,'yyyy-mm-dd hh24:mi')-to_date(back,'yyyy-mm-dd hh24:mi')=1/(24*60)
and to_date(front,'yyyy-mm-dd hh24:mi')-to_date(opttime,'yyyy-mm-dd hh24:mi')=1/(24*60) then 1 else 0 end) tot
from (
select username,opttime,
lead(opttime) over(partition by username order by opttime desc) back,
lead(opttime) over(partition by username order by opttime) front
from t)
group by username,username,opttime,back,front
;

tot>0为你要的结果

#8

引用 6 楼 mayanzs 的回复:

3楼很巧妙，我测试了一下，对于全连续不间断的结果正确，但对不全连续不对，如
A有 9：10，9：11，9：12 及 10：10，10：11，10：12，那么A应该有二次违规，但却只得出了1 次违规。

楼主要求计算出“每次“的违规记录，在我理解来每次就是指连续的

#9

引用 8 楼 wildwave 的回复:

Quote: 引用 6 楼 mayanzs 的回复:

3楼很巧妙，我测试了一下，对于全连续不间断的结果正确，但对不全连续不对，如
A有 9：10，9：11，9：12 及 10：10，10：11，10：12，那么A应该有二次违规，但却只得出了1 次违规。

楼主要求计算出“每次“的违规记录，在我理解来每次就是指连续的

应该就是连续的意思吧，如果出现断点的话，那就要从这个间断时间点开始重新计时3分钟，否则就说不过去了

#10

引用 3 楼 wildwave 的回复:

select username, opttime, floor(cnt / 3) times
  from (select username,
               opttime,
               opttime_dt,
               count(1) over(partition by username, opttime_dt - flag / 1440) cnt
          from (select t.*,
                       to_date(t.opttime, 'yyyy-mm-dd hh24:mi') opttime_dt,
                       row_number() over(partition by t.username order by opttime) flag
                  from t))
where cnt >= 3;

手头没有环境，没法验证。试试看吧
输出的是存在违规的相关记录，times为违规次数

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;

#11

引用 4 楼 mayanzs 的回复:

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的倍数记录找出来即可。

感谢回复！
这样查出来的是2次操作间隔小于1分钟的记录，我要找的是：连续3次操作间隔不大于1分钟的记录。这样连续操作的记录，每满3条算1次违规。
如：10：01，10：02，10：03，10：04，共有4条连续操作记录间隔不大于1分钟，算1次违规，结余的这条记录不累计到下一次的计算。

#12

引用 7 楼 arlen1990 的回复:

Quote: 引用 6 楼 mayanzs 的回复:

3楼很巧妙，我测试了一下，对于全连续不间断的结果正确，但对不全连续不对，如
A有  9：10，9：11，9：12 及 10：10，10：11，10：12，那么A应该有二次违规，但却只得出了1 次违规。

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 union all

  select 'A' username, '2016-09-08 10:10' opttime from dual union all
  select 'A' username, '2016-09-08 10:11' opttime from dual union all
  select 'A' username, '2016-09-08 10:12' opttime from dual

)
select username,opttime,back,front,sum(case when to_date(opttime,'yyyy-mm-dd hh24:mi')-to_date(back,'yyyy-mm-dd hh24:mi')=1/(24*60)
and to_date(front,'yyyy-mm-dd hh24:mi')-to_date(opttime,'yyyy-mm-dd hh24:mi')=1/(24*60) then 1 else 0 end) tot
from (
select username,opttime,
lead(opttime) over(partition by username order by opttime desc) back,
lead(opttime) over(partition by username order by opttime) front
from t)
group by username,username,opttime,back,front
;

tot>0为你要的结果

感谢回复！

按您的方法，如果有4条间隔不大于1分钟的连续操作记录，会得出2次违规的情况，而我要的是每满3次算1次违规，4次记录应该算1次违规。

如：10：01，10：02，10：03，10：04，共有4条连续操作记录间隔不大于1分钟，算1次违规，结余的这条记录不累计到下一次的计算

#13

引用 12 楼 zl_c 的回复:

Quote: 引用 7 楼 arlen1990 的回复:

Quote: 引用 6 楼 mayanzs 的回复:

3楼很巧妙，我测试了一下，对于全连续不间断的结果正确，但对不全连续不对，如
A有  9：10，9：11，9：12 及 10：10，10：11，10：12，那么A应该有二次违规，但却只得出了1 次违规。

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 union all

  select 'A' username, '2016-09-08 10:10' opttime from dual union all
  select 'A' username, '2016-09-08 10:11' opttime from dual union all
  select 'A' username, '2016-09-08 10:12' opttime from dual

)
select username,opttime,back,front,sum(case when to_date(opttime,'yyyy-mm-dd hh24:mi')-to_date(back,'yyyy-mm-dd hh24:mi')=1/(24*60)
and to_date(front,'yyyy-mm-dd hh24:mi')-to_date(opttime,'yyyy-mm-dd hh24:mi')=1/(24*60) then 1 else 0 end) tot
from (
select username,opttime,
lead(opttime) over(partition by username order by opttime desc) back,
lead(opttime) over(partition by username order by opttime) front
from t)
group by username,username,opttime,back,front
;

tot>0为你要的结果

感谢回复！

按您的方法，如果有4条间隔不大于1分钟的连续操作记录，会得出2次违规的情况，而我要的是每满3次算1次违规，4次记录应该算1次违规。

如：10：01，10：02，10：03，10：04，共有4条连续操作记录间隔不大于1分钟，算1次违规，结余的这条记录不累计到下一次的计算

参照10#的

#14

引用 12 楼 zl_c 的回复:

Quote: 引用 7 楼 arlen1990 的回复:

Quote: 引用 6 楼 mayanzs 的回复:

3楼很巧妙，我测试了一下，对于全连续不间断的结果正确，但对不全连续不对，如
A有  9：10，9：11，9：12 及 10：10，10：11，10：12，那么A应该有二次违规，但却只得出了1 次违规。

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 union all

  select 'A' username, '2016-09-08 10:10' opttime from dual union all
  select 'A' username, '2016-09-08 10:11' opttime from dual union all
  select 'A' username, '2016-09-08 10:12' opttime from dual

)
select username,opttime,back,front,sum(case when to_date(opttime,'yyyy-mm-dd hh24:mi')-to_date(back,'yyyy-mm-dd hh24:mi')=1/(24*60)
and to_date(front,'yyyy-mm-dd hh24:mi')-to_date(opttime,'yyyy-mm-dd hh24:mi')=1/(24*60) then 1 else 0 end) tot
from (
select username,opttime,
lead(opttime) over(partition by username order by opttime desc) back,
lead(opttime) over(partition by username order by opttime) front
from t)
group by username,username,opttime,back,front
;

tot>0为你要的结果

感谢回复！

按您的方法，如果有4条间隔不大于1分钟的连续操作记录，会得出2次违规的情况，而我要的是每满3次算1次违规，4次记录应该算1次违规。

如：10：01，10：02，10：03，10：04，共有4条连续操作记录间隔不大于1分钟，算1次违规，结余的这条记录不累计到下一次的计算

10#
是对的.

#1

#2

引用 1 楼 js14982 的回复:

有点歧义
每连续3分钟都有操作记录，指3分钟内有2次操作就算违规？
即连续6次每分钟都有操作记录算违规2次，指6分钟，每分钟都有操作记录才算违规，就是最少有6次？

是指：连续3次每分钟都操作记录算违规1次。例如：9：10，9：11，9：12都有操作记录，算违规1次。

#3

#4

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的倍数记录找出来即可。

#5

抱歉，要将 select 'A' username, '2016-09-08 09:10' opttime from dual
改成 select 'A' username, to_date('2016-09-08 09:10','yyyy-mm-dd hh24:mi) opttime from dual

#6

#7

引用 6 楼 mayanzs 的回复:

3楼很巧妙，我测试了一下，对于全连续不间断的结果正确，但对不全连续不对，如
A有 9：10，9：11，9：12 及 10：10，10：11，10：12，那么A应该有二次违规，但却只得出了1 次违规。

#8

引用 6 楼 mayanzs 的回复:

3楼很巧妙，我测试了一下，对于全连续不间断的结果正确，但对不全连续不对，如
A有 9：10，9：11，9：12 及 10：10，10：11，10：12，那么A应该有二次违规，但却只得出了1 次违规。

楼主要求计算出“每次“的违规记录，在我理解来每次就是指连续的

#9

引用 8 楼 wildwave 的回复:

Quote: 引用 6 楼 mayanzs 的回复:

3楼很巧妙，我测试了一下，对于全连续不间断的结果正确，但对不全连续不对，如
A有 9：10，9：11，9：12 及 10：10，10：11，10：12，那么A应该有二次违规，但却只得出了1 次违规。

楼主要求计算出“每次“的违规记录，在我理解来每次就是指连续的

应该就是连续的意思吧，如果出现断点的话，那就要从这个间断时间点开始重新计时3分钟，否则就说不过去了

#10

引用 3 楼 wildwave 的回复:

select username, opttime, floor(cnt / 3) times
  from (select username,
               opttime,
               opttime_dt,
               count(1) over(partition by username, opttime_dt - flag / 1440) cnt
          from (select t.*,
                       to_date(t.opttime, 'yyyy-mm-dd hh24:mi') opttime_dt,
                       row_number() over(partition by t.username order by opttime) flag
                  from t))
where cnt >= 3;

手头没有环境，没法验证。试试看吧
输出的是存在违规的相关记录，times为违规次数

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;

#11

引用 4 楼 mayanzs 的回复:

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的倍数记录找出来即可。

#12

引用 7 楼 arlen1990 的回复:

Quote: 引用 6 楼 mayanzs 的回复:

3楼很巧妙，我测试了一下，对于全连续不间断的结果正确，但对不全连续不对，如
A有  9：10，9：11，9：12 及 10：10，10：11，10：12，那么A应该有二次违规，但却只得出了1 次违规。

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 union all

  select 'A' username, '2016-09-08 10:10' opttime from dual union all
  select 'A' username, '2016-09-08 10:11' opttime from dual union all
  select 'A' username, '2016-09-08 10:12' opttime from dual

)
select username,opttime,back,front,sum(case when to_date(opttime,'yyyy-mm-dd hh24:mi')-to_date(back,'yyyy-mm-dd hh24:mi')=1/(24*60)
and to_date(front,'yyyy-mm-dd hh24:mi')-to_date(opttime,'yyyy-mm-dd hh24:mi')=1/(24*60) then 1 else 0 end) tot
from (
select username,opttime,
lead(opttime) over(partition by username order by opttime desc) back,
lead(opttime) over(partition by username order by opttime) front
from t)
group by username,username,opttime,back,front
;

tot>0为你要的结果

#13

引用 12 楼 zl_c 的回复:

Quote: 引用 7 楼 arlen1990 的回复:

Quote: 引用 6 楼 mayanzs 的回复:

3楼很巧妙，我测试了一下，对于全连续不间断的结果正确，但对不全连续不对，如
A有  9：10，9：11，9：12 及 10：10，10：11，10：12，那么A应该有二次违规，但却只得出了1 次违规。

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 union all

  select 'A' username, '2016-09-08 10:10' opttime from dual union all
  select 'A' username, '2016-09-08 10:11' opttime from dual union all
  select 'A' username, '2016-09-08 10:12' opttime from dual

)
select username,opttime,back,front,sum(case when to_date(opttime,'yyyy-mm-dd hh24:mi')-to_date(back,'yyyy-mm-dd hh24:mi')=1/(24*60)
and to_date(front,'yyyy-mm-dd hh24:mi')-to_date(opttime,'yyyy-mm-dd hh24:mi')=1/(24*60) then 1 else 0 end) tot
from (
select username,opttime,
lead(opttime) over(partition by username order by opttime desc) back,
lead(opttime) over(partition by username order by opttime) front
from t)
group by username,username,opttime,back,front
;

tot>0为你要的结果

感谢回复！

按您的方法，如果有4条间隔不大于1分钟的连续操作记录，会得出2次违规的情况，而我要的是每满3次算1次违规，4次记录应该算1次违规。

如：10：01，10：02，10：03，10：04，共有4条连续操作记录间隔不大于1分钟，算1次违规，结余的这条记录不累计到下一次的计算

参照10#的

#14

引用 12 楼 zl_c 的回复:

Quote: 引用 7 楼 arlen1990 的回复:

Quote: 引用 6 楼 mayanzs 的回复:

3楼很巧妙，我测试了一下，对于全连续不间断的结果正确，但对不全连续不对，如
A有  9：10，9：11，9：12 及 10：10，10：11，10：12，那么A应该有二次违规，但却只得出了1 次违规。

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 union all

  select 'A' username, '2016-09-08 10:10' opttime from dual union all
  select 'A' username, '2016-09-08 10:11' opttime from dual union all
  select 'A' username, '2016-09-08 10:12' opttime from dual

)
select username,opttime,back,front,sum(case when to_date(opttime,'yyyy-mm-dd hh24:mi')-to_date(back,'yyyy-mm-dd hh24:mi')=1/(24*60)
and to_date(front,'yyyy-mm-dd hh24:mi')-to_date(opttime,'yyyy-mm-dd hh24:mi')=1/(24*60) then 1 else 0 end) tot
from (
select username,opttime,
lead(opttime) over(partition by username order by opttime desc) back,
lead(opttime) over(partition by username order by opttime) front
from t)
group by username,username,opttime,back,front
;

tot>0为你要的结果

感谢回复！

按您的方法，如果有4条间隔不大于1分钟的连续操作记录，会得出2次违规的情况，而我要的是每满3次算1次违规，4次记录应该算1次违规。

如：10：01，10：02，10：03，10：04，共有4条连续操作记录间隔不大于1分钟，算1次违规，结余的这条记录不累计到下一次的计算

10#
是对的.

秒客网

请教一条SQL查询语句。。。。

14 个解决方案

#1

#2

#3

#4

#5

#6

#7

#8

#9

#10

#11

#12

#13

#14

#1

#2

#3

#4

#5

#6

#7

#8

#9

#10

#11

#12

#13

#14

相关文章