论文部分内容阅读
[摘 要] 在辽河油田勘探与生产技术数据管理系统（简称A1）数据主库建设中，对来自于多数据源的数据，通常存在大量的数据冗余、重复记录或一点多值情况。如何高效识别和区分有效或无效记录，成为影响海量勘探开发结构化数据由专业库到主库入库效率的瓶颈问题，同时也是影响主库数据完整性和唯一性的核心问题。本文对ORACLE 系统中RANK函数进行应用尝试，总结分析了RANK函数的使用要点，在实际应用中取得了良好效果。
[关键词] 数据整理；ORACLE；RANK分析函数；主键
doi ： 10 . 3969 / j . issn . 1673 - 0194 . 2013 . 15. 036
[中图分类号] TP274；TP392 [文献标识码] A [文章编号] 1673 - 0194（2013）15- 0056- 04
1 引 言
勘探与生产技术数据管理系统（简称A1）及其数据主库建设是中国石油“十一五”信息化建设的重点项目，为确保A1系统主库结构化数据的完整性、准确性和一致性，中国石油发布了统一的EPDM数据模型，为数据建设与管理人员提供了规范的数据标准。在A1系统建设与运维过程中，为减少数据重复录入工作量，数据建设与管理人员对油田公司勘探開发专业数据库已采集入库的海量结构化数据，采用数据迁移技术，由各专业数据库迁移到A1系统数据主库，以实现对物探、钻井、录井、测井、分析化验、地质等专业数据的集中管理和集成应用，更有效地支持勘探开发项目研究工作的开展，保护勘探开发数据资产，提升数据的综合应用价值。
辽河油田勘探、开发领域的不同专业数据，主要来自于辽河油田研究院已建勘探、开发、钻井、录井、分析化验等专业数据库以及各采油厂建立的专业库。分析发现，这些专业库通常具有多数据源、异构、数据关联性弱、数据完整性不足、不同专业间数据一致性差等特征。如何有效地规划、梳理和清洗专业库中的数据，使之符合A1系统主库数据入库标准，是油田数据建设与管理人员面临的难题之一。
在专业数据实际整理过程中，辽河油田数据建设与管理人员借助ORACLE系统提供的RANK分析函数功能，较好地实现了多源结构化数据记录的识别与区分，解决了影响海量勘探开发结构化数据由专业库到A1系统主库入库效率的瓶颈问题，取得了良好的使用效果。下面我们将结合实际应用案例，总结并分析ORACLE分析函数的功能和使用要点，供大家借鉴参考。
2 RANK函数的功能
一般认为ORACLE提供的RANK函数就是查询命令中聚合函数下GROUP BY功能的延伸，GROUP BY的功能是查询一个集合的统计信息，比如查询一个部门的总销售额、平均销售额等数据，使用格式为：
select deptno，sum（sal），Avg（sal） from emp Group by deptno；（查询语句一）
查询结果如表1所示。即对部门号为10，20，30的单位，查询其部门的总销售额和平均销售额。注意：一个部门只有一条记录。
但要求查询各部门内部，各员工的销售额排名先后次序时，GROUP BY就无能为力了，这就需要用到ORACLE的分析函数RANK。
RANK的本意是等级，也就是说这是一个给查询数据确定等级的函数[1]。
ORACLE从8.1.6版本开始提供分析函数，分析函数用于计算基于组的某种聚合值，它和聚合函数的不同之处是，对于每个组可返回多行，而聚合函数对于每个组只返回一行[2]（见上面的GROUP BY）。RANK是ORACLE中比较典型的一个分析函数，用它就能对各部门内部各员工的销售额进行排序，其使用方法是：
select deptno，ename，sal，rank（） over （partition by deptno order by sal） rank from emp order by deptno；（查询语句二）
查询结果如表2所示。
从查询结果可以看出，RANK函数给出了各个部门内部，各员工的销售额升序排列的详细列表。从列表中可以清楚地看出，在10号部门内，员工MILLER销售额最低，KING销售额最高等信息。
2.1 RANK函数使用的关键点分析
分析函数RANK的语法见图1。
用好RANK函数的关键，是理解partition by和order by的运用，笔者认为partition by之后的字段来划分堆，形成“分堆区”，而order by之后字段来排序，形成“排序区”，因此，RANK的使用可以分解为：先根据部门号来“分堆”，再根据销售额来“排序”，分两步走，这样就好理解了，这也是ORACLE分析函数的使用关键，即先选择“分堆区”的字段，再选择“排序区”的字段。奇怪的是ORACLE数据库系统对于这样的查询，执行效率还是比较高的，这也便于该类函数的推广应用。
注意：一般在“分堆区”的字段要比“排序区”的字段在范围上大一些，这样便于对表中的数据进行分堆，而且“分堆区”与“排序区”的字段不能重复，否则查询的结果都是1，就没有意义了，比如上面的例子中，员工的销售额字段不如部门字段的范围大，所以在“分堆区”内使用了部门号字段，在“排序区”内使用了员工销售额字段，得到了相应的查询结果。
在实际使用RANK函数时，用户往往对“分堆区”和“排序区”的字段不能正确运用，而导致不能得到满意的结果。一般认为，把握好“分堆区”字段范围大于“排序区”字段范围，而且两个分堆区内字段不能重复，掌握好这两个技巧，就能运用好RANK函数甚至其他各个分析函数。
2.2 RANK及DENSE_RANK函数的区别
还有一个与RANK函数类似的函数DENSE_RANK，两者的区别在于对ORDER BY之后排序字段的处理不同。RANK函数在ORDER BY之后的排序字段出现重复值时，将重复值也计入累积顺序号，这样在排下面一个号时就会发生“跳号”现象，例如可能产生1、2、2、4、5、6的结果，中间的3号被“跳过”了，原因是有两条重复的排序为2号的记录，占用两个顺序号，所以下一条记录的顺序号分配为4；而DENSE_RANK则不会产生“跳号”现象，它对重复的记录，只记入一次顺序号，例如可能产生1、2、2、3、4、5的结果，中间两条重复记录，只分配了一个顺序号，而3号顺序号没有被“跳过”。大家可以使用DENSE_RANK替换上面查询命令中的RANK，执行一下，观察其结果。 3 自定义“主键”原理分析
上面介绍了RANK函数的基本使用方法，这个查询命令有什么深层次的利用价值呢？辽河油田前一段時间进行数据迁移工作，即把数据从一个数据库模型迁移到另一个数据库模型，这两个数据库模型结构相差比较大，需要对源数据库中的表进行诸如拆分、合并、转换等工作，然后存储于目标数据库，这要求对源数据库中各个表之间的结构进行仔细分析，理清源数据表间的主外键关系，才能保证迁移数据的正确可靠。
在整理源表结构的过程中，发现了RANK函数一个很有价值的用途，确切地说是DENSE_RANK函数的用途：使用DENSE_RANK函数，可以根据源表中任意字段的组合，生成对源表中各条记录的唯一性“排序字段”，此“排序字段”和源表中相关字段组合，能保证源数据表中记录的唯一性，即生成关于源表的“主键”。大家知道，在ORACLE中，作为数据表中主键的字段必须是非空的，而使用DENSE_RANK函数，可以突破这一障碍，做到“自己的主键自己作主”，这对于一些无法定义主键的表，以及一些比如初期数据建设，没有考虑表的主键问题，数据质量没有保证的表进行数据整理，是很有帮助的。
自定义主键的原理是在分析函数的语法中，先使用partition by对数据表中的数据进行分堆，再用order by对分堆内的数据进行排序，而order排序时，数据是否为空不影响序号的生成，并且DENSE_RANK函数对order by表达式中的重复的记录不重复记入顺序号，保证了即使出现重复记录，也只能有一个顺序号，确保了组合数据项的唯一性。这种很方便定义“主键”的功能，会给大家的数据整理带来意外的惊喜。
4 应用案例及效果
采油X矿的一批分析化验数据见表3。
使用如下查询命令：
select t.*，dense_rank（） over（partition By 井号 Order By 取样地点，取样日期，分析日期） 样品编号from test01 t （查询语句三）
则查询结果如表4所示。
从表4可以看到，使用查询结果中的“井号 样品编号”这个组合字段，可以区分和反映不同化验样品的信息存储结果，其中取样日期和分析日期中的空值没有影响样品编号值的生成。
值得注意的是“陶4 井”，从前面的4个字段来看，同一个取样地点、同一天取样、同一天分析，但后面的分析指标不一样，这样的数据，反映了对同一个样品进行了两次分析。这在实际生产中也是有意义的。在数据整理过程中，如果随意删除任意一条数据，就会造成数据资产的丢失。
对于上面提到的这种情况，可以在查询命令中，再增加一个DENSE_RANK函数，将“指标1”和“指标2”这两个样品分析指标字段放入ORDER BY中，这样，不同的指标值也能生成不同的样品分析结果编号值。使用如下查询命令：
select t.*，dense_rank（） over（partition By 井号 Order By 取样地点，取样日期，分析日期） 样品编号，dense_rank（） over（partition By 井号 Order By 取样地点，取样日期，分析日期，指标1，指标2） 分析结果编号from test01 t （查询语句四）
则查询结果如表5所示。
从表5中可以看出，通过查询语句四中的第二个DENSE_RANK函数，可以在源表中生成一列样品的分析结果编号值，这样通过采用“井号 样品编号 分析结果编号”的组合字段，可以较好地区分源表中同一个样品的不同记录，确保了源数据表中记录的唯一性。所以，可以考虑将该组合字段作为源表的分析“主键”。
以此类推，在数据整理工作中，通过多个DENSE_RANK函数的综合使用，数据整理人员能按照数据整理的目标和意图，对数据进行分层次、分阶段的细化分析，并在不断细化的数据分析过程中在源表里产生多个具有一定涵义的排序字段，并将排序字段与源表中的部分原有字段组合在一起形成可以唯一标识源数据表中的记录且意义丰富的“主键”，用来与其他表进行关联运算，从而既能把握好源数据的层次关系，又能保证数据迁移的准确性和完整性。
[关键词] 数据整理；ORACLE；RANK分析函数；主键
doi ： 10 . 3969 / j . issn . 1673 - 0194 . 2013 . 15. 036
[中图分类号] TP274；TP392 [文献标识码] A [文章编号] 1673 - 0194（2013）15- 0056- 04
1 引 言
勘探与生产技术数据管理系统（简称A1）及其数据主库建设是中国石油“十一五”信息化建设的重点项目，为确保A1系统主库结构化数据的完整性、准确性和一致性，中国石油发布了统一的EPDM数据模型，为数据建设与管理人员提供了规范的数据标准。在A1系统建设与运维过程中，为减少数据重复录入工作量，数据建设与管理人员对油田公司勘探開发专业数据库已采集入库的海量结构化数据，采用数据迁移技术，由各专业数据库迁移到A1系统数据主库，以实现对物探、钻井、录井、测井、分析化验、地质等专业数据的集中管理和集成应用，更有效地支持勘探开发项目研究工作的开展，保护勘探开发数据资产，提升数据的综合应用价值。
辽河油田勘探、开发领域的不同专业数据，主要来自于辽河油田研究院已建勘探、开发、钻井、录井、分析化验等专业数据库以及各采油厂建立的专业库。分析发现，这些专业库通常具有多数据源、异构、数据关联性弱、数据完整性不足、不同专业间数据一致性差等特征。如何有效地规划、梳理和清洗专业库中的数据，使之符合A1系统主库数据入库标准，是油田数据建设与管理人员面临的难题之一。
在专业数据实际整理过程中，辽河油田数据建设与管理人员借助ORACLE系统提供的RANK分析函数功能，较好地实现了多源结构化数据记录的识别与区分，解决了影响海量勘探开发结构化数据由专业库到A1系统主库入库效率的瓶颈问题，取得了良好的使用效果。下面我们将结合实际应用案例，总结并分析ORACLE分析函数的功能和使用要点，供大家借鉴参考。
2 RANK函数的功能
一般认为ORACLE提供的RANK函数就是查询命令中聚合函数下GROUP BY功能的延伸，GROUP BY的功能是查询一个集合的统计信息，比如查询一个部门的总销售额、平均销售额等数据，使用格式为：
select deptno，sum（sal），Avg（sal） from emp Group by deptno；（查询语句一）
查询结果如表1所示。即对部门号为10，20，30的单位，查询其部门的总销售额和平均销售额。注意：一个部门只有一条记录。
但要求查询各部门内部，各员工的销售额排名先后次序时，GROUP BY就无能为力了，这就需要用到ORACLE的分析函数RANK。
RANK的本意是等级，也就是说这是一个给查询数据确定等级的函数[1]。
ORACLE从8.1.6版本开始提供分析函数，分析函数用于计算基于组的某种聚合值，它和聚合函数的不同之处是，对于每个组可返回多行，而聚合函数对于每个组只返回一行[2]（见上面的GROUP BY）。RANK是ORACLE中比较典型的一个分析函数，用它就能对各部门内部各员工的销售额进行排序，其使用方法是：
select deptno，ename，sal，rank（） over （partition by deptno order by sal） rank from emp order by deptno；（查询语句二）
查询结果如表2所示。
从查询结果可以看出，RANK函数给出了各个部门内部，各员工的销售额升序排列的详细列表。从列表中可以清楚地看出，在10号部门内，员工MILLER销售额最低，KING销售额最高等信息。
2.1 RANK函数使用的关键点分析
分析函数RANK的语法见图1。
用好RANK函数的关键，是理解partition by和order by的运用，笔者认为partition by之后的字段来划分堆，形成“分堆区”，而order by之后字段来排序，形成“排序区”，因此，RANK的使用可以分解为：先根据部门号来“分堆”，再根据销售额来“排序”，分两步走，这样就好理解了，这也是ORACLE分析函数的使用关键，即先选择“分堆区”的字段，再选择“排序区”的字段。奇怪的是ORACLE数据库系统对于这样的查询，执行效率还是比较高的，这也便于该类函数的推广应用。
注意：一般在“分堆区”的字段要比“排序区”的字段在范围上大一些，这样便于对表中的数据进行分堆，而且“分堆区”与“排序区”的字段不能重复，否则查询的结果都是1，就没有意义了，比如上面的例子中，员工的销售额字段不如部门字段的范围大，所以在“分堆区”内使用了部门号字段，在“排序区”内使用了员工销售额字段，得到了相应的查询结果。
在实际使用RANK函数时，用户往往对“分堆区”和“排序区”的字段不能正确运用，而导致不能得到满意的结果。一般认为，把握好“分堆区”字段范围大于“排序区”字段范围，而且两个分堆区内字段不能重复，掌握好这两个技巧，就能运用好RANK函数甚至其他各个分析函数。
2.2 RANK及DENSE_RANK函数的区别
还有一个与RANK函数类似的函数DENSE_RANK，两者的区别在于对ORDER BY之后排序字段的处理不同。RANK函数在ORDER BY之后的排序字段出现重复值时，将重复值也计入累积顺序号，这样在排下面一个号时就会发生“跳号”现象，例如可能产生1、2、2、4、5、6的结果，中间的3号被“跳过”了，原因是有两条重复的排序为2号的记录，占用两个顺序号，所以下一条记录的顺序号分配为4；而DENSE_RANK则不会产生“跳号”现象，它对重复的记录，只记入一次顺序号，例如可能产生1、2、2、3、4、5的结果，中间两条重复记录，只分配了一个顺序号，而3号顺序号没有被“跳过”。大家可以使用DENSE_RANK替换上面查询命令中的RANK，执行一下，观察其结果。 3 自定义“主键”原理分析
上面介绍了RANK函数的基本使用方法，这个查询命令有什么深层次的利用价值呢？辽河油田前一段時间进行数据迁移工作，即把数据从一个数据库模型迁移到另一个数据库模型，这两个数据库模型结构相差比较大，需要对源数据库中的表进行诸如拆分、合并、转换等工作，然后存储于目标数据库，这要求对源数据库中各个表之间的结构进行仔细分析，理清源数据表间的主外键关系，才能保证迁移数据的正确可靠。
在整理源表结构的过程中，发现了RANK函数一个很有价值的用途，确切地说是DENSE_RANK函数的用途：使用DENSE_RANK函数，可以根据源表中任意字段的组合，生成对源表中各条记录的唯一性“排序字段”，此“排序字段”和源表中相关字段组合，能保证源数据表中记录的唯一性，即生成关于源表的“主键”。大家知道，在ORACLE中，作为数据表中主键的字段必须是非空的，而使用DENSE_RANK函数，可以突破这一障碍，做到“自己的主键自己作主”，这对于一些无法定义主键的表，以及一些比如初期数据建设，没有考虑表的主键问题，数据质量没有保证的表进行数据整理，是很有帮助的。
自定义主键的原理是在分析函数的语法中，先使用partition by对数据表中的数据进行分堆，再用order by对分堆内的数据进行排序，而order排序时，数据是否为空不影响序号的生成，并且DENSE_RANK函数对order by表达式中的重复的记录不重复记入顺序号，保证了即使出现重复记录，也只能有一个顺序号，确保了组合数据项的唯一性。这种很方便定义“主键”的功能，会给大家的数据整理带来意外的惊喜。
4 应用案例及效果
采油X矿的一批分析化验数据见表3。
使用如下查询命令：
select t.*，dense_rank（） over（partition By 井号 Order By 取样地点，取样日期，分析日期） 样品编号from test01 t （查询语句三）
则查询结果如表4所示。
从表4可以看到，使用查询结果中的“井号 样品编号”这个组合字段，可以区分和反映不同化验样品的信息存储结果，其中取样日期和分析日期中的空值没有影响样品编号值的生成。
值得注意的是“陶4 井”，从前面的4个字段来看，同一个取样地点、同一天取样、同一天分析，但后面的分析指标不一样，这样的数据，反映了对同一个样品进行了两次分析。这在实际生产中也是有意义的。在数据整理过程中，如果随意删除任意一条数据，就会造成数据资产的丢失。
对于上面提到的这种情况，可以在查询命令中，再增加一个DENSE_RANK函数，将“指标1”和“指标2”这两个样品分析指标字段放入ORDER BY中，这样，不同的指标值也能生成不同的样品分析结果编号值。使用如下查询命令：
select t.*，dense_rank（） over（partition By 井号 Order By 取样地点，取样日期，分析日期） 样品编号，dense_rank（） over（partition By 井号 Order By 取样地点，取样日期，分析日期，指标1，指标2） 分析结果编号from test01 t （查询语句四）
则查询结果如表5所示。
从表5中可以看出，通过查询语句四中的第二个DENSE_RANK函数，可以在源表中生成一列样品的分析结果编号值，这样通过采用“井号 样品编号 分析结果编号”的组合字段，可以较好地区分源表中同一个样品的不同记录，确保了源数据表中记录的唯一性。所以，可以考虑将该组合字段作为源表的分析“主键”。
以此类推，在数据整理工作中，通过多个DENSE_RANK函数的综合使用，数据整理人员能按照数据整理的目标和意图，对数据进行分层次、分阶段的细化分析，并在不断细化的数据分析过程中在源表里产生多个具有一定涵义的排序字段，并将排序字段与源表中的部分原有字段组合在一起形成可以唯一标识源数据表中的记录且意义丰富的“主键”，用来与其他表进行关联运算，从而既能把握好源数据的层次关系，又能保证数据迁移的准确性和完整性。