Re: tricky group by questions
- From: "Greg Rahn" <greg@xxxxxxxxxxxxxxxxxx>
- To: ryan_gaffuri@xxxxxxxxxxx, "Oracle-L Freelists" <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 27 Nov 2007 14:46:48 -0800
On 11/27/07, ryan_gaffuri@xxxxxxxxxxx <ryan_gaffuri@xxxxxxxxxxx> wrote:
> I am writing a query that is grouping by 1 hour blocks over a period of time
> as follows
>
> Now I have one hour periods that do not have any rows. A standard group by
> just ignores those periods.
> I want periods with no data to return and have a count(*) = 0
A time dimension table is needed to do this. A logical table works
well here, rather than physical.
Here is one approach which should perform quite well since it should
do the fact group by reduction before it makes the join to the time
dimension as the time dimension is only required for pretty printing,
not predicate filtering. Adjust as necessary.
with f as (
select
to_char(create_date, 'yyyymmddhh24') time_id, count(*) cnt
from
fact_table
where
create_date > to_date('20071127 0000', 'yyyymmdd hh24mi') and
create_date < to_date('20071127 1900', 'yyyymmdd hh24mi')
group by
to_char(create_date, 'yyyymmddhh24')
),
d as (
select
to_char((to_date('20071127 0000', 'yyyymmdd hh24mi') + rownum /
24),'yyyymmddhh24') time_id
from
dual
connect by
level <=
(to_date('20071127 1900', 'yyyymmdd hh24mi') -
to_date('20071127 0000', 'yyyymmdd hh24mi')) *24
)
select
d.time_id, nvl(f.cnt,0) count
from
f, d
where
d.time_id = f.time_id(+)
order by
d.time_id desc
/
TIME_ID COUNT
---------- ----------
2007112719 0
2007112718 0
2007112717 0
2007112716 0
2007112715 0
2007112714 0
2007112713 0
2007112712 0
2007112711 41
2007112710 60
2007112709 60
2007112708 60
2007112707 60
2007112706 60
2007112705 60
2007112704 60
2007112703 60
2007112702 60
2007112701 60
19 rows selected.
--
Regards,
Greg Rahn
http://structureddata.org
--
//www.freelists.org/webpage/oracle-l
Other related posts:
