在itpub里看到一个很有意思的sql

一个表中记录了各个地点的报警开始时间，结束时间。

现在想统计，各个地点报警次数和时长，时长=结束时间-开始时间。

要求，同一个地点，开始时间间隔超过10分钟的，这个地点要在结果中再出现一次，10分钟之内的假如有多条，统计条数和报警时长，时长=10分钟内各条时长之和。

create table T_DEMO
(
id VARCHAR2(32) not null,
place_id VARCHAR2(32),
s_time DATE,
e_time DATE
)；

insert into t_demo (ID, PLACE_ID, S_TIME, E_TIME)

values (‘1’, ‘1’, to_date(’21-08-2014 01:24:00′, ‘dd-mm-yyyy hh24:mi:ss’), to_date(’21-08-2014 01:24:01′, ‘dd-mm-yyyy hh24:mi:ss’));

insert into t_demo (ID, PLACE_ID, S_TIME, E_TIME)

values (‘2’, ‘1’, to_date(’21-08-2014 01:25:00′, ‘dd-mm-yyyy hh24:mi:ss’), to_date(’21-08-2014 01:25:01′, ‘dd-mm-yyyy hh24:mi:ss’));

insert into t_demo (ID, PLACE_ID, S_TIME, E_TIME)

values (‘3’, ‘1’, to_date(’21-08-2014 01:35:00′, ‘dd-mm-yyyy hh24:mi:ss’), to_date(’21-08-2014 01:35:01′, ‘dd-mm-yyyy hh24:mi:ss’));

insert into t_demo (ID, PLACE_ID, S_TIME, E_TIME)

values (‘4’, ‘1’, to_date(’21-08-2014 01:39:00′, ‘dd-mm-yyyy hh24:mi:ss’), to_date(’21-08-2014 01:39:01′, ‘dd-mm-yyyy hh24:mi:ss’));

insert into t_demo (ID, PLACE_ID, S_TIME, E_TIME)

values (‘5’, ‘1’, to_date(’21-08-2014 02:05:00′, ‘dd-mm-yyyy hh24:mi:ss’), to_date(’21-08-2014 02:05:01′, ‘dd-mm-yyyy hh24:mi:ss’));

insert into t_demo (ID, PLACE_ID, S_TIME, E_TIME)

values (‘6’, ‘1’, to_date(’21-08-2014 02:06:00′, ‘dd-mm-yyyy hh24:mi:ss’), to_date(’21-08-2014 02:06:01′, ‘dd-mm-yyyy hh24:mi:ss’));

insert into t_demo (ID, PLACE_ID, S_TIME, E_TIME)

values (‘7’, ‘1’, to_date(’21-08-2014 02:12:00′, ‘dd-mm-yyyy hh24:mi:ss’), to_date(’21-08-2014 02:12:01′, ‘dd-mm-yyyy hh24:mi:ss’));

insert into t_demo (ID, PLACE_ID, S_TIME, E_TIME)

values (‘8’, ‘1’, to_date(’21-08-2014 02:14:00′, ‘dd-mm-yyyy hh24:mi:ss’), to_date(’21-08-2014 02:14:01′, ‘dd-mm-yyyy hh24:mi:ss’));

insert into t_demo (ID, PLACE_ID, S_TIME, E_TIME)

values (‘9’, ‘1’, to_date(’21-08-2014 02:50:00′, ‘dd-mm-yyyy hh24:mi:ss’), to_date(’21-08-2014 02:50:01′, ‘dd-mm-yyyy hh24:mi:ss’));

insert into t_demo (ID, PLACE_ID, S_TIME, E_TIME)

values (’10’, ‘1’, to_date(’21-08-2014 02:52:00′, ‘dd-mm-yyyy hh24:mi:ss’), to_date(’21-08-2014 02:52:01′, ‘dd-mm-yyyy hh24:mi:ss’));

insert into t_demo (ID, PLACE_ID, S_TIME, E_TIME)

values (’11’, ‘1’, to_date(’21-08-2014 02:57:00′, ‘dd-mm-yyyy hh24:mi:ss’), to_date(’21-08-2014 02:57:01′, ‘dd-mm-yyyy hh24:mi:ss’));

insert into t_demo (ID, PLACE_ID, S_TIME, E_TIME)

values (’12’, ‘1’, to_date(’25-08-2014 09:13:00′, ‘dd-mm-yyyy hh24:mi:ss’), to_date(’25-08-2014 09:13:01′, ‘dd-mm-yyyy hh24:mi:ss’));

insert into t_demo (ID, PLACE_ID, S_TIME, E_TIME)

values (’13’, ‘1’, to_date(’25-08-2014 09:18:00′, ‘dd-mm-yyyy hh24:mi:ss’), to_date(’25-08-2014 09:18:01′, ‘dd-mm-yyyy hh24:mi:ss’));

insert into t_demo (ID, PLACE_ID, S_TIME, E_TIME)

values (’14’, ‘1’, to_date(’25-08-2014 09:30:00′, ‘dd-mm-yyyy hh24:mi:ss’), to_date(’25-08-2014 09:30:01′, ‘dd-mm-yyyy hh24:mi:ss’));

insert into t_demo (ID, PLACE_ID, S_TIME, E_TIME)

values (’15’, ‘1’, to_date(’25-08-2014 09:37:00′, ‘dd-mm-yyyy hh24:mi:ss’), to_date(’25-08-2014 09:37:01′, ‘dd-mm-yyyy hh24:mi:ss’));

insert into t_demo (ID, PLACE_ID, S_TIME, E_TIME)

values (’16’, ‘1’, to_date(’25-08-2014 09:47:00′, ‘dd-mm-yyyy hh24:mi:ss’), to_date(’25-08-2014 09:47:01′, ‘dd-mm-yyyy hh24:mi:ss’));

insert into t_demo (ID, PLACE_ID, S_TIME, E_TIME)

values (’17’, ‘2’, to_date(’25-08-2014 10:09:00′, ‘dd-mm-yyyy hh24:mi:ss’), to_date(’25-08-2014 10:09:01′, ‘dd-mm-yyyy hh24:mi:ss’));

WITHt2AS

(SELECT  a.place_id,

a.s_time,

a.e_time,

a.id,

floor((a.s_time – to_date(‘2000-01-01′,’YYYY-MM-DD’)) * 24 * 60 / 10) last_cha

FROM t_demo a)

SELECT

place_id 地点,

SUM(e_time – s_time) * 24 * 60 * 60 “时长:秒”,

min(s_time)  开始时间，

COUNT(*) 次数

FROM t2

GROUP BY place_id, last_cha;