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:
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:
> 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:
> 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:
> 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?
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.
> On Dec 14, 11:43 am, Vincent <animedaydrea...@gmail.com> wrote: >> 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:
>> 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:
>> 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?
> 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
> -- > 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_dba_experts@googlegroups.com. > To unsubscribe from this group, send email to oracle_dba_experts+unsubscribe@googlegroups.com. > For more options, visit this group at http://groups.google.com/group/oracle_dba_experts?hl=en.
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.
> On Dec 14, 11:43 am, Vincent <animedaydrea...@gmail.com> wrote:
> > 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:
> > 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:
> > 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?
> 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.
> 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.
> On Dec 14, 4:46 pm, ddf <orat...@msn.com> wrote:
> > On Dec 14, 11:43 am, Vincent <animedaydrea...@gmail.com> wrote:
> > > 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:
> > > 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:
> > > 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?
> > 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
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?
(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 )
On Thu, Dec 15, 2011 at 1:13 AM, Vincent <animedaydrea...@gmail.com> wrote: > 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:
> 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:
> 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?
> -- > 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_dba_experts@googlegroups.com. > To unsubscribe from this group, send email to > oracle_dba_experts+unsubscribe@googlegroups.com. > For more options, visit this group at > http://groups.google.com/group/oracle_dba_experts?hl=en.
> On Dec 14, 6:52 pm, Vincent <animedaydrea...@gmail.com> wrote:
> > 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.
> > On Dec 14, 4:46 pm, ddf <orat...@msn.com> wrote:
> > > On Dec 14, 11:43 am, Vincent <animedaydrea...@gmail.com> wrote:
> > > > 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:
> > > > 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:
> > > > 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?
> > > 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
> 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?
