凡个人署名文章，均不代表国家统计局观点，作者文责自负。转载或引用时务请遵守本网“版权声明”。
|
数据审核是数据处理工作的重要组成部分，审核能检查出原始数据中的各种错误和不一致现象，便于统计业务人员修改错误，对保证数据质量有重要意义。
一、数据审核的分类
从统计业务的角度，数据审核大致可分为表内审核、表间审核，其中表内审核有：
1.逻辑平衡关系审核，如：总计应等于分项之和。
2.值集审核：如：行业代码应在国家标准行业代码范围内。
3.条件审核，如：如果是建筑业企业，资质等级应该在国家资质等级标准范围内，否则不应有数据。
4.单个指标合法性审核，如：组织机构代码应符合编码规则。
表间审核有：
1.存在性审核，如：经济普查填写601表的单位，如果专业属于规上工业，必须填写B103表。
2.计数审核，如：601表的产业单位个数指标的值应该与所属法人是此单位的产业活动单位填写的602表个数相等。
3.逻辑平衡关系审核，如：B105和B106表相同产品的产量应相等。
二、数据审核的设计思路比较
在统计数据处理软件的开发实践中，数据批量审核大致有下面几种实现方式：
1.单机版软件，采用自定义数据文件格式保存数据，软件自定义审核语法规则，使用自己的公式引擎解释执行，执行时读取数据文件，输出审核结果。
2.使用小型关系型数据库（如：FoxPro）自带的语言编写处理程序，读取保存在数据库的数据，解释执行。
3.网络版软件，数据保存在数据库服务器，由运行在应用服务器上的应用软件实现业务操作。这里面又可以分成2种做法：应用软件从数据库读取数据，应用软件操作数据并把结果存储到数据库，即以应用软件为中心，数据库承担存储功能；应用软件向数据库发出指令，由数据库操作数据，将结果通知应用软件，应用软件从数据库读取结果数据，即以数据库为中心。
现在我们使用的主要是网络版软件。因此下面不再讨论前2种实现方式，主要针对网络版软件的2种做法的优缺点进行讨论。
以应用软件为中心的系统主要负载在应用服务器层，数据库的功能很少用，对大批量数据处理请求而言，光是层与层之间原始数据传输就是很大的任务量，如果是网络环境，影响更加明显。而以数据库为中心，层与层之间主要传输指令和必要的结果数据，负担要轻得多。
三、功能实现和性能比较
我们认为，用数据库功能可以满足大部分统计业务的审核要求，下面把上述几类审核一一实现。
（一）表内列间审核
1.用SQL实现表的列间审核
长期以来，人们认为SQL语句不适合进行审核，如果在Where子句书写审核条件，那么一次查询确实只能得出一种审核关系的检查结果，要检查多个审核关系就需要反复扫描原始表，效率很低。然而，用SQL语句的casewhen语法可以实现一遍扫描完成列间多个审核关系的审核。
语法如下：
select
case when expr then value1 else value2 end
其中expr是条件表达式,可以包含各种关系运算符，如:AND，单行函数，如POWER()，value1是expr为真时返回的值，value2是expr为假时返回的值。case when也可以嵌套。因此，用case when语法可以描述大多数表内单列和列间审核关系。
实例1
假定某表有79个审核关系，分别为：v2>v1,..v80>v79 ，每个审核关系给定一个序号分别是1-79。我们可以将每个单位审核结果存储到一个数字中，该数字包含了多个序号的审核关系是否通过的信息，每个2进制位对应1个序号的审核关系，用1表示审核通过，0表示审核不通过。
比如二进制数11001002可以表示第1,2,5号审核关系审核通过。然后用自定义函数取各个2进制位表示某个序号的审核关系是否通过，或者用字符串方式，用相应位置的字符“1”和“0”表示审核通过与否，用SUBSTR函数取字串判断某个审核关系是否通过。二进制数保存审核结果比字符串节省约7/8空间，但是取值不如字符串方便。
示例代码：
/*用二进制数保存审核结果*/
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;
要输出审核清单，可以将审核关系序号将审核结果表和审核关系表作关联，按照错误类型或单位分类输出错误清单。
一个实际的例子，以二经普清查611表为例，下面语句实现了1-7条审核关系。
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
审核关系表em具有下列数据，
en(审核关系编号)msg(提示信息)
1 法人组织机构代码 必须符合校验公式。
2 法人单位名称长度应该大于等于8个字符（至少4个汉字）
3 法人单位名称不能含有单引号、双引号、逗号、问号、@ 半角字符和半个汉字
那么按单位代码顺序输出错误清单的语句可以这么写：
select id,msg from el,em where substr(flag,en,1)= '0'order by id;
ID MSG
123456789 法人单位名称长度应该大于等于8个字符（至少4个汉字）
234567890 法人组织机构代码必须符合校验公式。
234567890 法人单位名称长度应该大于等于8个字符（至少4个汉字）
888999999 法人组织机构代码必须符合校验公式。
2.用PL/SQL实现表的列间审核
一直以来，开发人员往往陷入追求数据库独立性的误区。他们认为，为了应用程序能够在各种数据库平台上运行，不能使用某种数据库特有的功能，比如存储过程和特殊的函数，而只能采用各种数据库都有的功能。其实，这种想法不太现实，实际上，各种数据库系统都有自己的特色功能，如果不使用这些功能，而非要自己用高级语言实现是事倍功半的。Oracle的存储过程和存储函数如果使用得当，可以发挥数据库的优势，减少编写应用程序的工作量和应用服务器的负担。
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;/* 为简单起见，这里没有将各单位的审核结果存回数据库，只是求一个总数，主要反映运行时间*/
end;
/
和SQL方法一样，可以返回2进制数或字符串。
采用PL/SQL或应用软件的Java过程来进行表内列间审核,由于需要另外开辟空间存储某行的值与其他行进行运算和比较，Java过程还需要进行数据库内外部数据传递和类型转换，审核的时间比用PL/SQL审核更长。我们用3种语言同样对40000行的表测试，结果如下：
|
语言
|
审核3列时间（秒）
|
审核80列时间（秒）
|
|
SQL
|
0.01
|
2
|
|
PL/SQL
|
0.05
|
3
|
|
Java
|
1.05
|
15
|
以上方法都可以实现一遍扫描，审核多个审核关系同时把结果存储起来。同时，我们对各种方法的速度也有了初步印象。
（二）单个指标的复杂审核
对于单个指标的复杂审核，也有2种思路，一种是在过程语言中比较，另一种是编写函数由SQL调用。后者的运行速度更快。
Oracle支持SQL调用多种语言编写的自定义函数，用于解决只用SQL难以解决的复杂的问题。对于不支持自定义函数的数据库，只能将数据取出用编程语言进行处理。
组织机构代码审核是基本单位统计工作中必做的一项审核，它是对一个9位编码的长度和校验关系的审核，用于防止录入了错误的代码而无法准确定位填表单位。编码规则如下。
全国组织机构代码由八位数字(或大写字母)本体代码和一位数字(或大写字母)校验码组成?
1 本体代码采用系列(即分区段)顺序编码方法?
2 校验码按下列公式计算:
C9=11-MOD(∑(Wi*Ci),11); i=1->8
式中:MOD：表示求余函数;
i：表示代码字符从左至右位置序号;
Ci：表示第i位置上的代码字符的值;,如果Ci是数字,就是字面值,如'0'的值为0,如果Ci是大写字母,'A'开始算10,以此类推,如'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 )时,校验码应用大写字母X表示;当MOD函数值为0即(C9=11)时,校验码仍用0表示?
从上述规则可以看出，它涉及多个条件判断和运算，用前文的case when语句不是不能实现，但可读性差，用函数实现则更佳。
我们分别用PL/SQL、Java存储过程和C语言外部过程来实现。然后对50000行的组织机构代码进行测试。结果见下表：
|
语言
|
审核组织机构代码时间（秒）
|
|
PL/SQL
|
3
|
|
Java
|
1.03
|
|
C
|
2.83
|
在此特定的应用下，Java存储过程有更快的速度，但其他情况则不一定，比如对数log函数，Java快于Oracle的内置函数，随机数random函数，Oracle的DBMS_RANDOM.RAND快于Java,另外Oracle 10g XE不支持Java存储过程，但支持PL/SQL。
C语言外部过程的执行速度不如Java存储过程，所以并不见得编译型语言速度比解释性语言快。
（三）指标的行间审核
我们知道，关系型数据库并不关心各行记录的顺序，要将查询结果按一定顺序输出必须指定按什么条件排序。这样一来，统计上常用的一类表――二维表的审核就不容易了，除了定长二维表可以在设计表结构时将多行内容保存在一行中来避开这个问题外，不定长表，如工业产品产销存表，必须采取特殊的方式。
参考资料提供了SQL对行间加减运算审核的实现方式，他的设计思想是将审核表达式拆分，用一个表存储审核表达式左边的代码，一个表存储审核表达式右边的代码，这2个表按照审核关系序号关联，再将要审核的表按某个审核序号分组汇总就分别得到了表达式左右2边的值，再判断逻辑关系就容易了。采用这种行审核方式，具有以下优点：1)简单易行。不用编写繁琐的程序，仅用SQL语句就可以得到结果。2)具有通用性。审核关系存放在库中，审核关系的增减、修改，不需要修改程序，只需修改库中的审核关系表即可。3)效率高。经过测试，采用这种方式进行行间审核，比逐个审核关系编写SQL语句效率有明显提高，尤其是在数据量大、审核关系多的情况下，更能体现出其高效性。
但是，这种方式仅适用于审核行之间的和差关系，对于复杂的乘除等关系，如“第一行=第二行*第三行”则不适用。
这里我们提供一种SQL计算行间四则运算的办法。
数据库的SQL语言只能计算行间的和，为了进行加减乘除四则运算，我们必须找到把其他运算转化为加法的办法。减法转化为加法的办法比较简单，只要先取要减的数的相反数，然后相加就可以了。
乘法转化为加法我们要利用数学知识，假定a的常用对数是m,10的m次幂就等于a,用公式表示为：10 (lg(a))=a。两个数a、b的积的常用对数等于a、b各自常用对数的和，用公式表示为：lg(a×b)=lg(a)+lg(b)。这样推论n个数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
然后用下面的语句，求出行间四则运算的结果。
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
以上只是示例，还需要更完善的设计才能满足实际审核要求。
（四）值集审核
值集审核是指某个指标允许的取值有一个有限的集合。比如各种行政区划、行业、产品、登记注册类型代码等。如果这个集合的元素个数很少，如：性别，那么可以直接在条件表达式中列举。如果元素个数较多或者可能发生变化，比如：各省可能会增加产品代码，则必须把需要审核的表和相应的目录进行关联查找。SQL可以用子查询和外连接2种方式来解决这类问题。
实例:
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)
如果要同时检查多个审核关系，就需要关联多个表，不同的写法效率有较大差别。
（五）表间审核
表间审核和值集审核的思路相似，但一般关联表的个数不会太多。
601和602表间产业活动单位数计数审核用SQL实现如下：
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实现如下：
select a.frdm,case when b.frdm is not null then 1 else 0 end from (select frdm from t601 where 专业='规上工业') a left join B103 b on (a.frdm=b.frdm);
如果用过程语言实现此2项审核，必须在对t601扫描的外层循环中嵌入查找t602或B103匹配记录的查询语句，语句结构复杂且难以维护。
五、小结
从同样的列间审核分别采用SQL和PL/SQL的解决办法可知，两种方式都可以实现对数据库表一遍扫描，审核多个审核关系同时把结果存储起来。而SQL和PL/SQL的运行速度比较，对同样多的列间运算，SQL速度更胜一筹。我们有理由采用执行效果更好的SQL方式。
从不同语言工具单个指标的复杂审核比较结果可知，语言的性能差异在不同情况下各有所长，我们应当针对用户需求和数据库条件合理选择，比如采用不具备调用外部过程功能的数据库，只能采用数据库本身提供的语言。
SQL语句目前能够支持包含乘除运算的行间审核。
因此，在批量审核中，首选SQL和数据库本身提供的语言，因为它与数据库管理系统集成在一起，效率最高。如果可能，优先使用SQL语句解决问题，如果计算比较复杂，可以先在PL/SQL、java中调用SQL计算中间结果，然后用过程语言实现复杂的运算，再把结果输出或保存到数据库，这也是我们在开发应用程序中要注意的。
数据库系统运行的环境一般是具有大量CPU和内存资源的服务器，支持并行处理，SQL已经针对这些进行了优化，而自己编写程序实现并行有相当难度。
此外，符合SQL国际标准的数据库系统提供了更强大的SQL语句，比如分析函数可以对记录间进行运算而只需要一次扫描；这些都为我们尽量利用SQL语句提供了好的条件。
如果数据库管理系统本身提供的语言和SQL不能完成特定的任务，考虑用Java或C等高级语言实现。
使用任何一种语言都要遵守一些基本的原则，尽量减少不必要的重复操作（例如：全表扫描），在查找前排序，批量获取等等。
参考资料：《Oracle中行间审核的一种实现方式》贾书民
（作者单位：国家统计局数据管理中心）
|
