SQL按时间段统计（5分钟统计一次访问量为例,oracle统计）

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

表结构中有创建日期，类型TIMESTAMP



方案一：临时表/临时存储

统计每5分钟的访问量，存入临时表或者临时存储（比如excel）

循环当天整个时间段

然后对临时表/临时存储的数据做排序



方案二：伪列的SQL查询（数据量小于<1亿数据量，且时间列做索引）

分析：5分钟一次，那就对每条记录做个伪列，每5分钟的数据都是一个标识。

比如：创建时间=201604061200 -至- 201604061204，这五分钟统一标识为201604061200

　　　创建时间=201604061205 -至- 201604061209，这五分钟统一标识为201604061205

实现起来非常方便：

a. 取出创建时间的分钟参数：to_char(t.create_time,'mi')，都是两位：比如01分，07分，13分，19分....

    只判断第二位，如果第二位<5,统一让分钟的第二位=0,否则（第二位>=5）统一让分钟的第二位=5

    而分钟的第一位不变

    比如01分、04分统一标识为00分，05、06、09分统一标识为05分

    切割字符串使用substr函数

    判断使用case when

    拼接使用||

b.取出创建时间的年月日小时参数，拼装上第一步的特殊分钟参数

  一个伪列就这么诞生了

关键SQL如下：


case when substr(to_char(t.create_time,'mi'),2,1)<5 then to_char(t.create_time,'yyyymmddhh24')||substr(to_char(t.create_time,'mi'),1,1)||0
         else to_char(t.create_time,'yyyymmddhh24')||substr(to_char(t.create_time,'mi'),1,1)||5 end as newTime
上一个完整的sql


select count(tmp.id) totalNum,tmp.newTime
from(
  select t.id,t.latest_status, -- ID,状态
         to_char(t.create_time,'yyyymmddhh24mi') oldTime, -- 原来的时间
         case when substr(to_char(t.create_time,'mi'),2,1)<5 then to_char(t.create_time,'yyyymmddhh24')||substr(to_char(t.create_time,'mi'),1,1)||0
         else to_char(t.create_time,'yyyymmddhh24')||substr(to_char(t.create_time,'mi'),1,1)||5 end as newTime  -- 时间段伪列    
  from acquire_order t
  where t.create_time>=to_date('20160406000000','yyyymmddhh24miss')
  order by t.create_time asc
) tmp
group by tmp.newTime
order by totalNum desc