Re: tricky group by questions

From: Greg Rahn <greg_at_structureddata.org>
Date: Tue, 27 Nov 2007 14:46:48 -0800
Message-ID: <a9c093440711271446s176c95eel4cafdfb4753d6bed@mail.gmail.com>

On 11/27/07, ryan_gaffuri_at_comcast.net <ryan_gaffuri_at_comcast.net> 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
--
http://www.freelists.org/webpage/oracle-l

Received on Tue Nov 27 2007 - 16:46:48 CST