Finding Sequence Gaps in a Table
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.
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)
column or expression is the value to be compared with lagging (preceding) values. The preceding
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
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
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
where order_date between ’01-NOV-1999′ and ’30-NOV-1999′)
select prev_sale, next_sale
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.
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