请教一条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;
...全文
470 14 打赏 收藏 转发到动态 举报
AI 作业
写回复
用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语句得到结果
ODBC_数据库链接函数库
ODBC_数据库链接函数库.doc
第三章：数据表管理(2)：创建表
第三章包含以下内容： 1.数据表简介 2.创建表 3.临时表，删除表，编写表脚本 4.修改表 前言 非科班自学SQLSERVER和C#编程两年。该系列文章中涉及的知识点是在工作中需要用到的一些基础知识。文章比较适合初识数据库的学生或自学数据库的成人，文章是以SSMS作为例子。如果有认知错误的地方请大家指正。 定义数据表的字段和主键 我们有两种方式可以创建数据表的和定义主键：SSMS界面直接创建、T-SQL语句。 1.SSMS界面创建表 (1).登陆你的数据库，点开右键”表”—-》新建—-》表。 (2).列名就是该列名称，数据类型就是前文所讲的数据类型。允许NULL值，勾选以后就代表可以为NUL
请教一条SQL查询语句

Team PO Item Serial Sdate
A A100 L001 0603-E 2010-9-10
A A101 L001 0603-E 2010-9-10
A A102 L002 0603-E 2010-9-10
A A103 L001 0603-E 2010-9-11
B B100 N001 0806-M 2010-9-10
B B101 N001 0806-M 2010-9-11
B B102 L001 060
oracle如何加快语句执行,一条sql语句，执行太慢了，请问下如何优化
当前位置:我的异常网» Oracle开发»一条sql语句，执行太慢了，请问下如何优化一条sql语句，执行太慢了，请问下如何优化www.myexceptions.net网友分享于：2013-02-24浏览：76次一条sql语句，执行太慢了，请教下怎么优化一条sql语句，执行太慢了，请教下怎么优化。主要是里面有31条子查询，子查询里面又有联合查询，所以才比较慢。请教一下改怎么优化啊？语句如...
Oracle

17,140

社区成员

55,258

社区内容

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

加载中

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

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

+ 用AI写文章