Analysis of time difference between logs

[Vincent]

I know there must be a way to do this, but the answer thus far remains
elusive. I am trying to write an odd sort of query to gather some
statistics on time differences between the records. For example,
assume I have the following records in a table:

Received_Date
11/30/2011 00:00
11/30/2011 01:00
11/30/2011 02:00
11/30/2011 03:00
11/30/2011 07:00
11/30/2011 08:00
11/30/2011 09:00
11/30/2011 11:00
11/30/2011 18:00
11/30/2011 19:00
11/30/2011 20:00
11/30/2011 23:00

What I desire to calculate are the average, minimum, maximum, mode,
and standard deviation of the time differences between each
consecutive record. So, the raw differences would be:

1 - 0 = 1
2 - 1 = 1
3 - 2 = 1
7 - 3 = 4
8 - 7 = 1
9 - 8 = 1
11 - 9 = 2
18 - 11 = 7
19 - 18 = 1
20 - 19 = 1
23 - 20 = 3

The desired resultant values would be:
Avg: 2.09
Min: 1
Max: 7
Mode: 1
Std. Dev: 1.9

I'm thinking there must be a way to do this with Oracle's analytic
functions, but I'm not having much luck wrapping my head around them.
Ideas?

[ddf]

Lag() or lead() would help here; using lag():

select lag(received_date) over (order by received_date) -
received_date diff
from <table name here>;

will return the difference list you desire. Wrapping that in another
query could give you the sum, avg, min, max and standard deviation of
the result set. Remember to discard the NULL difference for the very
first record.

David Fitzjarrell

[Carlos Romeu]

Starting in 9i, oracle introduced 2 new function that would make this task possible ... I recommend you look for the lead & lag functions

Sent from my iPhone

> --
> You received this message because you are subscribed to the Google Groups "ORACLE_DBA_EXPERTS" group.
> To post to this group, send email to oracle_db...@googlegroups.com.
> To unsubscribe from this group, send email to oracle_dba_expe...@googlegroups.com.
> For more options, visit this group at http://groups.google.com/group/oracle_dba_experts?hl=en.
>

[Vincent]

Great! But, how would I discard the NULL difference in the first
record? I keep getting an ORA-01722 error when I run the query and
I'm fairly certain this is due to a NULL in the first lag evaluation.

Thanks.

[ddf]

The error is not due to a NULL it's due to how you have received_date
defined. Normally date subtraction is valid; are these values
timestamps rather than dates?

David Fitzjarrell

[Ashwani Kamboj]

Hi Vincent,

this is working fine in this scenario.at my end

SQL> desc dttest
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 RCD                                                DATE

rcd

11/30/2011 00:00
11/30/2011 01:00
11/30/2011 02:00
11/30/2011 03:00
11/30/2011 07:00
11/30/2011 08:00
11/30/2011 09:00
11/30/2011 11:00
11/30/2011 18:00
11/30/2011 19:00
11/30/2011 20:00

SQL ---

(select rcd,to_char(rcd,'hh24') hr,lag(to_char(rcd,'hh24')) over (order by rcd) dd ,(to_char(rcd,'hh24'))-(lag(to_char(rcd,'hh24')) over (order by rcd)) Diff from dttest )

Output -

RCD             HR         DD               DIFF
--------------- ---------- ---------- ----------
30-NOV-11       00
30-NOV-11       01         00                  1
30-NOV-11       02         01                  1
30-NOV-11       03         02                  1
30-NOV-11       07         03                  4
30-NOV-11       08         07                  1
30-NOV-11       09         08                  1
30-NOV-11       11         09                  2
30-NOV-11       18         11                  7
30-NOV-11       19         18                  1
30-NOV-11       20         19                  1
30-NOV-11       23         20                  3

use can use nvl to fill first null value, and can further grouping on DIFF for your desire result

Brgds

Ashwani Kamboj

--

[Vincent]

That was the problem. I have a workaround in place now and it is
working as intended.

Thanks!