我的一个统计时间片是截取到分钟的 现在我想把他弄成5秒内 请高手帮帮select mobile, trunc(savetime, 'mi'), count(mid) from test.waphistory
where savetime>=to_date('2008-10-1','yyyy-mm-dd') and savetime<to_date('2008-11-1','yyyy-mm-dd') and actionid in(1,4) and mobile<>0
group by mobile, trunc(savetime, 'mi')
having count(mid) >1
where savetime>=to_date('2008-10-1','yyyy-mm-dd') and savetime<to_date('2008-11-1','yyyy-mm-dd') and actionid in(1,4) and mobile<>0
group by mobile, trunc(savetime, 'mi')
having count(mid) >1
5)*5/(24*60*60), 'yyyy-mm-dd hh24:mi:ss') fivesec, to_char(sysdate, 'yyyy-mm-dd
hh24:mi:ss') realtime from dual;FIVESEC REALTIME
------------------- -------------------
2009-07-14 22:26:05 2009-07-14 22:26:09SQL> select to_char(trunc(sysdate, 'mi')+trunc(to_number(to_char(sysdate,'ss'))/
5)*5/(24*60*60), 'yyyy-mm-dd hh24:mi:ss') fivesec, to_char(sysdate, 'yyyy-mm-dd
hh24:mi:ss') realtime from dual;FIVESEC REALTIME
------------------- -------------------
2009-07-14 22:26:10 2009-07-14 22:26:11
5)*5/(24*60*60), 'yyyy-mm-dd hh24:mi:ss') fivesec, to_char(sysdate, 'yyyy-mm-dd
hh24:mi:ss') realtime from dual;FIVESEC REALTIME
------------------- -------------------
2009-07-14 22:26:55 2009-07-14 22:26:57SQL> select to_char(trunc(sysdate, 'mi')+trunc(to_number(to_char(sysdate,'ss'))/
5)*5/(24*60*60), 'yyyy-mm-dd hh24:mi:ss') fivesec, to_char(sysdate, 'yyyy-mm-dd
hh24:mi:ss') realtime from dual;FIVESEC REALTIME
------------------- -------------------
2009-07-14 22:27:00 2009-07-14 22:27:03
可以把你的改为
select mobile, trunc(savetime, 'mi'), count(mid) from test.waphistory
where savetime>=to_date('2008-10-1','yyyy-mm-dd') and savetime <to_date('2008-11-1','yyyy-mm-dd') and actionid in(1,4) and mobile <>0
group by mobile, to_date(trunc(savetime, 'mi')+trunc(to_number(to_char(savetime,'ss'))/5)*5/(24*60*60))
having count(mid) >1
where savetime>=to_date('2008-10-1','yyyy-mm-dd') and savetime <to_date('2008-11-1','yyyy-mm-dd') and actionid in(1,4) and mobile <>0
group by mobile, to_date(trunc(savetime, 'mi')+trunc(to_number(to_char(savetime,'ss'))/5)*5/(24*60*60))
having count(mid) >1 楼上 应该是这样,但我看了结果 与实际不符合 唉~·~ 谢谢!!
不好意思,应该是把to_date拿走也就是
select mobile, trunc(savetime, 'mi'), count(mid) from test.waphistory
where savetime>=to_date('2008-10-1','yyyy-mm-dd') and savetime <to_date('2008-11-1','yyyy-mm-dd') and actionid in(1,4) and mobile <>0
group by mobile, trunc(savetime, 'mi')+trunc(to_number(to_char(savetime,'ss'))/5)*5/(24*60*60)
having count(mid) >1
where savetime>=to_date('2008-10-1','yyyy-mm-dd') and savetime <to_date('2008-11-1','yyyy-mm-dd') and actionid in(1,4) and mobile <>0
group by mobile, trunc(savetime, 'mi')+trunc(to_number(to_char(savetime,'ss'))/5)*5/(24*60*60)
having count(mid) >1 这个语句报了ora-00970 不是group by 表达式 的错误 还是不行 谢谢!!!
把select里的trunc也改过来就可以了。
select mobile, trunc(savetime, 'mi'), count(mid) from test.waphistory
where savetime>=to_date('2008-10-1','yyyy-mm-dd') and savetime <to_date('2008-11-1','yyyy-mm-dd') and actionid in(1,4) and mobile <>0
group by mobile, trunc(savetime, 'mi')
having count(mid) >1 我这个语句是统计一分钟内一个号码的产生多条mid 我把他统计成一条 截取了1分钟的时间片 现在我想把时间片变成5秒钟内的也就我要的结果是统计5秒内钟有mid》1的记录有多少 谢谢 非常谢谢!!
where savetime>=to_date('2008-10-1','yyyy-mm-dd') and savetime <to_date('2008-11-1','yyyy-mm-dd') and actionid in(1,4) and mobile <>0
group by mobile, trunc(savetime, 'mi')+trunc(to_number(to_char(savetime,'ss'))/5)*5/(24*60*60)
having count(1) >1你需要把select里的也改的和后面一样的,试试上面这个。如果这个不是你想要的结果的话,就是我理解错了。
where savetime>=to_date('2008-10-1','yyyy-mm-dd') and savetime <to_date('2008-11-1','yyyy-mm-dd') and actionid in(1,4) and mobile <>0
group by mobile, trunc(savetime, 'mi')+trunc(to_number(to_char(savetime,'ss'))/5)*5/(24*60*60)
having count(1) >1
where savetime>=to_date('2008-10-1','yyyy-mm-dd') and savetime <to_date('2008-11-1','yyyy-mm-dd') and actionid in(1,4) and mobile <>0
group by mobile, trunc(savetime, 'mi')+trunc(to_number(to_char(savetime,'ss'))/5)*5/(24*60*60)
having count(1) >1 ;楼上的有点小错误 我改了过来了 谢谢 我已经结了贴 不能在再给分 所以只好在这里说声谢谢了!