在前面的一个建议中，我说明了如何应用Oracle强大的解析函数发现序列间隙。TechRepublic成员Sachin Jayashekhar在论坛的一个贴子中问到与那篇文章有关的一个问题。该成员希望了解：在已探测到间隙的情况下，如何找到一个序列的起始值与终值。我对Sachin的样本数据进行了一些修改，在列表A的脚本中增加了几行代码。
CREATE TABLE sn
(carton_numberVARCHAR2(10),
serial_numberVARCHAR2(10)
);
INSERT INTO sn VALUES('Carton A','SN1030');
INSERT INTO sn VALUES('Carton A','SN1031');
INSERT INTO sn VALUES('Carton A','SN1032');
INSERT INTO sn VALUES('Carton A','SN1033');
INSERT INTO sn VALUES('Carton B','SN1005');
INSERT INTO sn VALUES('Carton B','SN1054');
INSERT INTO sn VALUES('Carton B','SN1085');
INSERT INTO sn VALUES('Carton C','SN1029');
INSERT INTO sn VALUES('Carton B','SN1006');
INSERT INTO sn VALUES('Carton B','SN1086');
COMMIT;
|
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在贴子中描述的非常相似。
|
