如何用Oracle的解析函数查找序列记录

作者：Builder.com.cn 来源:

在前面的一个建议中，我说明了如何应用Oracle强大的解析函数发现序列间隙。TechRepublic成员Sachin Jayashekhar在论坛的一个贴子中问到与那篇文章有关的一个问题。该成员希望了解：在已探测到间隙的情况下，如何找到一个序列的起始值与终值。我对Sachin的样本数据进行了一些修改，在列表A的脚本中增加了几行代码。

Sachin的样本脚本使用了解析函数FIRST_VALUE和LAST_VALUE，试图找到序列的终点。虽然它可以找到Sachin样本数据中Carton A和Carton C的终点，但Carton B的结果却是错误的，因为它在硬纸箱中有多个记录。既然Sachin用carton_number进行分割，那么FIRST_VALUE和LAST_VALUE总是返回整个硬纸箱的起始值与终值，而不是序列号的每个记录。

如果使用另外一栏，如run_id栏来分割数据，就可以得到希望的结果。那样，解析子句：

OVER (PARTITION BY carton_number ORDER BY serial_number)

可以简单改写成：

OVER (PARTITION BY carton_number, run_id ORDER BY serial_number)

但是，表中没有这样一栏。相反，我们必须像原文一样，应用LEAD和LAG函数来探测序列记录之间的间隙。列表B是其中一种应用。

一个指定行共有四种可能的情况：

• 该行是一个多行序列的第一行。
• 该行是一个多行序列的最后一行。
• 该行是一个多行序列的中间行，既不是第一行，也不是最后一行。
• 该行是一个单行序列。

代码必须考虑到所有这些情况。如果它是第一行或单行，我们只需在报告的FROM栏中打印出序列号。如果它是最后一行或单行，我们在TO栏中打印序列号。如果它是中间行，则可以将其忽略。

由于解析函数不能嵌套（即用作其它解析函数的自变量），因此我们必须使用两层查询来完成这个任务。第一层查询为aquery，应用LAG函数从前一行的序列号中提取当前序列号，从而确定一个新序列的起点。如果间隙为1，说明这是现在序列的延续部分。如果间隙大于1，则说明它是一个新序列。（在解析子句中应用ORDER BY可保证序号以升序排列。）

而后，第二层查询bquery检查这个名为diff的计算结果栏。用LEAD函数将当前行的间隙与紧接一行的间隙进行比较。如果这个next_diff值为1，则说明序列记录中至少有二行。如果next_diff值大于1，则说明已到序列的结尾部分。注意，如果没有下一行，就把默认值999分配给它（即我们到达了硬纸箱的终点），于是这一行标记为间隙。

最后，我们从bquery中选择主查询，并对上述四种情况进行测试。用WHERE子句排除不必要的中间行，仅选择代表序列起点和终点的行。分别对FROM和TO栏使用CASE语句，比较diff和next_diff的值，并返回一个系列号或NULL。

这样得到的结果与Sachin在贴子中描述的非常相似。