Oracle实例之 ---- 统计某段时间数据量

转载

mob60475704ea1a 2021-09-02 11:16:00

需求：统计当天的访问量，每五分钟采集一次

表结构中有日期字段，类型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