17,377
社区成员
发帖
与我相关
我的任务
分享
select a_time, nvl(counts, 0) "count_time"
from (select trunc(datatime, 'HH24') c_time, count(1) counts
from (select datatime
from test_time t1
where t1.rowid = (select max(rowid)
from test_time t2
where t1.datatime = t2.datatime)
)
group by trunc(datatime, 'HH24')) count_time,
(select (min_time + (rownum - 1)/24) a_time,
rownum rn
from all_objects,
(select min(trunc(datatime, 'HH24')) min_time,
max(trunc(datatime, 'HH24')) max_time
from test_time) loop_time
where rownum < = (max_time - min_time)*24 + 1) all_time
where a_time = c_time(+);
select a_time, nvl(counts, 0) "count_time"
from (select trunc(datatime, 'HH24') c_time, count(1) counts
from (select datatime
from test_time t1
where t1.rowid = (select max(rowid)
from test_time t2
where t1.datatime = t2.datatime)
and to_char(datatime,'yyyy-mm-dd') = &day_time_char
)
group by trunc(datatime, 'HH24')) count_time,
(select (trunc(min_time) + (rownum - 1)/24) a_time,
rownum rn
from all_objects,
(select min(trunc(datatime, 'HH24')) min_time
from test_time
where to_char(datatime,'yyyy-mm-dd') = &day_time_char) loop_time
where rownum < = 24) all_time
where a_time = c_time(+);
select weeks,sum("count") "count"
from
(
select aa.days,aa.weeks,nvl(bb.num,0) "count"
from (
select start_date+rownum-1 Days,trunc(rownum/7) weeks
from (
select trunc(min(a.datetime)) start_date,trunc(max(a.datetime)) end_date
from TABLE a
)
connect by rownum <=end_date-start_Date+1
)aa,(
select trunc(DATATIME ) days,count(*) num
from table
group by trunc(DATATIME )
)bb
where aa.days=bb.days(+))
group by weeks
select a_time, nvl(counts, 0)
from (select trunc(datatime,'mm') c_time, count(1) counts
from (select datatime
from test_time t1
where t1.rowid =
(select max(rowid)
from test_time t2
where t1.datatime = t2.datatime)
)
group by trunc(datatime,'mm')) count_time,
(select add_months(min_time, rownum - 1) a_time
from all_objects,
(select min(trunc(datatime,'mm')) min_time,
max(trunc(datatime,'mm')) max_time
from test_time) loop_time
where rownum < = MONTHS_BETWEEN(max_time,min_time) + 1) all_time
where a_time = c_time(+);
select trunc(datatime) c_time, count(1) counts
from test_time
group by trunc(datatime)
select trunc(datatime) c_time, count(1) counts
from (select datatime
from test_time t1
where t1.rowid =
(select max(rowid)
from test_time t2
where t1.datatime = t2.datatime))
group by trunc(datatime)
select a_time, nvl(counts, 0)
from (select trunc(datatime) c_time, count(1) counts
from (select datatime
from test_time t1
where t1.rowid =
(select max(rowid)
from test_time t2
where t1.datatime = t2.datatime)
and datatime >= &start_time
and datatime <= &end_time)
group by trunc(datatime)) count_time,
(select min_time + rownum - 1 a_time
from all_objects,
(select min(trunc(datatime)) min_time,
max(trunc(datatime)) max_time
from test_time) loop_time
where rownum < = max_time - min_time + 1) all_time
where a_time = c_time(+);
For hour range : group by trunc(datatime,'HH24')
For week range : group by trunc(datatime,'ww')
For month range: group by trunc(datatime,'mm')
SQL> select * from test_time;
TID DATATIME
---------- -----------
1 2008-1-1 1:
2 2008-1-1 7:
3 2008-1-1 9:
4 2008-1-2 11
5 2008-1-2 12
6 2008-1-2 17
7 2008-1-2 21
8 2008-1-4 1:
9 2008-1-4 5:
10 2008-1-4 14
11 2008-1-4 16
12 2008-1-4 22
12 rows selected
SQL>
SQL> select a_time, nvl(counts, 0)
2 from (select trunc(datatime) c_time, count(1) counts
3 from test_time
4 group by trunc(datatime)) count_time,
5 (select min_time + rownum - 1 a_time
6 from all_objects,
7 (select min(trunc(datatime)) min_time,
8 max(trunc(datatime)) max_time
9 from test_time) loop_time
10 where rownum < = max_time - min_time + 1) all_time
11 where a_time = c_time(+);
A_TIME NVL(COUNTS,0)
----------- -------------
2008-1-1 3
2008-1-2 4
2008-1-3 0
2008-1-4 5
SQL>
select aa.days,nvl(bb.num,0) "count"
from (
select start_date+rownum-1 Days
from (
select trunc(min(a.datetime)) start_date,trunc(max(a.datetime)) end_date
from TABLE a
)
connect by rownum <=end_date-start_Date+1
)aa,(
select trunc(DATATIME ) days,count(*) num
from table
group by trunc(DATATIME )
)bb
where aa.days=bb.days(+)