The data structure is similar to sql> select * from T;
B E N
----------------- ----------------- --------------------
20150106 01:00:02 20150106 01:00:42 A
20150106 01:00:02 20150106 01:00:12 B
20150106 02:01:02 20150106 01:10:03 C
20150106 02:01:07 20150106 02:01:08 D
20150106 02:00:01 20150106 02:00:02 E
20150106 01:00:03 20150106 01:00:07 F
6 rows selected. Data table definition sql> @desc t
Name Null? Type
------------------------------- -------- ----------------------------
1 B DATE
2 E DATE
3 N VARCHAR2 (10) Demand abstraction counts the number of N per minute of the day in minutes. Ideas: 1, 1440 minutes per day, construction of the full-day minutes collectionSELECTTo_date (' 20150106 00:00:00 ', ' Yyyy-mm-dd hh24:mi:ss ') + Numtodsinterval ( Level , ' minute ') DT fromDual CONNECT by Level<= - * - 2, left outer connection, conditions DT >= begin and DT < end + 1 3, need to intercept t.begin and t.end to minutes for begin, trunc (Begin, ' mi ') for end, Trunc (end+ numto Dsinterval (1, ' minute '), ' mi ')//trunc (end + 1, [format]) 4, aggregated statistical results by minute withEach_minute as (SELECTTo_date ( ' 20150106 00:00:00 ' , ' Yyyy-mm-dd hh24:mi:ss ') + Numtodsinterval ( Level , ' minute ') DT fromDual CONNECT by Level<= - * - )SELECT trunc(Em.dt,' mi ') d,Count(T.N) CNT fromeach_minute em Left OUTER JOINT on(Em.dt >=trunc(t.b,' mi ') andEm.dt <trunc (t.e+Numtodsinterval (1, ' minute '),' mi ' ))Group bytrunc(Em.dt,' mi ' )Order by1; Simple test 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
Em.dt < Trunc (T.E + numtodsinterval (1, ' minute '), ' mi ')
One where T.N is isn't null--no need for 1440,just for test.
Group BY Trunc (Em.dt, ' mi ')
Order by 1;
D-CNT
----------------- ----------
20150106 01:00:00 3
20150106 02:00:00 1
20150106 02:01:00 1
Oracle query that is count connections by minute with start and end times provided
