Discovery of sequence gaps with Oracle analytic functions

A sequence of values is often used in database tables. Whether it is naturally generated as the data itself or by an Oracle sequence object, its purpose is to number each row with a unique, incremental number.

In the process of numbering, the causes of gaps are varied. If a stored procedure picks a number from a sequence, it is defined as a local variable, but it is never used, and the number is lost. It will no longer return to the original sequence, resulting in a gap in the numerical sequence. There is no need to worry about this in relational database models. But sometimes people care about this, and these people want to know which numbers are missing.

An obvious way is to use Pl/sql: Loop through the sorted pointer and store with a local variable, comparing each number to the previous line. The problem with this approach is that the efficiency is low and the speed is slow. Another less common solution is to do a self merging of tables, which in essence means that each row matches the previous line with a certain standard. This method is difficult to write code.

Oracle's analytic functions provide a much quicker way to check gaps. They enable you to still see the next line (lead) or the previous line (LAG) values while using full, set-oriented SQL processing.

The following are the formats for these functions:

{lead | LAG} (value_expression, offset, default)

Over ([PARTITION by expr] order by expr)

Typically, a value_expression is a data column that you want to retrieve. Parameter offset is the number of rows you want to read forward or backward, and default is the value returned when you reach the beginning or end of any partition (that is, there are no rows to match).

The script in List A is a series of data that is collected automatically from the working place sensor. The sensor device automatically gives each measurement a number, and we need to find out if the result is missing.

In list B, we sort the data by number of measurements and use the LAG function to set the offset to 1 so that each row corresponds to its previous line. The first row has no corresponding amount, so the default value of 0 is returned accordingly. Because the gap cannot occur on the first line, we remove it by requiring a measure value greater than 0 before passing.

The rest is a set of virtual rows that show the measure number ("Before_gap") of the previous line and the measure number ("After_gap") of the current row. If the difference is greater than 1, the gap between the two lines is indicated.

Also note that the WITH clause names the parse subquery "Aquery" at the beginning of the query. Then we can access the sequence in "Aquery" in the WHERE clause in the main SELECT statement.