Press "Enter" to skip to content

Finding Sequence Gaps in a Table

Finding Sequence Gaps in a Table
Problem
You want to find all gaps in the sequence of numbers or in dates and times in your data. The gaps could
be in dates recorded for a given action, or in some other data with a logically consecutive nature.
Solution
Oracle’s

LAG and LEAD OLAP functions let you compare the current row of results with a preceding row.

The general format of
LAG looks like this
Lag (column or expression, preceding row offset, default for first row)
The

column or expression is the value to be compared with lagging (preceding) values. The preceding

row offset
indicates how many rows prior to the current row the LAG should act against. We’ve used ‘1’ in
the following listing to mean the row one prior to the current row. The default for
LAG indicates what
value to use as a precedent for the first row, as there is no row zero in a table or result. We instruct Oracle
to use 0 as the default anchor value, to handle the case where we look for the day prior to the first of the
month.
The
WITH query alias approach can be used in almost all situations where a subquery is used, to
relocate the subquery details ahead of the main query. This aids readability and refactoring of the code if
required at a later date.
This recipe looks for gaps in the sequence of days on which orders were made for the month of
November 1999:

with salesdays as
(select extract(day from order_date) next_sale,
lag(extract(day from order_date),1,0)
over (order by extract(day from order_date)) prev_sale
from oe.orders
where order_date between ’01-NOV-1999′ and ’30-NOV-1999′)
select prev_sale, next_sale
from salesdays
where next_sale – prev_sale > 1
order by prev_sale;

Our query exposes the gaps, in days, between sales for the month of November 1999.
PREV_SALE NEXT_SALE
———- ———-
1 10
10 14
15 19
20 22
The results indicate that after an order was recorded on the first of the month, no subsequent order
was recorded until the 10

th. Then a four-day gap followed to the 14th, and so on. An astute sales manager

might well use this data to ask what the sales team was doing on those gap days, and why no orders
came in!