SQL查询实现时间段区间统计

问题描述:

表数据j结果如下:
serviceid       callerno     begintime     endtime     
serviceid业务类型,callerno手机号码,其中begintime和endtime均为date类型分别为通话开始时间和通话结束时间,现在要统计2009-6-1每三十分钟业务分布情况,包括通话次数和通话总时间,比如统计的区间格式为00:00:00-00:30:00,00:30:00-01:00:00........

解决方案:

该问题的难点主要在于三十分钟区间格式的处理,在这里我将通过构造一个整数表来实现:

SQL> create table test1(serviceid number,
  2  callerno number,
  3  begintime date,
  4  endtime date);

表已创建。

SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';

会话已更改。

SQL> select * from test1;

  SEVICEID   CALLERNO BEGINTIME           ENDTIME
---------- ---------- ------------------- -------------------
         1        138 2009-06-01 00:25:00 2009-06-01 00:26:10
         2        136 2009-06-01 00:10:00 2009-06-01 00:12:10
         3        135 2009-06-01 01:10:00 2009-06-01 01:14:10

SQL> select to_date('2009-06-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss') +

  2         (n - 1) / 48 sec_begin,

  3         to_date('2009-06-01 00:00:00', 'yyyy-mm-ddhh24:mi:ss') + n / 48 sec_

end,

  4         count(callerno) callnum,

  5         nvl(sum(endtime - begintime),0) * 24 * 60 * 60 "calllength(s)"

  6    from test1 t,

  7         (select rownum n from dual connect by level < = 48) integers

  8    where t.begintime between

  9         to_date('2009-06-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss') +

 10         (n - 1) / 48 and

 11         to_date('2009-06-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss') + n / 48

 12   group by n;

 

SEC_BEGIN           SEC_END                CALLNUM calllength(s)

------------------- ------------------- ---------- -------------

2009-06-01 00:00:00 2009-06-01 00:30:00          2           200

2009-06-01 01:00:00 2009-06-01 01:30:00          1           250

上面的结果只显示了存在呼叫信息的时间段的统计数据,而对于其他时间段则没有显示出来,可以通过外连接来显示一天中所有时间段的统计信息,具体实现如下:

SQL> select to_date('2009-06-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss') +
  2         (n - 1) / 48 sec_begin,
  3         to_date('2009-06-01 00:00:00', 'yyyy-mm-ddhh24:mi:ss') + n / 48 sec_
end,
  4         count(callerno) callnum,
  5         nvl(sum(endtime - begintime),0) * 24 * 60 * 60 "calllength(s)"
  6    from test1 t right join
  7         (select rownum n from dual connect by level < = 48) integers
  8    on t.begintime between
  9         to_date('2009-06-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss') +
10         (n - 1) / 48 and
11         to_date('2009-06-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss') + n / 48
12   group by n;

SEC_BEGIN           SEC_END                CALLNUM calllength(s)
------------------- ------------------- ---------- -------------
2009-06-01 00:00:00 2009-06-01 00:30:00          2           200
2009-06-01 00:30:00 2009-06-01 01:00:00          0             0
2009-06-01 01:00:00 2009-06-01 01:30:00          1           250
2009-06-01 01:30:00 2009-06-01 02:00:00          0             0
2009-06-01 02:00:00 2009-06-01 02:30:00          0             0
2009-06-01 02:30:00 2009-06-01 03:00:00          0             0
2009-06-01 03:00:00 2009-06-01 03:30:00          0             0
2009-06-01 03:30:00 2009-06-01 04:00:00          0             0
2009-06-01 04:00:00 2009-06-01 04:30:00          0             0
2009-06-01 04:30:00 2009-06-01 05:00:00          0             0
2009-06-01 05:00:00 2009-06-01 05:30:00          0             0

SEC_BEGIN           SEC_END                CALLNUM calllength(s)
------------------- ------------------- ---------- -------------
2009-06-01 05:30:00 2009-06-01 06:00:00          0             0
2009-06-01 06:00:00 2009-06-01 06:30:00          0             0
2009-06-01 06:30:00 2009-06-01 07:00:00          0             0
2009-06-01 07:00:00 2009-06-01 07:30:00          0             0
2009-06-01 07:30:00 2009-06-01 08:00:00          0             0
2009-06-01 08:00:00 2009-06-01 08:30:00          0             0
2009-06-01 08:30:00 2009-06-01 09:00:00          0             0
2009-06-01 09:00:00 2009-06-01 09:30:00          0             0
2009-06-01 09:30:00 2009-06-01 10:00:00          0             0
2009-06-01 10:00:00 2009-06-01 10:30:00          0             0
2009-06-01 10:30:00 2009-06-01 11:00:00          0             0

SEC_BEGIN           SEC_END                CALLNUM calllength(s)
------------------- ------------------- ---------- -------------
2009-06-01 11:00:00 2009-06-01 11:30:00          0             0
2009-06-01 11:30:00 2009-06-01 12:00:00          0             0
2009-06-01 12:00:00 2009-06-01 12:30:00          0             0
2009-06-01 12:30:00 2009-06-01 13:00:00          0             0
2009-06-01 13:00:00 2009-06-01 13:30:00          0             0
2009-06-01 13:30:00 2009-06-01 14:00:00          0             0
2009-06-01 14:00:00 2009-06-01 14:30:00          0             0
2009-06-01 14:30:00 2009-06-01 15:00:00          0             0
2009-06-01 15:00:00 2009-06-01 15:30:00          0             0
2009-06-01 15:30:00 2009-06-01 16:00:00          0             0
2009-06-01 16:00:00 2009-06-01 16:30:00          0             0

SEC_BEGIN           SEC_END                CALLNUM calllength(s)
------------------- ------------------- ---------- -------------
2009-06-01 16:30:00 2009-06-01 17:00:00          0             0
2009-06-01 17:00:00 2009-06-01 17:30:00          0             0
2009-06-01 17:30:00 2009-06-01 18:00:00          0             0
2009-06-01 18:00:00 2009-06-01 18:30:00          0             0
2009-06-01 18:30:00 2009-06-01 19:00:00          0             0
2009-06-01 19:00:00 2009-06-01 19:30:00          0             0
2009-06-01 19:30:00 2009-06-01 20:00:00          0             0
2009-06-01 20:00:00 2009-06-01 20:30:00          0             0
2009-06-01 20:30:00 2009-06-01 21:00:00          0             0
2009-06-01 21:00:00 2009-06-01 21:30:00          0             0
2009-06-01 21:30:00 2009-06-01 22:00:00          0             0

SEC_BEGIN           SEC_END                CALLNUM calllength(s)
------------------- ------------------- ---------- -------------
2009-06-01 22:00:00 2009-06-01 22:30:00          0             0
2009-06-01 22:30:00 2009-06-01 23:00:00          0             0
2009-06-01 23:00:00 2009-06-01 23:30:00          0             0
2009-06-01 23:30:00 2009-06-02 00:00:00          0             0

已选择48行。

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/15203236/viewspace-605346/,如需转载,请注明出处,否则将追究法律责任。

转载于:http://blog.itpub.net/15203236/viewspace-605346/

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值