我给一个比较复杂原始的,没有优化,在我自己的环境中可以运行,自己看看吧:select to_char(time_stamp,'YYYYMMDDHH24')||trunc((to_number(to_char(time_stamp,'MI'))/10)),count(*) from TABLE_A group by to_char(time_stamp,'YYYYMMDDHH24')||trunc((to_number(to_char(time_stamp,'MI'))/10))将最后的分钟转换成数值,/10,再取整。
SELECT TO_CHAR(UPDATE_TIME, 'YYYYMMDD') ADAY, TO_CHAR(UPDATE_TIME, 'HH24') ANHOUR, TRUNC((TO_NUMBER(TO_CHAR(UPDATE_TIME, 'MI')) / 10)) TENMINUTES, COUNT(*) CNT FROM SAMPLE_TABLE GROUP BY TO_CHAR(UPDATE_TIME, 'YYYYMMDD'), TO_CHAR(UPDATE_TIME, 'HH24'), TRUNC((TO_NUMBER(TO_CHAR(UPDATE_TIME, 'MI')) / 10)) ORDER BY 1, 2, 3
from TABLE_A
group by to_char(time_stamp,'YYYYMMDDHH24')||trunc((to_number(to_char(time_stamp,'MI'))/10))将最后的分钟转换成数值,/10,再取整。
SELECT TO_CHAR(UPDATE_TIME, 'YYYYMMDD') ADAY,
TO_CHAR(UPDATE_TIME, 'HH24') ANHOUR,
TRUNC((TO_NUMBER(TO_CHAR(UPDATE_TIME, 'MI')) / 10)) TENMINUTES,
COUNT(*) CNT
FROM SAMPLE_TABLE
GROUP BY TO_CHAR(UPDATE_TIME, 'YYYYMMDD'),
TO_CHAR(UPDATE_TIME, 'HH24'),
TRUNC((TO_NUMBER(TO_CHAR(UPDATE_TIME, 'MI')) / 10))
ORDER BY 1, 2, 3