红联Linux门户
Linux帮助

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

发布时间:2006-08-09 16:12:43来源:红联作者:厉烨
  数据库表格中经常用到值序列。无论是作为数据本身自然产生的,还是由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”中的数列。
文章评论

共有 0 条评论