做一段时间内信息的统计。比如说,一个流程实例的存储表,我要统计出某段时间所创建的流程实例的个数。要求是每七天做一个统计数据。
例如:原表
processInstanceId createDate
1 2012/01/20
2 2012/01/29
3 2012/01/25
4 2012/02/10
数据表内createDate时间范围是1月20号到2月10号,那我就要统计出这段时间内每7天的count值,得到
date count
2012/01/20--2012/01/26 2
2012/01/27--2012/02/02 1
2012/02/09--2012/02/15 1
其中2012/02/03--2012/02/08这段时间没有记录就不进行统计。这个应该怎么实现呢?
例如:原表
processInstanceId createDate
1 2012/01/20
2 2012/01/29
3 2012/01/25
4 2012/02/10
数据表内createDate时间范围是1月20号到2月10号,那我就要统计出这段时间内每7天的count值,得到
date count
2012/01/20--2012/01/26 2
2012/01/27--2012/02/02 1
2012/02/09--2012/02/15 1
其中2012/02/03--2012/02/08这段时间没有记录就不进行统计。这个应该怎么实现呢?
with t as(
select 1 processInstanceId,to_date('2012/01/20','yyyy/mm/dd') createDate from dual
union all
select 2,to_date('2012/01/29','yyyy/mm/dd') from dual
union all
select 3,to_date('2012/01/25','yyyy/mm/dd') from dual
union all
select 4,to_date('2012/02/10','yyyy/mm/dd') from dual
)
select to_char(to_date('2012/01/20', 'yyyy/mm/dd') +
7 *
trunc((createDate - to_date('2012/01/20', 'yyyy/mm/dd')) / 7),
'yyyy/mm/dd')||'--'||to_char(to_date('2012/01/20', 'yyyy/mm/dd') +6+
7 *
trunc((createDate - to_date('2012/01/20', 'yyyy/mm/dd')) / 7),
'yyyy/mm/dd') "date",
count(1) "count"
from t
group by trunc((createDate - to_date('2012/01/20', 'yyyy/mm/dd')) / 7) order by trunc((createDate - to_date('2012/01/20', 'yyyy/mm/dd')) / 7)date count
---------------------- ----------
2012/01/20--2012/01/26 2
2012/01/27--2012/02/02 1
2012/02/10--2012/02/16 1
select 1 tid,to_date('2012/01/20','yyyy/mm/dd') createDate from dual
union all
select 2,to_date('2012/01/29','yyyy/mm/dd') from dual
union all
select 3,to_date('2012/01/25','yyyy/mm/dd') from dual
union all
select 4,to_date('2012/02/10','yyyy/mm/dd') from dual
)
select to_char(b_date,'yyyy/mm/dd')||'-'||to_char(e_date,'yyyy/mm/dd') "date",count(t.tid) "count"
from t left join
(select n_date+(level-1)*7 b_date,n_date+level*7-1 e_date
from (select min(createDate) n_date,max(createDate) m_date from t)
connect by level <= (m_date-n_date)/7+1) on createDate between b_date and e_date
group by to_char(b_date,'yyyy/mm/dd')||'-'||to_char(e_date,'yyyy/mm/dd')
order by to_char(b_date,'yyyy/mm/dd')||'-'||to_char(e_date,'yyyy/mm/dd')
date count
-------------------------------------------
1 2012/01/20-2012/01/26 2
2 2012/01/27-2012/02/02 1
3 2012/02/10-2012/02/16 1
HJ_daxian的方式加入到我的表中稍加修改就行了,感谢!