RANK函數在多源數據整理中的應用

[摘 要] 在遼河油田勘探與生產技術數據管理系統（簡稱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函數的綜合使用，數據整理人員能按照數據整理的目標和意圖，對數據進行分層次、分階段的細化分析，并在不斷細化的數據分析過程中在源表里產生多個具有一定涵義的排序字段，并將排序字段與源表中的部分原有字段組合在一起形成可以唯一標識源數據表中的記錄且意義豐富的“主鍵”，用來與其他表進行關聯運算，從而既能把握好源數據的層次關系，又能保證數據遷移的準確性和完整性。