Re: Filling in "gaps" in data

On Oct 31, 11:22 am, Charles Hooper <hooperc2..._at_yahoo.com> wrote:
> 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.
>
> Charles Hooper
> IT Manager/Oracle DBA
> K&M Machine-Fabricating, Inc.- Hide quoted text -
>
> - Show quoted text -

Charles have you ever found an article or any form of documentation that explains how connect by level can be used on dual with 10g+ to create a series. This does not work on 9.2 and below and I have never found any useful information on this technique.

• Mark D Powell --