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

数据结构类似

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.

 

数据表定义

SQL> @desc t
           Name                            Null?    Type
           ------------------------------- -------- ----------------------------
    1      B                                        DATE
    2      E                                        DATE
    3      N                                        VARCHAR2(10)

 

需求抽象

以分钟为单位统计指定天每分钟内N的个数。

 

思路：

1、每天1440分钟，构造全天的分钟集合

SELECT  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

转载于:https://www.cnblogs.com/syksky/p/oracle.html