Home » SQL & PL/SQL » SQL & PL/SQL » finding non-continuity in a consecutive date field.
| finding non-continuity in a consecutive date field. [message #164409] |
Thu, 23 March 2006 06:02  |
jazzist
Messages: 11
Registered: March 2006
|
Junior Member |
|
|
Example table data as per:
http://www.orafaq.com/forum/t/59775/83243/
I was wondering if anyone could help me go about writing a query that finds when there is missing data for the example table given in the above thread?
Each row has a date field in it. I would like to display the row before the time gap when the time gap between the rows exceeds a set period, for example >10 seconds. Or, it should tell me when the gap(s) was, and how long it was for.
|
|
|
|
| Re: finding non-continuity in a consecutive date field. [message #164491 is a reply to message #164409] |
Thu, 23 March 2006 12:05  |
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
|
Senior Member |
|
|
I'be been involved in a question of this type on this forum in the past. Not sure off the top of my head what to suggest you search for, but there is at least one built in oracle function to help in exactly this situation...I'll post back if I can remember what it was, and please you do as well if you find it in your searching of this board. I think the function I'm thinking of is fairly new and just makes the process easier.
A similar approach, which has been talked about many times on this board and elsewhere, is creating an artificial data set that contains one of every possible value you are comparing against. Then you do a join to your actual data, and that will show you which ones are missing. Or a minus instead of join.
Examples of both approaches are on this forum though.
|
|
|
|
| Re: finding non-continuity in a consecutive date field. [message #164498 is a reply to message #164409] |
Thu, 23 March 2006 12:35 |
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
|
Senior Member |
|
|
Found a little more. Search both here and in the data warehouse guide for data densification and partition outer join. The first is the process of filling in sparse data, and the second is oracle's built in ability to do it. Sparse might be another useful search term.
|
|
|
|
| Re: finding non-continuity in a consecutive date field. [message #164506 is a reply to message #164498] |
Thu, 23 March 2006 13:29 |
William Robertson
Messages: 1643
Registered: August 2003
Location: London, UK
|
Senior Member |
|
|
I don't see why you would need to generate rows here. Unless I'm missing something, don't we just need to compare each row with the next one using LEAD()?
For example:
SELECT object_name, object_type, created
, next_created
, next_object
, next_type
, CAST(CAST(next_created AS TIMESTAMP(0)) -
CAST(created AS TIMESTAMP(0))
AS INTERVAL DAY(1) TO SECOND(0)) AS gap
FROM ( SELECT object_name, object_type, created
, LEAD(created) OVER(ORDER BY created) AS next_created
, LEAD(object_name) OVER(ORDER BY created) AS next_object
, LEAD(object_type) OVER(ORDER BY created) AS next_type
FROM user_objects
ORDER BY created )
WHERE next_created - created > 1;
The CAST() business is just to get the time interval and not strictly relevant to the question. (Actually the query isn't very meaningful either but using USER_OBJECTS saved me having to set up test data.)
[Updated on: Thu, 23 March 2006 13:31] Report message to a moderator |
|
|
|
| Re: finding non-continuity in a consecutive date field. [message #164650 is a reply to message #164409] |
Fri, 24 March 2006 09:22 |
jazzist
Messages: 11
Registered: March 2006
|
Junior Member |
|
|
Hi, thanks for your suggestions.
I'm just looking at this again and think that lead will probably do the job, there is a similar example in the SQL Cookbook, determining the time between employee hire date and the hire date of the next employee. It's similar to W. Robertson's solution:
select ename, hiredate, next_hd,
next_hd - hiredate diff
from (
select deptno, ename, hiredate,
lead(hiredate)over(order by hiredate) next_hd
from emp
)
where deptno=10
[Updated on: Fri, 24 March 2006 09:28] Report message to a moderator |
|
|
|
| Re: finding non-continuity in a consecutive date field. [message #171768 is a reply to message #164506] |
Thu, 11 May 2006 07:10 |
jazzist
Messages: 11
Registered: March 2006
|
Junior Member |
|
|
| William Robertson wrote on Thu, 23 March 2006 13:29 |
I don't see why you would need to generate rows here. Unless I'm missing something, don't we just need to compare each row with the next one using LEAD()?
For example:
SELECT object_name, object_type, created
, next_created
, next_object
, next_type
, CAST(CAST(next_created AS TIMESTAMP(0)) -
CAST(created AS TIMESTAMP(0))
AS INTERVAL DAY(1) TO SECOND(0)) AS gap
FROM ( SELECT object_name, object_type, created
, LEAD(created) OVER(ORDER BY created) AS next_created
, LEAD(object_name) OVER(ORDER BY created) AS next_object
, LEAD(object_type) OVER(ORDER BY created) AS next_type
FROM user_objects
ORDER BY created )
WHERE next_created - created > 1;
The CAST() business is just to get the time interval and not strictly relevant to the question. (Actually the query isn't very meaningful either but using USER_OBJECTS saved me having to set up test data.)
|
Slight modifications to this and it has worked really welll, an example of the output of the query is:
7 08/02/2006 21:26:15 08/02/2006 21:36:25 13 +00 00:10:10.000000
The second column is the start of the gap, the third column is the end of the gap, and the fifth column is the difference between the two.
I can't think of an easy way to modify this in SQL, and am considering post-processing. I would like to change the query so that I get an output that also contains the middle point of the gap (e.g. in the example 21:16:15+(00:10:10/2)=21:31:20) and also has the gap length in seconds, i.e. instead of "+00 00:10:10.000000" the fifth column is "610" seconds.
This would help me graph my data with gnuplot, I'm plotting a bar graph, and will set the centre of the bar to the middle point, and the width of the bar to the number of seconds it takes up along the x-axis (which is in units seconds).
Thanks.
|
|
|
|
| Re: finding non-continuity in a consecutive date field. [message #171802 is a reply to message #171768] |
Thu, 11 May 2006 09:26 |
William Robertson
Messages: 1643
Registered: August 2003
Location: London, UK
|
Senior Member |
|
|
How about:
SELECT object_name, object_type
, created
, created + gap/2 as midpoint
, next_created
, next_object
, next_type
, gap
, gap_seconds
FROM ( SELECT object_name, object_type, created
, next_created
, next_object
, next_type
, (next_created - created) * 86400 AS gap_seconds
, CAST(CAST(next_created AS TIMESTAMP(0)) -
CAST(created AS TIMESTAMP(0))
AS INTERVAL DAY(1) TO SECOND(0)) AS gap
FROM ( SELECT object_name, object_type, created
, LEAD(created) OVER(ORDER BY created) AS next_created
, LEAD(object_name) OVER(ORDER BY created) AS next_object
, LEAD(object_type) OVER(ORDER BY created) AS next_type
FROM user_objects
ORDER BY created )
WHERE next_created - created > 1 ); |
|
|
|
|
|
| Re: finding non-continuity in a consecutive date field. [message #182901 is a reply to message #182898] |
Tue, 18 July 2006 11:36 |
jazzist
Messages: 11
Registered: March 2006
|
Junior Member |
|
|
thanks, I have been using a modified version of this for a while now.
however, depending on what time period I specify at the bottom I get error ORA-01873 : the leading precision of the interval is too small. I understand that to fix this I'll have to increase the precision of an interval. But, I don't know to what part the error refers. Any ideas?
edit: this seems to have been caused by an error in the data in the table, which i've now been fixed. 
[Updated on: Tue, 18 July 2006 11:45] Report message to a moderator |
|
|
|
Goto Forum:
Current Time: Sat Jan 17 04:53:03 CST 2026
|
] 
Blog
Search
Help
Members
Register
Login
Home
