個(gè)人投稿 2009-03-17 13:57:56
凡個(gè)人署名文章，均不代表國家統計局觀(guān)點(diǎn)，作者文責自負。轉載或引用時(shí)務(wù)請遵守本網(wǎng)“
版權聲明”。
數據審核是數據處理工作的重要組成部分，審核能檢查出原始數據中的各種錯誤和不一致現象，便于統計業(yè)務(wù)人員修改錯誤，對保證數據質(zhì)量有重要意義。
一、數據審核的分類(lèi)
從統計業(yè)務(wù)的角度，數據審核大致可分為表內審核、表間審核，其中表內審核有：
1.邏輯平衡關(guān)系審核，如：總計應等于分項之和。
2.值集審核：如：行業(yè)代碼應在國家標準行業(yè)代碼范圍內。
3.條件審核，如：如果是建筑業(yè)企業(yè)，資質(zhì)等級應該在國家資質(zhì)等級標準范圍內，否則不應有數據。
4.單個(gè)指標合法性審核，如：組織機構代碼應符合編碼規則。
表間審核有：
1.存在性審核，如：經(jīng)濟普查填寫(xiě)601表的單位，如果專(zhuān)業(yè)屬于規上工業(yè)，必須填寫(xiě)B103表。
2.計數審核，如：601表的產(chǎn)業(yè)單位個(gè)數指標的值應該與所屬法人是此單位的產(chǎn)業(yè)活動(dòng)單位填寫(xiě)的602表個(gè)數相等。
3.邏輯平衡關(guān)系審核，如：B105和B106表相同產(chǎn)品的產(chǎn)量應相等。
二、數據審核的設計思路比較
在統計數據處理軟件的開(kāi)發(fā)實(shí)踐中，數據批量審核大致有下面幾種實(shí)現方式：
1.單機版軟件，采用自定義數據文件格式保存數據，軟件自定義審核語(yǔ)法規則，使用自己的公式引擎解釋執行，執行時(shí)讀取數據文件，輸出審核結果。
2.使用小型關(guān)系型數據庫（如：FoxPro）自帶的語(yǔ)言編寫(xiě)處理程序，讀取保存在數據庫的數據，解釋執行。
3.網(wǎng)絡(luò )版軟件，數據保存在數據庫服務(wù)器，由運行在應用服務(wù)器上的應用軟件實(shí)現業(yè)務(wù)操作。這里面又可以分成2種做法：應用軟件從數據庫讀取數據，應用軟件操作數據并把結果存儲到數據庫，即以應用軟件為中心，數據庫承擔存儲功能；應用軟件向數據庫發(fā)出指令，由數據庫操作數據，將結果通知應用軟件，應用軟件從數據庫讀取結果數據，即以數據庫為中心。
現在我們使用的主要是網(wǎng)絡(luò )版軟件。因此下面不再討論前2種實(shí)現方式，主要針對網(wǎng)絡(luò )版軟件的2種做法的優(yōu)缺點(diǎn)進(jìn)行討論。
以應用軟件為中心的系統主要負載在應用服務(wù)器層，數據庫的功能很少用，對大批量數據處理請求而言，光是層與層之間原始數據傳輸就是很大的任務(wù)量，如果是網(wǎng)絡(luò )環(huán)境，影響更加明顯。而以數據庫為中心，層與層之間主要傳輸指令和必要的結果數據，負擔要輕得多。
三、功能實(shí)現和性能比較
我們認為，用數據庫功能可以滿(mǎn)足大部分統計業(yè)務(wù)的審核要求，下面把上述幾類(lèi)審核一一實(shí)現。
（一）表內列間審核
1.用SQL實(shí)現表的列間審核
長(cháng)期以來(lái)，人們認為SQL語(yǔ)句不適合進(jìn)行審核，如果在Where子句書(shū)寫(xiě)審核條件，那么一次查詢(xún)確實(shí)只能得出一種審核關(guān)系的檢查結果，要檢查多個(gè)審核關(guān)系就需要反復掃描原始表，效率很低。然而，用SQL語(yǔ)句的casewhen語(yǔ)法可以實(shí)現一遍掃描完成列間多個(gè)審核關(guān)系的審核。
語(yǔ)法如下：
select
case when expr then value1 else value2 end
其中expr是條件表達式,可以包含各種關(guān)系運算符，如:AND，單行函數，如POWER()，value1是expr為真時(shí)返回的值，value2是expr為假時(shí)返回的值。case when也可以嵌套。因此，用case when語(yǔ)法可以描述大多數表內單列和列間審核關(guān)系。
實(shí)例1
假定某表有79個(gè)審核關(guān)系，分別為：v2>v1,..v80>v79 ，每個(gè)審核關(guān)系給定一個(gè)序號分別是1-79。我們可以將每個(gè)單位審核結果存儲到一個(gè)數字中，該數字包含了多個(gè)序號的審核關(guān)系是否通過(guò)的信息，每個(gè)2進(jìn)制位對應1個(gè)序號的審核關(guān)系，用1表示審核通過(guò)，0表示審核不通過(guò)。
比如二進(jìn)制數11001002可以表示第1,2,5號審核關(guān)系審核通過(guò)。然后用自定義函數取各個(gè)2進(jìn)制位表示某個(gè)序號的審核關(guān)系是否通過(guò)，或者用字符串方式，用相應位置的字符“1”和“0”表示審核通過(guò)與否，用SUBSTR函數取字串判斷某個(gè)審核關(guān)系是否通過(guò)。二進(jìn)制數保存審核結果比字符串節省約7/8空間，但是取值不如字符串方便。
示例代碼：
/*用二進(jìn)制數保存審核結果*/
insert into test_err select x1,
case when v2>v1 then 2 else 0 end +
case when v3>v2 then 4 else 0 end +
:
case when v80>v79 then POWER(2,79) else 0 end +
0 from tcol80;
/*用字符串保存審核結果*/
create table test_err1(x1 varchar(10), ev varchar(255));
insert into test_err select x1,
case when v2>v1 then '1' else '0' end ||
case when v3>v2 then '1' else '0' end ||
:
case when v80>v79 then '1' else '0' end ||
0 from tcol80;
要輸出審核清單，可以將審核關(guān)系序號將審核結果表和審核關(guān)系表作關(guān)聯(lián)，按照錯誤類(lèi)型或單位分類(lèi)輸出錯誤清單。
一個(gè)實(shí)際的例子，以二經(jīng)普清查611表為例，下面語(yǔ)句實(shí)現了1-7條審核關(guān)系。
insert into err select m1,
case when (m30='3' and m1=substr(m31,1,8)||'B') or (lengthb(m1)=9 and vcj(m1)=1) then 1 else 0 end --1
||case when lengthb(m3)>=8 then 1 else 0 end --2
||case when translate(m3,'$''"@?,','$')=m3 then 1 else 0 end --3
||case when translate(m3,'$0123456789abcdefghijklmnopqrstuvwxyz','$')=m3 then 1 else 0 end --4
||case when translate(m2,'$0123456789','$')=m2 then 1 else 0 end --5
||case when lengthb(m2)>=4 then 1 else 0 end --6
||case when translate(m2,'$''"@?,','$')=m2 then 1 else 0 end --7
from V_8600000002008030607000001;
若審核結果表el具有下列數據，
id(單位代碼) flag(錯誤標志)
123456789 101
234567890 001
888999999 011
審核關(guān)系表em具有下列數據，
en(審核關(guān)系編號)msg(提示信息)
1 法人組織機構代碼 必須符合校驗公式。
2 法人單位名稱(chēng)長(cháng)度應該大于等于8個(gè)字符（至少4個(gè)漢字）
3 法人單位名稱(chēng)不能含有單引號、雙引號、逗號、問(wèn)號、@ 半角字符和半個(gè)漢字
那么按單位代碼順序輸出錯誤清單的語(yǔ)句可以這么寫(xiě)：
select id,msg from el,em where substr(flag,en,1)= '0'order by id;
ID MSG
123456789 法人單位名稱(chēng)長(cháng)度應該大于等于8個(gè)字符（至少4個(gè)漢字）
234567890 法人組織機構代碼必須符合校驗公式。
234567890 法人單位名稱(chēng)長(cháng)度應該大于等于8個(gè)字符（至少4個(gè)漢字）
888999999 法人組織機構代碼必須符合校驗公式。
2.用PL/SQL實(shí)現表的列間審核
一直以來(lái)，開(kāi)發(fā)人員往往陷入追求數據庫獨立性的誤區。他們認為，為了應用程序能夠在各種數據庫平臺上運行，不能使用某種數據庫特有的功能，比如存儲過(guò)程和特殊的函數，而只能采用各種數據庫都有的功能。其實(shí)，這種想法不太現實(shí)，實(shí)際上，各種數據庫系統都有自己的特色功能，如果不使用這些功能，而非要自己用高級語(yǔ)言實(shí)現是事倍功半的。Oracle的存儲過(guò)程和存儲函數如果使用得當，可以發(fā)揮數據庫的優(yōu)勢，減少編寫(xiě)應用程序的工作量和應用服務(wù)器的負擔。
create or replace procedure
test_chk( x in out number)is
l_cnt number;
vv tcol80%rowtype;
cursor cr is select * from tcol80;
begin
l_cnt := 0;
open cr;
loop
fetch cr into vv;
exit when cr%NOTFOUND;
if vv.v2>vv.v1 then
l_cnt:=l_cnt+2;
end if;
if vv.v3>vv.v2 then
l_cnt:=l_cnt+4;
end if;
end loop;
--dbms_output.put_line(' sum is '|| l_cnt );
close cr;
x:=l_cnt;/* 為簡(jiǎn)單起見(jiàn)，這里沒(méi)有將各單位的審核結果存回數據庫，只是求一個(gè)總數，主要反映運行時(shí)間*/
end;
/
和SQL方法一樣，可以返回2進(jìn)制數或字符串。
采用PL/SQL或應用軟件的Java過(guò)程來(lái)進(jìn)行表內列間審核,由于需要另外開(kāi)辟空間存儲某行的值與其他行進(jìn)行運算和比較，Java過(guò)程還需要進(jìn)行數據庫內外部數據傳遞和類(lèi)型轉換，審核的時(shí)間比用PL/SQL審核更長(cháng)。我們用3種語(yǔ)言同樣對40000行的表測試，結果如下：
語(yǔ)言
審核3列時(shí)間（秒）
審核80列時(shí)間（秒）
SQL
0.01
2
PL/SQL
0.05
3
Java
1.05
15
以上方法都可以實(shí)現一遍掃描，審核多個(gè)審核關(guān)系同時(shí)把結果存儲起來(lái)。同時(shí)，我們對各種方法的速度也有了初步印象。
（二）單個(gè)指標的復雜審核
對于單個(gè)指標的復雜審核，也有2種思路，一種是在過(guò)程語(yǔ)言中比較，另一種是編寫(xiě)函數由SQL調用。后者的運行速度更快。
Oracle支持SQL調用多種語(yǔ)言編寫(xiě)的自定義函數，用于解決只用SQL難以解決的復雜的問(wèn)題。對于不支持自定義函數的數據庫，只能將數據取出用編程語(yǔ)言進(jìn)行處理。
組織機構代碼審核是基本單位統計工作中必做的一項審核，它是對一個(gè)9位編碼的長(cháng)度和校驗關(guān)系的審核，用于防止錄入了錯誤的代碼而無(wú)法準確定位填表單位。編碼規則如下。
全國組織機構代碼由八位數字(或大寫(xiě)字母)本體代碼和一位數字(或大寫(xiě)字母)校驗碼組成?
1 本體代碼采用系列(即分區段)順序編碼方法?
2 校驗碼按下列公式計算:
C9=11-MOD(∑(Wi*Ci),11); i=1->8
式中:MOD：表示求余函數;
i：表示代碼字符從左至右位置序號;
Ci：表示第i位置上的代碼字符的值;,如果Ci是數字,就是字面值,如'0'的值為0,如果Ci是大寫(xiě)字母,'A'開(kāi)始算10,以此類(lèi)推,如'Z'的值為35
C9：表示校驗碼;
Wi：表示第i位置上的加權因子,其數值如下表:
i 1 2 3 4 5 6 7 8
Wi 3 7 9 10 5 8 4 2
當 MOD函數值為1(C9=10 )時(shí),校驗碼應用大寫(xiě)字母X表示;當MOD函數值為0即(C9=11)時(shí),校驗碼仍用0表示?
從上述規則可以看出，它涉及多個(gè)條件判斷和運算，用前文的case when語(yǔ)句不是不能實(shí)現，但可讀性差，用函數實(shí)現則更佳。
我們分別用PL/SQL、Java存儲過(guò)程和C語(yǔ)言外部過(guò)程來(lái)實(shí)現。然后對50000行的組織機構代碼進(jìn)行測試。結果見(jiàn)下表：
語(yǔ)言
審核組織機構代碼時(shí)間（秒）
PL/SQL
3
Java
1.03
C
2.83
在此特定的應用下，Java存儲過(guò)程有更快的速度，但其他情況則不一定，比如對數log函數，Java快于Oracle的內置函數，隨機數random函數，Oracle的DBMS_RANDOM.RAND快于Java,另外Oracle 10g XE不支持Java存儲過(guò)程，但支持PL/SQL。
C語(yǔ)言外部過(guò)程的執行速度不如Java存儲過(guò)程，所以并不見(jiàn)得編譯型語(yǔ)言速度比解釋性語(yǔ)言快。
（三）指標的行間審核
我們知道，關(guān)系型數據庫并不關(guān)心各行記錄的順序，要將查詢(xún)結果按一定順序輸出必須指定按什么條件排序。這樣一來(lái)，統計上常用的一類(lèi)表――二維表的審核就不容易了，除了定長(cháng)二維表可以在設計表結構時(shí)將多行內容保存在一行中來(lái)避開(kāi)這個(gè)問(wèn)題外，不定長(cháng)表，如工業(yè)產(chǎn)品產(chǎn)銷(xiāo)存表，必須采取特殊的方式。
參考資料提供了SQL對行間加減運算審核的實(shí)現方式，他的設計思想是將審核表達式拆分，用一個(gè)表存儲審核表達式左邊的代碼，一個(gè)表存儲審核表達式右邊的代碼，這2個(gè)表按照審核關(guān)系序號關(guān)聯(lián)，再將要審核的表按某個(gè)審核序號分組匯總就分別得到了表達式左右2邊的值，再判斷邏輯關(guān)系就容易了。采用這種行審核方式，具有以下優(yōu)點(diǎn)：1)簡(jiǎn)單易行。不用編寫(xiě)繁瑣的程序，僅用SQL語(yǔ)句就可以得到結果。2)具有通用性。審核關(guān)系存放在庫中，審核關(guān)系的增減、修改，不需要修改程序，只需修改庫中的審核關(guān)系表即可。3)效率高。經(jīng)過(guò)測試，采用這種方式進(jìn)行行間審核，比逐個(gè)審核關(guān)系編寫(xiě)SQL語(yǔ)句效率有明顯提高，尤其是在數據量大、審核關(guān)系多的情況下，更能體現出其高效性。
但是，這種方式僅適用于審核行之間的和差關(guān)系，對于復雜的乘除等關(guān)系，如“第一行=第二行*第三行”則不適用。
這里我們提供一種SQL計算行間四則運算的辦法。
數據庫的SQL語(yǔ)言只能計算行間的和，為了進(jìn)行加減乘除四則運算，我們必須找到把其他運算轉化為加法的辦法。減法轉化為加法的辦法比較簡(jiǎn)單，只要先取要減的數的相反數，然后相加就可以了。
乘法轉化為加法我們要利用數學(xué)知識，假定a的常用對數是m,10的m次冪就等于a,用公式表示為：10 (lg(a))=a。兩個(gè)數a、b的積的常用對數等于a、b各自常用對數的和，用公式表示為：lg(a×b)=lg(a)+lg(b)。這樣推論n個(gè)數a1,a2...an的連乘積∏(ai)就等于10的m次冪，其中m=∑(lg(ai))。我們再利用lg(1/a)=-lg(a)同理可以把除法轉為加法。
假定我們要求1/5*2+3-4的值
SQL> select 1/5*2+3-4 from dual;
1/5*2+3-4
----------
-.6
我們可以把數值存在表的V1列，運算符存在表的V2列，用1-4分別代表乘、除、加、減，也可以直接存字符。上述表達式存放的數值和相應運算符如下：
SQL> select * from tm;
V1 V2
---------- ----------
5 2
2 1
3 3
4 4
然后用下面的語(yǔ)句，求出行間四則運算的結果。
SQL> select sum(a) from(
2 (select power(10,sum(
3 case
4 when v2=1 then log(10,v1) --乘
5 when v2=2 then -log(10,v1) --除
6 end )) a from tm where v2<3)
7 union all
8 (select sum(
9 case
10 when v2=3 then v1 --加
11 when v2=4 then -v1 --減
12 end ) a from tm where v2>=3)
13 )
14 ;
SUM(A)
----------
-.6
以上只是示例，還需要更完善的設計才能滿(mǎn)足實(shí)際審核要求。
（四）值集審核
值集審核是指某個(gè)指標允許的取值有一個(gè)有限的集合。比如各種行政區劃、行業(yè)、產(chǎn)品、登記注冊類(lèi)型代碼等。如果這個(gè)集合的元素個(gè)數很少，如：性別，那么可以直接在條件表達式中列舉。如果元素個(gè)數較多或者可能發(fā)生變化，比如：各省可能會(huì )增加產(chǎn)品代碼，則必須把需要審核的表和相應的目錄進(jìn)行關(guān)聯(lián)查找。SQL可以用子查詢(xún)和外連接2種方式來(lái)解決這類(lèi)問(wèn)題。
實(shí)例:
t601表的地址碼審核：
select case when m4 in (select code from dzm) then 1 else 0 end from t601
或者
select case when b.code is not null then 1 else 0 end from t601 a left join dzm b on(a.m4=b.code)
如果要同時(shí)檢查多個(gè)審核關(guān)系，就需要關(guān)聯(lián)多個(gè)表，不同的寫(xiě)法效率有較大差別。
（五）表間審核
表間審核和值集審核的思路相似，但一般關(guān)聯(lián)表的個(gè)數不會(huì )太多。
601和602表間產(chǎn)業(yè)活動(dòng)單位數計數審核用SQL實(shí)現如下：
select a.frdm,case when a.m3=b.cnt then 1 else 0 end from t601 a,(select frdm,count(*) cnt from t602 group by frdm) b where a.frdm=b.frdm;
601和B103表存在性審核用SQL實(shí)現如下：
select a.frdm,case when b.frdm is not null then 1 else 0 end from (select frdm from t601 where 專(zhuān)業(yè)='規上工業(yè)') a left join B103 b on (a.frdm=b.frdm);
如果用過(guò)程語(yǔ)言實(shí)現此2項審核，必須在對t601掃描的外層循環(huán)中嵌入查找t602或B103匹配記錄的查詢(xún)語(yǔ)句，語(yǔ)句結構復雜且難以維護。
五、小結
從同樣的列間審核分別采用SQL和PL/SQL的解決辦法可知，兩種方式都可以實(shí)現對數據庫表一遍掃描，審核多個(gè)審核關(guān)系同時(shí)把結果存儲起來(lái)。而SQL和PL/SQL的運行速度比較，對同樣多的列間運算，SQL速度更勝一籌。我們有理由采用執行效果更好的SQL方式。
從不同語(yǔ)言工具單個(gè)指標的復雜審核比較結果可知，語(yǔ)言的性能差異在不同情況下各有所長(cháng)，我們應當針對用戶(hù)需求和數據庫條件合理選擇，比如采用不具備調用外部過(guò)程功能的數據庫，只能采用數據庫本身提供的語(yǔ)言。
SQL語(yǔ)句目前能夠支持包含乘除運算的行間審核。
因此，在批量審核中，首選SQL和數據庫本身提供的語(yǔ)言，因為它與數據庫管理系統集成在一起，效率最高。如果可能，優(yōu)先使用SQL語(yǔ)句解決問(wèn)題，如果計算比較復雜，可以先在PL/SQL、java中調用SQL計算中間結果，然后用過(guò)程語(yǔ)言實(shí)現復雜的運算，再把結果輸出或保存到數據庫，這也是我們在開(kāi)發(fā)應用程序中要注意的。
數據庫系統運行的環(huán)境一般是具有大量CPU和內存資源的服務(wù)器，支持并行處理，SQL已經(jīng)針對這些進(jìn)行了優(yōu)化，而自己編寫(xiě)程序實(shí)現并行有相當難度。
此外，符合SQL國際標準的數據庫系統提供了更強大的SQL語(yǔ)句，比如分析函數可以對記錄間進(jìn)行運算而只需要一次掃描；這些都為我們盡量利用SQL語(yǔ)句提供了好的條件。
如果數據庫管理系統本身提供的語(yǔ)言和SQL不能完成特定的任務(wù)，考慮用Java或C等高級語(yǔ)言實(shí)現。
使用任何一種語(yǔ)言都要遵守一些基本的原則，盡量減少不必要的重復操作（例如：全表掃描），在查找前排序，批量獲取等等。
參考資料：《Oracle中行間審核的一種實(shí)現方式》賈書(shū)民
（作者單位：國家統計局數據管理中心）
