Re: Filling in "gaps" in data

On 31 ÏËÔ, 10:51, Jeremy <jeremy0..._at_gmail.com> wrote:
> In article <37d61e27-c8a2-4724-8b0b-55f925d5b167
> @t39g2000prh.googlegroups.com>, hooperc2..._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

You can make counter without CONNECT BY clause using any big table for example DBA_OBJECTS

select ROUND(sysdate, 'MI')+t.i/1440
from (select rownum i from dba_objects where rownum <1440) t, dual;

COUNTER

02.11.2008 19:07:00
02.11.2008 19:08:00
02.11.2008 19:09:00
02.11.2008 19:10:00
02.11.2008 19:11:00
02.11.2008 19:12:00
02.11.2008 19:13:00
02.11.2008 19:14:00
02.11.2008 19:15:00

then You can join this table with you query.

Alex Received on Sun Nov 02 2008 - 18:07:26 CST