Wednesday, April 30, 2008

Find sequence gaps using Oracle's analytic functions

The following query will give you the gaps exactly :
Insert one more record...


INSERT INTO sensor_data VALUES ('UNIT1',10,1.235,SYSDATE);

COMMIT;

SELECT measurement_id - gap gap_from, measurement_id - 1 gap_to
FROM ( SELECT measurement_id, CASE measurement_id - NVL((LAG(measurement_id) OVER (ORDER BY measurement_id)),0)
WHEN 1
THEN 0
ELSE measurement_id - (LAG(measurement_id)
OVER (ORDER BY measurement_id)) -1
END gap
FROM sensor_data )
WHERE gap > 0;

Output will look like:
-GAP_FROM GAP_TO
3 3
6 6
8 9

Hope you find this useful.

No comments: