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

核心提示： Sachin的样本脚本使用了解析函数FIRST_VALUE和LAST_VALUE，试图找到序列的终点，如何用Oracle的解析函数查找序列记录(2)，虽然它可以找到Sachin样本数据中Carton A和Carton C的终点，但Carton B的结果却是错误的，比较diff和next_d

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是其中一种应用。

WITH aquery AS
　　　　(SELECT carton_number, serial_number,
　　　　　 SUBSTR(serial_number,3) -
　　　　　　　LAG(SUBSTR(serial_number,3),1,0)
　　　　　　　　 OVER (PARTITION BY carton_number
　　　　　　　　　　　 ORDER BY serial_number) diff
　　　　 FROM sn),
　　bquery AS
　　　 (SELECT carton_number, serial_number, diff,
　　　 LEAD(diff,1,999) OVER (PARTITION BY carton_number
　　　　　　　　　　　 ORDER BY serial_number) next_diff
　　　 FROM aquery)
SELECT carton_number,
　　　 CASE
　　　　　WHEN diff>1 THEN serial_number
　　　　　ELSE NULL
　　　 END from_serial,
　　　 CASE
　　　　　WHEN (diff>1 AND next_diff>1) THEN serial_number
　　　　　WHEN next_diff > 1 THEN serial_number
　　　　　ELSE NULL
　　　 END to_serial
FROM bquery
WHERE diff > 1 OR next_diff > 1
ORDER BY carton_number, serial_number
/

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

该行是一个多行序列的第一行。

该行是一个多行序列的最后一行。

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

该行是一个单行序列。

代码必须考虑到所有这些情况。如果它是第一行或单行，我们只需在报告的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在贴子中描述的非常相似。