RANK函數(shù)在多源數(shù)據整理中的應用

[摘 要] 在遼河油田勘探與生產技術數(shù)據管理系統(tǒng)（簡稱A1）數(shù)據主庫建設中，對來自于多數(shù)據源的數(shù)據，通常存在大量的數(shù)據冗余、重復記錄或一點多值情況。如何高效識別和區(qū)分有效或無效記錄，成為影響海量勘探開發(fā)結構化數(shù)據由專業(yè)庫到主庫入庫效率的瓶頸問題，同時也是影響主庫數(shù)據完整性和唯一性的核心問題。本文對ORACLE 系統(tǒng)中RANK函數(shù)進行應用嘗試，總結分析了RANK函數(shù)的使用要點，在實際應用中取得了良好效果。

[關鍵詞] 數(shù)據整理；ORACLE；RANK分析函數(shù)；主鍵

doi ： 10 . 3969 / j . issn . 1673 - 0194 . 2013 . 15. 036

[中圖分類號] TP274；TP392 [文獻標識碼] A [文章編號] 1673 - 0194（2013）15- 0056- 04

1 引 言

勘探與生產技術數(shù)據管理系統(tǒng)（簡稱A1）及其數(shù)據主庫建設是中國石油“十一五”信息化建設的重點項目，為確保A1系統(tǒng)主庫結構化數(shù)據的完整性、準確性和一致性，中國石油發(fā)布了統(tǒng)一的EPDM數(shù)據模型，為數(shù)據建設與管理人員提供了規(guī)范的數(shù)據標準。在A1系統(tǒng)建設與運維過程中，為減少數(shù)據重復錄入工作量，數(shù)據建設與管理人員對油田公司勘探開發(fā)專業(yè)數(shù)據庫已采集入庫的海量結構化數(shù)據，采用數(shù)據遷移技術，由各專業(yè)數(shù)據庫遷移到A1系統(tǒng)數(shù)據主庫，以實現(xiàn)對物探、鉆井、錄井、測井、分析化驗、地質等專業(yè)數(shù)據的集中管理和集成應用，更有效地支持勘探開發(fā)項目研究工作的開展，保護勘探開發(fā)數(shù)據資產，提升數(shù)據的綜合應用價值。

遼河油田勘探、開發(fā)領域的不同專業(yè)數(shù)據，主要來自于遼河油田研究院已建勘探、開發(fā)、鉆井、錄井、分析化驗等專業(yè)數(shù)據庫以及各采油廠建立的專業(yè)庫。分析發(fā)現(xiàn)，這些專業(yè)庫通常具有多數(shù)據源、異構、數(shù)據關聯(lián)性弱、數(shù)據完整性不足、不同專業(yè)間數(shù)據一致性差等特征。如何有效地規(guī)劃、梳理和清洗專業(yè)庫中的數(shù)據，使之符合A1系統(tǒng)主庫數(shù)據入庫標準，是油田數(shù)據建設與管理人員面臨的難題之一。

在專業(yè)數(shù)據實際整理過程中，遼河油田數(shù)據建設與管理人員借助ORACLE系統(tǒng)提供的RANK分析函數(shù)功能，較好地實現(xiàn)了多源結構化數(shù)據記錄的識別與區(qū)分，解決了影響海量勘探開發(fā)結構化數(shù)據由專業(yè)庫到A1系統(tǒng)主庫入庫效率的瓶頸問題，取得了良好的使用效果。下面我們將結合實際應用案例，總結并分析ORACLE分析函數(shù)的功能和使用要點，供大家借鑒參考。

2 RANK函數(shù)的功能

一般認為ORACLE提供的RANK函數(shù)就是查詢命令中聚合函數(shù)下GROUP BY功能的延伸，GROUP BY的功能是查詢一個集合的統(tǒng)計信息，比如查詢一個部門的總銷售額、平均銷售額等數(shù)據，使用格式為：

select deptno，sum（sal），Avg（sal） from emp Group by deptno；（查詢語句一）

