用Oracle的解析函数发现序列间隙

 来源：赛迪网技术社区    作者：ta

数据库表格中经常用到值序列。无论是作为数据本身自然产生的，还是由Oracle 的序列对象分配的，它的目的就是用一个唯一的、递增的数字给每一行编号。

在编号的过程中，产生间隙的原因多种多样。如果一个存储过程从一个序列中挑选某个数字，定为本地变量，但是从来不用它，这个数字就丢失了。它将不能再返回到原序列中，结果就造成数值序列中存在一个间隙。关系型数据库模型中不必担心这一点。但是有时候人们在意这一点，这些人想知道是哪些数字丢失了。

一个显而易见的方式就是用PL/SQL:在已排序指针中循环，并且用一个本地变量进行存储，把每一个数值与其前一行的进行比较。这种方法的问题在于效率很低，速度很慢。另外一个不常用的解决办法就是对表格做一个自合并，实质也就是用某种标准使每一行与其前一行相匹配。这种方法很难编写代码。

Oracle的解析函数为检查间隙提供了一种要快捷得多的方法。它们使你在使用完整的、面向集合的SQL处理的同时，仍然能够看到下一个行(LEAD)或者前一行(LAG)的数值。

下面是这些函数的格式：

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

OVER ([PARTITION BY expr] ORDER BY expr)

通常，value_expression是一个你想要检索的数据列。参数offset是指你想要往前或往后读取的行数，default则是到达任意一个分区的开头或者结尾（即没有与之相匹配的行）时返回的数值。

列表A中的脚本是一系列从工作地传感器自动收集来的数据。传感器装置自动给每一个测量值编上号，我们要找出结果有没有缺失。

在列表B中，我们将数据按照测量值编号排序，运用LAG函数，把偏移量设置为1，使得每一行与其前一行对应起来。第一行将没有对应量，所以相应地，将返回默认值0。因为间隙不可能发生在第一行，所以我们通过之前要求测量值大于0而将其删除。

剩下的就是一组虚拟的行，显示的是前一行的测量值编号（“before_gap”）和当前行的测量值编号（“after_gap”）。如果两者之差大于1，就说明这两行之间存在间隙。

同时也要注意到，WITH子句在查询开始的时候把解析子查询命名为“aquery”。那么我们就可以在主SELECT 语句中的WHERE 子句中访问“aquery”中的数列。