巧用SQL整理不规范业务数据
石磊(重庆市合川区审计局)
【发布时间:2016年08月11日】
字号:【大】 【中】 【小】

随着信息化时代的来临,基层审计机关在审计工作中向被审计单位采集和处理的数据越来越多,但采集的数据普遍存在零星分散、规范性较差的特点。为提高审计效率,下面简要介绍两种常用不规范业务数据的处理技巧。

一、房产信息表身份证号字段多身份证号拆分

(一)项目背景

保障房审计项目中,为了查找违规享受廉租住房保障人员审计疑点,我们获取的房屋登记信息数据发现存在多人拥有同一套房屋所有权,其中房屋登记信息中身份证号码字段系多人身份证号码的情况,如“身份证:61022619****6890,身份证:61022619****5569”。为将享受廉租住房人员信息与房屋登记信息通过身份证号码进行关联分析,我们就需要把房屋登记信息按身份证号码字段分割成同一房屋多条记录。

(二)、数据源

因数据保密需要,此次仅选取房产登记信息中“[房屋拥有者]”和“[身份证号码]”两个字段举例,在实际审计工作中中可根据需要自行确定保留字段。实例数据库名称为“[测试数据]”,表名为“[房产信息部分字段]”。

(三)、数据处理方法

1、在“[测试数据]”数据库中新建[F_Get_Number]函数(同一数据库下仅执行一次以下脚本)

CREATE function [dbo].[F_Get_Number] (@S varchar(100))

returns varchar(100) AS

begin while PATINDEX('%[^0-9X]%',@S)>0

begin set @s=stuff(@s,patindex('%[^0-9X]%',@s),1,'') end

return cast(@S as varchar(100)) end

2、对房屋登记信息以“身份证号码”字段进行拆分

建立每行记录的唯一值(采集的数据可能没有唯一标识符)

if exists(select * from tempdb..sysobjects where id=OBJECT_ID('tempdb..#_test'))

drop table #_test

SELECT NEWID() id,[房屋拥有者],[身份证号码]—-中文字段为采集数据表的字段

into #_test

FROM [测试数据].[dbo].[房产信息部分字段];--写入临时表#_test

2)对数据进行预处理

if exists(select * from tempdb..sysobjects where id=OBJECT_ID('tempdb..#_test2'))

drop table #_test2;

with mycte as

( SELECT ID,CAST('<i>' + REPLACE([身份证号码], ',', '</i><i>') + '</i>' AS XML) AS KID

FROM [测试数据].[dbo].[#_test]

)

select ID,dbo.F_Get_Number( replace(x.i.value('.', 'VARCHAR(100)'),':','')) AS card_id --运用自定义函数[dbo].[F_Get_Number]

into [测试数据].[dbo].#_test2

from mycte

CROSS APPLY KID.nodes('i') x(i)

where len(x.i.value('.', 'VARCHAR(100)'))<>'' --以关键字符分隔列名[身份证号码]

3)生成分割后的房产信息数据

if exists (select * from sys.objects

where name='result' and type='U')

drop table result

SELECT b.card_id,a.*

into result

FROM [测试数据].[dbo].[#_test] a left join [测试数据].[dbo].[#_test2] b on a.id=b.id

4)查看分割后的数据结果

select * from result

将房屋登记信息处理后的结果表与享受廉租住房人员关联,发现153人涉嫌违规享受廉租住房。

二、全库关键字检索

(一)项目背景

平常我们将被审计单位采集的财务或业务数据库备份数据还原到审计数据平台后,如果对方单位没有提供数据字典,数据表名或字段像“AC01”等辨识程度不高的命名方式时,对我们下一步开展审计工作形成了一定的障碍,为提高审计效率,我们采取以下方式快速获取所需表名或表字段。

(二)数据处理方法(将以下脚本复制到所需查找的关键信息的数据库中)

1、对所需查找的关键信息的数据库新建并复制以下内容到“查询”文件中。

declare @关键字 varchar(500)

declare @类型 varchar(50)

set @关键字='陈' –-输入需要查找的关键字

set @类型='char'

---------------------------------------------

declare @cloumns varchar(500)

declare @tablename varchar(500)

declare @str varchar(500)

declare @counts int

declare @sql nvarchar(2000)

declare MyCursor Cursor For

Select a.name as Columns, b.name as TableName

from syscolumns a,sysobjects b,systypes c

where a.id = b.id

and b.type = 'U'

and a.xtype=c.xtype

and c.name like '%'+@类型+'%'

set @str=@关键字

Open MyCursor

Fetch next From MyCursor Into @cloumns,@tablename

While(@@Fetch_Status = 0)

Begin

set @sql='select @tmp_counts=count(*) from [' +@tablename+ '] where [' +@cloumns+'] like ''%' +@str+ '%'''--模糊匹配

execute sp_executesql @sql,N'@tmp_counts int out',@counts out

if @counts>0

begin

print '表名为:'+@tablename+',字段名为:'+@cloumns

end

Fetch next From MyCursor Into @cloumns,@tablename

End

Close MyCursor

Deallocate MyCursor

3、在上面脚本第三行输入关键字信息,如:set @关键字='陈'。

4、执行查询脚本。

输出结果如下:

表名为:房产信息部分字段,字段名为:房屋拥有者

表名为:result,字段名为:房屋拥有者

表名为:房产信息部分字段(演示),字段名为:房屋拥有者

表名为:地税数据,字段名为:法定代表人姓名

表名为:工商数据,字段名为:法定代表人

通过以上方式即可快速找到未知数据库中是否存在我们审计所需的内容。(石磊)

【关闭】    【打印】