查詢結果如表1所示。即對部門號為10，20，30的單位，查詢其部門的總銷售額和平均銷售額。注意：一個部門只有一條記錄。

但要求查詢各部門內部，各員工的銷售額排名先后次序時，GROUP BY就無能為力了，這就需要用到ORACLE的分析函數(shù)RANK。

RANK的本意是等級，也就是說這是一個給查詢數(shù)據確定等級的函數(shù)[1]。

ORACLE從8.1.6版本開始提供分析函數(shù)，分析函數(shù)用于計算基于組的某種聚合值，它和聚合函數(shù)的不同之處是，對于每個組可返回多行，而聚合函數(shù)對于每個組只返回一行[2]（見上面的GROUP BY）。RANK是ORACLE中比較典型的一個分析函數(shù)，用它就能對各部門內部各員工的銷售額進行排序，其使用方法是：

select deptno，ename，sal，rank（） over （partition by deptno order by sal） rank from emp order by deptno；（查詢語句二）

查詢結果如表2所示。

從查詢結果可以看出，RANK函數(shù)給出了各個部門內部，各員工的銷售額升序排列的詳細列表。從列表中可以清楚地看出，在10號部門內，員工MILLER銷售額最低，KING銷售額最高等信息。

2.1 RANK函數(shù)使用的關鍵點分析

分析函數(shù)RANK的語法見圖1。

用好RANK函數(shù)的關鍵，是理解partition by和order by的運用，筆者認為partition by之后的字段來劃分堆，形成“分堆區(qū)”，而order by之后字段來排序，形成“排序區(qū)”，因此，RANK的使用可以分解為：先根據部門號來“分堆”，再根據銷售額來“排序”，分兩步走，這樣就好理解了，這也是ORACLE分析函數(shù)的使用關鍵，即先選擇“分堆區(qū)”的字段，再選擇“排序區(qū)”的字段。奇怪的是ORACLE數(shù)據庫系統(tǒng)對于這樣的查詢，執(zhí)行效率還是比較高的，這也便于該類函數(shù)的推廣應用。

注意：一般在“分堆區(qū)”的字段要比“排序區(qū)”的字段在范圍上大一些，這樣便于對表中的數(shù)據進行分堆，而且“分堆區(qū)”與“排序區(qū)”的字段不能重復，否則查詢的結果都是1，就沒有意義了，比如上面的例子中，員工的銷售額字段不如部門字段的范圍大，所以在“分堆區(qū)”內使用了部門號字段，在“排序區(qū)”內使用了員工銷售額字段，得到了相應的查詢結果。

在實際使用RANK函數(shù)時，用戶往往對“分堆區(qū)”和“排序區(qū)”的字段不能正確運用，而導致不能得到滿意的結果。一般認為，把握好“分堆區(qū)”字段范圍大于“排序區(qū)”字段范圍，而且兩個分堆區(qū)內字段不能重復，掌握好這兩個技巧，就能運用好RANK函數(shù)甚至其他各個分析函數(shù)。

2.2 RANK及DENSE_RANK函數(shù)的區(qū)別

還有一個與RANK函數(shù)類似的函數(shù)DENSE_RANK，兩者的區(qū)別在于對ORDER BY之后排序字段的處理不同。RANK函數(shù)在ORDER BY之后的排序字段出現(xiàn)重復值時，將重復值也計入累積順序號，這樣在排下面一個號時就會發(fā)生“跳號”現(xiàn)象，例如可能產生1、2、2、4、5、6的結果，中間的3號被“跳過”了，原因是有兩條重復的排序為2號的記錄，占用兩個順序號，所以下一條記錄的順序號分配為4；而DENSE_RANK則不會產生“跳號”現(xiàn)象，它對重復的記錄，只記入一次順序號，例如可能產生1、2、2、3、4、5的結果，中間兩條重復記錄，只分配了一個順序號，而3號順序號沒有被“跳過”。大家可以使用DENSE_RANK替換上面查詢命令中的RANK，執(zhí)行一下，觀察其結果。

3 自定義“主鍵”原理分析

