Oracle query that count connections by minute with start and end times provided

 to_date(‘20150106 00:00:00‘ , ‘yyyy-mm-dd hh24:mi:ss‘) +          numtodsinterval (level , ‘minute‘) dt     FROM dual   CONNECT BY level <= 24 * 60   2、左外连接，条件 dt >= begin and dt < end + 1   3、需要截取t.begin 和 t.end到分钟 对于begin , trunc(begin,‘mi‘) 对于end   , trunc(end+ numtodsinterval(1, ‘minute‘),‘mi‘))  // trunc( end + 1 ,[format])   4、按分钟聚合统计     结果 WITH each_minute AS  ( SELECT to_date( ‘20150106 00:00:00‘ , ‘yyyy-mm-dd hh24:mi:ss‘ ) +          numtodsinterval (level , ‘minute‘) dt     FROM dual   CONNECT BY level <= 24 * 60 ) SELECT trunc (em.dt, ‘mi‘) d, count (t.n) cnt   FROM each_minute em   LEFT OUTER JOIN t     ON (em.dt >= trunc (t.b, ‘mi‘) AND em.dt < trunc (t.e+ numtodsinterval (1 , ‘minute‘), ‘mi‘ )) group by trunc(em.dt, ‘mi‘ ) order by 1;   简单测试 SQL> WITH each_minute AS
  2   (SELECT to_date(‘20150106 00:00:00‘, ‘yyyy-mm-dd hh24:mi:ss‘) +
  3           numtodsinterval(level, ‘minute‘) dt
  4      FROM dual
  5    CONNECT BY level <= 24 * 60)
  6  SELECT trunc(em.dt, ‘mi‘) d, count(t.n) cnt
  7    FROM each_minute em
  8    LEFT OUTER JOIN t
  9      ON (em.dt >= trunc(t.b, ‘mi‘) AND
10         em.dt < trunc(t.e + numtodsinterval(1, ‘minute‘), ‘mi‘))
11   where t.n is not null   -- no need for 1440,just for test.
12   group by trunc(em.dt, ‘mi‘)
13   order by 1;

D                        CNT
----------------- ----------
20150106 01:00:00          3
20150106 02:00:00          1
20150106 02:01:00          1

Oracle query that count connections by minute with start and end times provided

标签：