需求:统计当天的访问量,每五分钟采集一次
表结构中有日期字段,类型TIMESTAMP
如果,统计是采用每秒/分钟/小时/天/周/月/年,都非常容易实现,只要to_char日期字段然后group by分组即可
但是:如果是X秒/分钟/小时/天/周/月/年 and X>1,就需要变通实现,方法如下:
SELECT TO_CHAR(B.T_CRT_TM, 'YYYY'), COUNT(*) FROM ZSSYS.WEB_PLY_BASE B GROUP BY TO_CHAR(B.T_CRT_TM, 'YYYY') ---------------------------- SELECT TO_CHAR(B.T_CRT_TM, 'yyyy-MM'), COUNT(*) FROM ZSSYS.WEB_PLY_BASE B GROUP BY TO_CHAR(B.T_CRT_TM, 'yyyy-MM') ---------------------------- SELECT TO_CHAR(B.T_CRT_TM, 'yyyy-MM-dd hh24') AS DATE_PLY, COUNT(*) AS COUNT_PLY FROM ZSSYS.WEB_PLY_BASE B WHERE B.T_CRT_TM >TO_DATE('2016-01-01','yyyy-MM-dd') GROUP BY TO_CHAR(B.T_CRT_TM, 'yyyy-MM-dd hh24') ORDER BY COUNT_PLY DESC ------------------------ select count(tmp.c_ply_no) totalNum,tmp.newTime from( select b.c_ply_no, to_char(b.t_crt_tm,'YYYY-MM-DD HH24:MI:SS') oldTime, -- 原来的时间 case when substr(to_char(b.t_crt_tm,'mi'),2,1)<5 then to_char(b.t_crt_tm,'yyyymmddhh24')||substr(to_char(b.t_crt_tm,'mi'),1,1)||0 else to_char(b.t_crt_tm,'yyyymmddhh24')||substr(to_char(b.t_crt_tm,'mi'),1,1)||5 end as newTime -- 时间段伪列 from web_ply_base b where b.t_crt_tm >= TO_DATE('2018-01-01','yyyy-MM-dd') order by b.t_crt_tm asc ) tmp group by tmp.newTime order by totalNum desc