上面介紹了RANK函數(shù)的基本使用方法，這個查詢命令有什么深層次的利用價值呢？遼河油田前一段時間進行數(shù)據遷移工作，即把數(shù)據從一個數(shù)據庫模型遷移到另一個數(shù)據庫模型，這兩個數(shù)據庫模型結構相差比較大，需要對源數(shù)據庫中的表進行諸如拆分、合并、轉換等工作，然后存儲于目標數(shù)據庫，這要求對源數(shù)據庫中各個表之間的結構進行仔細分析，理清源數(shù)據表間的主外鍵關系，才能保證遷移數(shù)據的正確可靠。

在整理源表結構的過程中，發(fā)現(xiàn)了RANK函數(shù)一個很有價值的用途，確切地說是DENSE_RANK函數(shù)的用途：使用DENSE_RANK函數(shù)，可以根據源表中任意字段的組合，生成對源表中各條記錄的唯一性“排序字段”，此“排序字段”和源表中相關字段組合，能保證源數(shù)據表中記錄的唯一性，即生成關于源表的“主鍵”。大家知道，在ORACLE中，作為數(shù)據表中主鍵的字段必須是非空的，而使用DENSE_RANK函數(shù)，可以突破這一障礙，做到“自己的主鍵自己作主”，這對于一些無法定義主鍵的表，以及一些比如初期數(shù)據建設，沒有考慮表的主鍵問題，數(shù)據質量沒有保證的表進行數(shù)據整理，是很有幫助的。

自定義主鍵的原理是在分析函數(shù)的語法中，先使用partition by對數(shù)據表中的數(shù)據進行分堆，再用order by對分堆內的數(shù)據進行排序，而order排序時，數(shù)據是否為空不影響序號的生成，并且DENSE_RANK函數(shù)對order by表達式中的重復的記錄不重復記入順序號，保證了即使出現(xiàn)重復記錄，也只能有一個順序號，確保了組合數(shù)據項的唯一性。這種很方便定義“主鍵”的功能，會給大家的數(shù)據整理帶來意外的驚喜。

4 應用案例及效果

采油X礦的一批分析化驗數(shù)據見表3。

使用如下查詢命令：

select t.*，dense_rank（） over（partition By 井號 Order By 取樣地點，取樣日期，分析日期） 樣品編號from test01 t （查詢語句三）

則查詢結果如表4所示。

從表4可以看到，使用查詢結果中的“井號+樣品編號”這個組合字段，可以區(qū)分和反映不同化驗樣品的信息存儲結果，其中取樣日期和分析日期中的空值沒有影響樣品編號值的生成。

值得注意的是“陶4 井”，從前面的4個字段來看，同一個取樣地點、同一天取樣、同一天分析，但后面的分析指標不一樣，這樣的數(shù)據，反映了對同一個樣品進行了兩次分析。這在實際生產中也是有意義的。在數(shù)據整理過程中，如果隨意刪除任意一條數(shù)據，就會造成數(shù)據資產的丟失。

對于上面提到的這種情況，可以在查詢命令中，再增加一個DENSE_RANK函數(shù)，將“指標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函數(shù)，可以在源表中生成一列樣品的分析結果編號值，這樣通過采用“井號+樣品編號+分析結果編號”的組合字段，可以較好地區(qū)分源表中同一個樣品的不同記錄，確保了源數(shù)據表中記錄的唯一性。所以，可以考慮將該組合字段作為源表的分析“主鍵”。

以此類推，在數(shù)據整理工作中，通過多個DENSE_RANK函數(shù)的綜合使用，數(shù)據整理人員能按照數(shù)據整理的目標和意圖，對數(shù)據進行分層次、分階段的細化分析，并在不斷細化的數(shù)據分析過程中在源表里產生多個具有一定涵義的排序字段，并將排序字段與源表中的部分原有字段組合在一起形成可以唯一標識源數(shù)據表中的記錄且意義豐富的“主鍵”，用來與其他表進行關聯(lián)運算，從而既能把握好源數(shù)據的層次關系，又能保證數(shù)據遷移的準確性和完整性。