• SQL按时间段统计(5分钟统计一次访问量为例,oracle统计)


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

    表结构中有日期字段,类型TIMESTAMP

    如果,统计是采用每秒/分钟/小时/天/周/月/年,都非常容易实现,只要to_char日期字段然后group by分组即可

    但是:如果是X秒/分钟/小时/天/周/月/年 and X>1,就需要变通实现,方法如下:

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

    统计每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
    

      

  • 相关阅读:
    python-Web-django-路由保护
    python-Web-django-图表统计
    python-linux-集群nginx
    python-Web-数据库-oracle
    python-Web-数据库-mysql
    python-爬虫-scrapy
    Educational Codeforces Round 90 (Rated for Div. 2) A~C
    leetcode周赛192
    Codeforces Round #597 (Div. 2) C dp
    Codeforces Round #645 (Div. 2) A~D
  • 原文地址:https://www.cnblogs.com/huahua035/p/5360956.html
一二三 - 开发者的网上家园