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

zl_c 2016-09-08 09:29:42
查询条件：同一用户每连续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;
...全文
458 14 打赏 收藏 转发到动态 举报
写回复
用AI写文章
14 条回复
切换为时间正序
请发表友善的回复…
发表回复
ghx287524027 2016-09-09
  • 打赏
  • 举报
 回复
引用 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为你要的结果[/quote] 感谢回复！ 按您的方法，如果有4条间隔不大于1分钟的连续操作记录，会得出2次违规的情况，而我要的是每满3次算1次违规，4次记录应该算1次违规。 如：10：01，10：02，10：03，10：04，共有4条连续操作记录间隔不大于1分钟，算1次违规，结余的这条记录不累计到下一次的计算[/quote] 参照10#的
zl_c 2016-09-09
  • 打赏
  • 举报
 回复
引用 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为你要的结果[/quote] 感谢回复！ 按您的方法，如果有4条间隔不大于1分钟的连续操作记录，会得出2次违规的情况，而我要的是每满3次算1次违规，4次记录应该算1次违规。 如：10：01，10：02，10：03，10：04，共有4条连续操作记录间隔不大于1分钟，算1次违规，结余的这条记录不累计到下一次的计算
zl_c 2016-09-09
  • 打赏
  • 举报
 回复
引用 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次违规，结余的这条记录不累计到下一次的计算。
ben01234 2016-09-09
  • 打赏
  • 举报
 回复
引用 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;
ghx287524027 2016-09-09
  • 打赏
  • 举报
 回复
引用 8 楼 wildwave 的回复:
[quote=引用 6 楼 mayanzs 的回复:] 3楼很巧妙，我测试了一下，对于全连续不间断的结果正确，但对不全连续不对，如 A有 9：10，9：11，9：12 及 10：10，10：11，10：12，那么A应该有二次违规，但却只得出了1 次违规。
楼主要求计算出“每次“的违规记录，在我理解来每次就是指连续的[/quote] 应该就是连续的意思吧，如果出现断点的话，那就要从这个间断时间点开始重新计时3分钟，否则就说不过去了
不写代码的钦 2016-09-09
  • 打赏
  • 举报
 回复
引用 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为你要的结果[/quote] 感谢回复！ 按您的方法，如果有4条间隔不大于1分钟的连续操作记录，会得出2次违规的情况，而我要的是每满3次算1次违规，4次记录应该算1次违规。 如：10：01，10：02，10：03，10：04，共有4条连续操作记录间隔不大于1分钟，算1次违规，结余的这条记录不累计到下一次的计算[/quote] 10# 是对的.
zl_c 2016-09-08
  • 打赏
  • 举报
 回复
引用 1 楼 js14982 的回复:
有点歧义 每连续3分钟都有操作记录，指3分钟内有2次操作就算违规？ 即连续6次每分钟都有操作记录算违规2次，指6分钟，每分钟都有操作记录才算违规，就是最少有6次？
是指：连续3次每分钟都操作记录算违规1次。例如：9：10，9：11，9：12都有操作记录，算违规1次。
js14982 2016-09-08
  • 打赏
  • 举报
 回复
有点歧义 每连续3分钟都有操作记录，指3分钟内有2次操作就算违规？ 即连续6次每分钟都有操作记录算违规2次，指6分钟，每分钟都有操作记录才算违规，就是最少有6次？
小灰狼W 2016-09-08
  • 打赏
  • 举报
 回复
引用 6 楼 mayanzs 的回复:
3楼很巧妙，我测试了一下，对于全连续不间断的结果正确，但对不全连续不对，如 A有 9：10，9：11，9：12 及 10：10，10：11，10：12，那么A应该有二次违规，但却只得出了1 次违规。
楼主要求计算出“每次“的违规记录，在我理解来每次就是指连续的
不写代码的钦 2016-09-08
  • 打赏
  • 举报
 回复
引用 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为你要的结果
mayanzs 2016-09-08
  • 打赏
  • 举报
 回复
3楼很巧妙，我测试了一下，对于全连续不间断的结果正确，但对不全连续不对，如 A有 9：10，9：11，9：12 及 10：10，10：11，10：12，那么A应该有二次违规，但却只得出了1 次违规。
mayanzs 2016-09-08
  • 打赏
  • 举报
 回复
抱歉，要将 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
mayanzs 2016-09-08
  • 打赏
  • 举报
 回复 
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的倍数记录找出来即可。
小灰狼W 2016-09-08
  • 打赏
  • 举报
 回复
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为违规次数
一个查询的SQL语句请教,希望能够用一条SQL语句得到结果
一个查询的SQL语句请教,希望能够用一条SQL语句得到结果
一条sql语句完成MySQL去重留一
最快想到的是可以通过一条sql语句来解决，无奈自己对于复杂sql语句的道行太浅，所以想找大佬帮忙。 找人帮忙 因为这个需求有点着急，所以最开始想到的是，可以找这方面的同行来解决，然后分享这个问题给同事，结果这...
7天带你玩转Mysql数据库之SQL语句
您观看课程学习后 免费入群领取【超全Python资料包+17本学习电子书】 七年SQL语句编写经验。满满的干货！ 在网上找了好多SQL数据库的教程，但你还会面临这样的问题： 1. 自己写了SQL却不知道对不对。 2. 在上线后...
asp.net知识库
直接从SQL语句问题贴子数据建表并生成建表语句的存储过程 从SQL中的一个表中导出HTML文件表格 获取數据库表的前N条记录 几段SQL Server语句和存储过程 生成表中的数据的脚本 最详细的SQL注入相关的命令整理 Oracle ...
请教一条SQL查询语句
这SQL语句怎么写? -- > 测试数据：[TB] if object_id ( ' [TB] ' ) is not null drop table [ TB ] create table [ TB ] ( [ Team ] varchar ( 1 ), [ PO ] varchar ( 4 ), [ Item ] ...
Oracle

17,082

社区成员

55,235

社区内容

发帖
与我相关
我的任务
社区描述
Oracle开发相关技术讨论
社区管理员
  • 开发
  • Lucifer三思而后行
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章