Re: Filling in "gaps" in data

In article <37d61e27-c8a2-4724-8b0b-55f925d5b167 @t39g2000prh.googlegroups.com>, hooperc2000_at_yahoo.com says...>
> On Oct 31, 10:45 am, Jeremy <jeremy0..._at_gmail.com> wrote:
> > Oracle 10g R2 10.2.0.1.0
> >
> > Hi
> >
> > 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       192.168.0.1
> > 31-oct-2008 13:08       192.168.0.1
> > 31-oct-2008 13:11       192.168.0.1
> > 31-oct-2008 13:12       192.168.0.1
> > 31-oct-2008 13:15       192.168.0.1
> > 31-oct-2008 13:15    192.168.0.1
> >
> > 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?
> > jeremy
>
> You need a way to generate rows with the missing time elements, for
> instance by creating an inline view with a simple counter:
> SELECT
> LEVEL COUNTER
> FROM
> DUAL
> CONNECT BY
> LEVEL<=1000;
> COUNTER
> ==========
> 1
> 2
> 3
> 4
> 5
> 6
> 7
> 8
> 9
> 10
> ...
>
> With a slight modification to the above:
> SELECT
> DT.DATE_TIME
> FROM
> (SELECT
> TO_DATE('31-OCT-2008','DD-MON-YYYY') + (LEVEL/1440) DATE_TIME
> FROM
> DUAL
> CONNECT BY
> LEVEL<=1000) DT;
>
> ====================
> 31-OCT-2008 00:01:00
> 31-OCT-2008 00:02:00
> 31-OCT-2008 00:03:00
> 31-OCT-2008 00:04:00
> 31-OCT-2008 00:05:00
> 31-OCT-2008 00:06:00
> 31-OCT-2008 00:07:00
> 31-OCT-2008 00:08:00
> 31-OCT-2008 00:09:00
> 31-OCT-2008 00:10:00
>
> Once the rows with the missing time elements are available, you could
> outer join your table to this inline view, something like this:
> SELECT
> DT.DATE_TIME,
> NVL(COUNT(*)) HIT_COUNT
> FROM
> (SELECT
> TO_DATE('31-OCT-2008','DD-MON-YYYY') + (LEVEL/1440) DATE_TIME
> FROM
> DUAL
> CONNECT BY
> LEVEL<=1000) DT,
> HITS
> WHERE
> DT.DATE_TIME=HITS.DATE_TIME(+)
> GROUP BY
> DT.DATE_TIME;
>
> There are of course more than 1000 minutes in a day (right around
> 1440), so some adjustment will be necessary.
>

Thanks Charles, exactly the sort of thing I was looking for.

-- 
jeremy