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

 来源：Builder.com.cn 

在前面的一个建议中，我说明了如何应用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在贴子中描述的非常相似。