Filling in "gaps" in data

From: Jeremy <>
Date: Fri, 31 Oct 2008 14:45:59 -0000
Message-ID: <MPG.23752a7f359204d6989698@News.Individual.NET>

Oracle 10g R2


I posted (couple of weeks ago) on this (as a sub-thread) but wanted to ask it here with some clarity.

Say we have a table with date/time related data

create table hits

(date_time		date,
 ip_address		varchar2(240));

And our data look like this:

date_time		ip_address
---------     	----------
31-oct-2008 13:08
31-oct-2008 13:08
31-oct-2008 13:11
31-oct-2008 13:12
31-oct-2008 13:15

31-oct-2008 13:15

I want to produce a report that shows the number of hits per minute but include the "gaps" where there is no data recorded.

So I would like to see:

date_time		hits
---------     	----
31-oct-2008 13:08	2
31-oct-2008 13:09	0
31-oct-2008 13:10	0
31-oct-2008 13:11	1
31-oct-2008 13:12	1
31-oct-2008 13:13	0
31-oct-2008 13:14	0
31-oct-2008 13:15	2

Is it possible without outer-joining to another table with "time" data in it?

I was previously pointed to 01/server.102/b14223/analys... which relates to data densification - but this method involves getting the "blanks" from another table.

Received on Fri Oct 31 2008 - 09:45:59 CDT

Original text of this message