跪求时间拆分存储过程，谢谢了
本帖最后由 Grn0210 于 2013-11-21 15:29:11 编辑 已知
表1
ID StareTime EndTime
001 2013/5/4 7:12:33 2013/5/4 17:07:52
001 2013/5/4 20:12:33 2013/5/4 22:00:00
001 2013/5/5 0:00:01 2013/5/14 23:59:59
001 2013/5/15 8:53:20 2013/5/16 20:35:23
求：按天来统计ID运行小时(把时间段进行拆分)
结果：
ID StareTime EndTime RunTime(小时)
001 2013/5/4 00:00:01 2013/5/4 23:59:59 12
001 2013/5/5 00:00:01 2013/5/5 23:59:59 24
001 2013/5/6 00:00:01 2013/5/6 23:59:59 24
.
.
.
001 2013/5/15 00:00:01 2013/5/15 23:59:59 15
001 2013/5/16 00:00:01 2013/5/16 23:59:59 21
[解决办法]
先弄个临时表把跨天的拆分成几条记录吧，然后
select ID,min(StareTime),max(EndTime),sum(round((EndTime-StareTime)*24),0) from tb group by id就行了
[解决办法]
create table t1 (id varchar2(20),StartTime date,EndTime date);
insert into t1
select '001' ID,To_date('2013/5/4 7:12:33','yyyy-mm-dd hh24:mi:ss') StareTime ,To_date('2013/5/4 17:07:52','yyyy-mm-dd hh24:mi:ss') EndTime from dual
union all select '001',To_date('2013/5/4 20:12:33','yyyy-mm-dd hh24:mi:ss') ,To_date('2013/5/4 22:00:00','yyyy-mm-dd hh24:mi:ss') from dual
union all select '001',To_date('2013/5/5 0:00:01','yyyy-mm-dd hh24:mi:ss') ,To_date('2013/5/14 23:59:59','yyyy-mm-dd hh24:mi:ss') from dual
union all select '001',To_date('2013/5/15 8:53:20','yyyy-mm-dd hh24:mi:ss') ,To_date('2013/5/16 20:35:23','yyyy-mm-dd hh24:mi:ss') from dual;
WITH t AS
(
SELECT To_date('2013/5/4 00:00:01','yyyy-mm-dd hh24:mi:ss') StartTime,To_date('2013/5/4 23:59:59','yyyy-mm-dd hh24:mi:ss')EndTime FROM dual
),dimDate AS
(
SELECT LEVEL AS rn, StartTime+LEVEL-1 StartTime ,EndTime+LEVEL-1 EndTime
FROM t
CONNECT BY LEVEL<=100
)
SELECT t.id,d.StartTime,d.EndTime
,SUM(
ROUND(TO_Number(
CASE
WHEN d.StartTime<=t.StartTime AND d.EndTime>=t.StartTime AND d.EndTime<=t.EndTime THEN d.EndTime-t.StartTime
WHEN d.StartTime<=t.StartTime AND d.EndTime>=t.EndTime THEN t.EndTime-t.StartTime
WHEN d.StartTime>t.StartTime AND d.StartTime<=t.EndTime AND t.EndTime<d.EndTime THEN t.EndTime-d.StartTime
WHEN d.StartTime>=t.StartTime AND d.StartTime<=t.EndTime AND t.EndTime>=d.EndTime THEN d.EndTime-d.StartTime
END
) * 24))
FROM dimDate d
JOIN t1 t
ON (d.StartTime<=t.StartTime AND d.EndTime>=t.StartTime AND d.EndTime<=t.EndTime)
OR (d.StartTime>=t.StartTime AND d.StartTime<=t.EndTime)
OR (d.StartTime<=t.StartTime AND d.EndTime>=t.EndTime)
GROUP BY t.id,d.StartTime,d.EndTime
ORDER BY d.StartTime
[解决办法]
declare
cursor cr1 is
select distinct to_char(a.st + b.dis - 1, 'yyyy/mm/dd') h, a.id
from test a,
(select rownum dis
from (select max(et - st) + 1 gap from test)
connect by rownum <= gap) b
where a.et >= a.st + b.dis - 1
order by h;
c_tem varchar2(10);
i number;
l_date date;
l_count number;
begin
for c1 in cr1 loop
i := 0;
select count(*)
into l_count
from test t
where to_char(t.et, 'yyyy/mm/dd') = c1.h;
if l_count > 0 then
for c2 in (select * from test where to_char(et, 'yyyy/mm/dd') = c1.h) loop
select count(*)
into l_count
from test t
where t.st <= c2.et
and to_char(t.st, 'yyyy/mm/dd') = to_char(c2.et, 'yyyy/mm/dd');
if l_count > 0 then
begin
select max(st)
into l_date
from test t
where t.st <= c2.et
and to_char(t.st, 'yyyy/mm/dd') =
to_char(c2.et, 'yyyy/mm/dd');
end;
else
l_date := to_date(c1.h
[解决办法]
' 00:00:01', 'yyyy/mm/dd hh24:mi:ss');
end if;
i := i + round((c2.et - l_date) * 24);
end loop;
else
begin
select t.st
into l_date
from test t
where to_char(t.st, 'yyyy/mm/dd') = c1.h;
exception
when others then
l_date := to_date(c1.h
[解决办法]
' 00:00:00', 'yyyy/mm/dd hh24:mi:ss');
end;
i := round((to_date(c1.h
[解决办法]
' 23:59:59', 'yyyy/mm/dd hh24:mi:ss') -
l_date) * 24);
end if;
dbms_output.put_line(c1.id
[解决办法]
' '
[解决办法]
to_date(c1.h
[解决办法]
' 00:00:01',
'yyyy/mm/dd hh24:mi:ss')
[解决办法]
' '
[解决办法]
to_date(c1.h
[解决办法]
' 23:59:59',
'yyyy/mm/dd hh24:mi:ss')
[解决办法]
' '
------解决方案--------------------
i);
end loop;
end;
[解决办法]
此sql 定义游标的时候
cursor cr1 is
select distinct to_char(a.st + b.dis - 1, 'yyyy/mm/dd') h, a.id
from test a,
(select rownum dis
from (select max(et - st) + 1 gap from test)
connect by rownum <= gap) b
where a.et >= a.st + b.dis - 1
order by h;
不能查询出 像这样的数据（2013/5/7 20:12:33 至 2013/5/8 20:12:08）
可修改为
cursor cr1 is
select distinct to_char(a.st + b.dis - 1, 'yyyy/mm/dd') h, a.id
from test a,
(select rownum dis
from (select max(et - st) + 1 gap from test)
connect by rownum <= gap) b
where to_char(a.et,'yyyy/mm/dd') >= to_char(a.st + b.dis - 1,'yyyy/mm/dd')
order by h;
还有数据得和id关联。
这种数据
0012013/5/4 7:12:332013/5/4 17:07:52
0012013/5/4 20:12:332013/5/5 22:00:00
统计也有问题、