Re: Filling in "gaps" in data

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. Received on Fri Oct 31 2008 - 10:22:12 CDT