Table表结构如下:
==============================================
姓名 时间 数量
AAA1 2009-1-12 00:16:05 100
AAA2 2009-1-12 02:56:05 101
AAA3 2009-1-12 05:36:05 102
AAA4 2009-1-12 08:16:05 103
AAA5 2009-1-12 10:56:05 104
AAA6 2009-1-12 13:36:05 105
AAA7 2009-1-12 16:16:05 106
AAA8 2009-1-12 18:56:05 107
AAA9 2009-1-12 21:36:05 108
AAA10 2009-1-13 00:16:05 109
AAA11 2009-1-13 02:56:05 110
AAA12 2009-1-13 05:36:05 111
AAA13 2009-1-13 08:16:05 112
AAA14 2009-1-13 10:56:05 113
......
=============================================我想是想的效果如下:
=============================================
日期 小时 时间段 合计
2009-1-12 0 0 0
2009-1-12 0 15 100
2009-1-12 0 30 0
2009-1-12 0 45 0
2009-1-12 1 0 0
2009-1-12 1 15 0
2009-1-12 1 30 0
2009-1-12 1 45 0
2009-1-12 2 0 0
2009-1-12 2 15 0
2009-1-12 2 30 0
2009-1-12 2 45 101
2009-1-12 3 0 0
2009-1-12 3 15 0
2009-1-12 3 30 0
2009-1-12 3 45 0
2009-1-12 4 0 0
2009-1-12 4 15 0
2009-1-12 4 30 0
2009-1-12 4 45 0
......
===========================================说明:我就是想以每天每小时每十五分钟为一个时间段统计,该时间段内没有的数据以0填充。
动态需求:假如要求以5分钟或者20分钟作为时间段来统计呢?请高手不吝赐教!
==============================================
姓名 时间 数量
AAA1 2009-1-12 00:16:05 100
AAA2 2009-1-12 02:56:05 101
AAA3 2009-1-12 05:36:05 102
AAA4 2009-1-12 08:16:05 103
AAA5 2009-1-12 10:56:05 104
AAA6 2009-1-12 13:36:05 105
AAA7 2009-1-12 16:16:05 106
AAA8 2009-1-12 18:56:05 107
AAA9 2009-1-12 21:36:05 108
AAA10 2009-1-13 00:16:05 109
AAA11 2009-1-13 02:56:05 110
AAA12 2009-1-13 05:36:05 111
AAA13 2009-1-13 08:16:05 112
AAA14 2009-1-13 10:56:05 113
......
=============================================我想是想的效果如下:
=============================================
日期 小时 时间段 合计
2009-1-12 0 0 0
2009-1-12 0 15 100
2009-1-12 0 30 0
2009-1-12 0 45 0
2009-1-12 1 0 0
2009-1-12 1 15 0
2009-1-12 1 30 0
2009-1-12 1 45 0
2009-1-12 2 0 0
2009-1-12 2 15 0
2009-1-12 2 30 0
2009-1-12 2 45 101
2009-1-12 3 0 0
2009-1-12 3 15 0
2009-1-12 3 30 0
2009-1-12 3 45 0
2009-1-12 4 0 0
2009-1-12 4 15 0
2009-1-12 4 30 0
2009-1-12 4 45 0
......
===========================================说明:我就是想以每天每小时每十五分钟为一个时间段统计,该时间段内没有的数据以0填充。
动态需求:假如要求以5分钟或者20分钟作为时间段来统计呢?请高手不吝赐教!
这个可以用
select rownum
from dual
connect by 1000(这里的数字随你的需求来定)
这个做一个连续数的临时表。
然后生成你的连续结果集。
from (
select to_date('2009-1-12','YYYY-MM-DD')+(rownum-1)/24/4 begin_date
,to_date('2009-1-12','YYYY-MM-DD')+rownum/24/4 end_date
,trunc(mod(rownum-1,4*24)/24) hours
,mod(mod(rownum-1,4*24),4) sk
from dual
connect by rownum<=4*24*30 --30天
) a join (select distinct name from table_name) B on 1=1
left join table_name C on B.name=C.name and C.c_date between A.begin_date and C.end_date
;
from (
select to_date('2009-1-12','YYYY-MM-DD')+(rownum-1)/24/4 begin_date
,to_date('2009-1-12','YYYY-MM-DD')+rownum/24/4 end_date
,trunc(mod(rownum-1,4*24)/24) hours
,mod(mod(rownum-1,4*24),4) sk
from dual
connect by rownum<=4*24*30 --30天
) a left join table_name C on C.c_date between A.begin_date and C.end_date
;
你测试看看有没有什么问题,有问题再提出来。
select A.begin_date,A.hours,A.sk,sum(C.quality)
from (
select to_date('2009-1-12','YYYY-MM-DD')+(rownum-1)/24/4 begin_date
,to_date('2009-1-12','YYYY-MM-DD')+rownum/24/4 end_date
,trunc(mod(rownum-1,4*24)/24) hours
,mod(mod(rownum-1,4*24),4) sk
from dual
connect by rownum<=4*24*30 --30天
) a left join table_name C on C.c_date between A.begin_date and C.end_date
group by A.begin_date,A.hours,A.sk
;
这个还没有结
干
select rownum
from table_name A,table_name B
where rownum<=24*4*30
;
可以替代connect by,可以改写为select A.begin_date,A.hours,A.sk,sum(C.quality)
from (
select to_date('2009-1-12','YYYY-MM-DD')+(rownum-1)/24/4 begin_date
,to_date('2009-1-12','YYYY-MM-DD')+rownum/24/4 end_date
,trunc(mod(rownum-1,4*24)/24) hours
,mod(mod(rownum-1,4*24),4) sk
from table_name A,table_name B
where rownum <=4*24*30 --30天
) a left join table_name C on C.c_date between A.begin_date and C.end_date
group by A.begin_date,A.hours,A.sk
;