基于SQL Server的情报数据分析实践

杨雁莹

（南京森林警察学院 信息技术系，江苏 南京210023）

基于SQL Server的情报数据分析实践

杨雁莹

（南京森林警察学院 信息技术系，江苏 南京210023）

对涉案基础数据进行深度分析研判是获取有效情报的重要手段，然而人工分析耗时太久，贻误战机，而情报数据分析软件耗资巨大。利用SQLServer数据库管理系统实现话单次数时长分析、新号码分析、数据比对碰撞等常用情报数据分析功能，在不增加任何费用的基础上完成了软件系统的类似功能，且简单易学、使用灵活，对基层公安机关的情报人员学习有一定的帮助。

SQL Server；情报数据；数据分析

一、引言

随着公安信息化建设的发展，各种类型的公安数据库应用系统相继建立，同时也催生了各种各样的情报数据分析软件。这些软件依托公安信息基础数据库，利用数据仓库、数据挖掘技术对涉案数据进行分析处理，为情报人员、侦查人员对案件的研判提供了便利。但这些软件系统的费用动辄几十万甚至上百万，为公安机关增加了经费开支，特别是基层处所购买这类软件负担很重。

笔者在使用这类情报数据分析软件时发现，其中很多常用数据分析功能都可以用SQL Server中的T-SQL语句实现。笔者使用免费的SQLServer2005Express进行了尝试，发现其简单易学，同时不增加任何经费开支。

二、SQL Server与excel表数据的互访

民警在日常工作中经常使用各个公安信息基础数据库，大多提供了多种查询操作。情报人员根据案情分析，可首先进行数据的初步查询，筛选得到待分析的批量嫌疑数据。因为数据库的构建可能基于不同的数据库管理系统，比较通用的方法是将查询结果导出，生成excel表，然后利用SQLServer访问excel表数据，或者直接将excel表导入SQL Server中进行分析研判。

SQLServer数据库和异构数据库之间的数据传递常用的有两种方式，使用链接服务器和使用分布式查询。链接服务器的建立称作数据源的永久性连接，如果经常进行excel表和SQL Server数据库之间的导入导出，可使用该种方法。分布式查询则是为单个查询建立的临时连接。因为SQLServer2005express中没有设置数据导入导出功能，可以使用T-SQL语句实现这两种连接的建立，从而完成excel表和数据库表的数据交互。在此，以临时连接、即分布式查询方法为例，excel以2003版本为例进行详细介绍。

（一）将excel表数据导入数据库表

分布式查询可以使用SQL Server中的OPENDATASOURCE或OPENROWSET函数来实现。OPENDATASOURCE可以打开任何支持OLEDB的数据库，并且可以将OPENDATASOURCE做为SELECT、UPDATE、INSERT和DELETE后所跟的表名。OPENROWSET函数和OPENDATASOURCE函数类似，只是它可以在打开数据库的同时对数据库中的表进行查询。本文以OPENDATASOURCE为例进行说明。

OPENDATASOURCE的语法格式为：OPENDATASOURCE(provider_name,init_string)

其中的参数：provider_name为注册用于访问数据源的OLE DB访问接口的PROGID的名称。init_string为连接字符串，该字符串将要传递给目标提供程序的 IDataInitialize接口。

比如下面的T-SQL语句：

SELECT*FROM OPENDATASOURCE('M icrosoft. Jet.OLEDB.4.0','Data Source=D:ill.xls;Extended Properties=Excel8.0')...Sheet1$

将创建与excel电子表格的临时链接，并查询本地服务器D盘根目录下的bill.xls文件中sheet1工作表中的数据。

该方法也可在select语句中，增加into子句，将excel表直接存储为SQL Server的库表。如：SELECT*into hd FROM OPENDATASOURCE('M icrosoft.Jet.OLEDB.4.0',' Data Source=D:ill.xls;Extended Properties=Excel 8.0')...Sheet1$则将excel表数据存储为当前数据库中的表hd，库表的属性根据excel表列自动生成。数据库表生成后，即可方便地进行各种查询。

（二）将数据库表输出到excel

利用数据库管理系统对数据进行各种查询分析后，获得的有效信息可直接用库表的形式存储。如果需要将该信息与别人交流，转换成excel格式是更方便的渠道。常用的一种做法是用Insertinto语句实现。INSERT into用于将新行追加到表中，可以利用OPENDATASOURCE函数直接将数据库表追加的excel表中。语法格式为：

Insert Into OPENDATASOURCE('M icrosoft.Jet.OLEDB.4.0','Data Source={Your Excel File Path};Extended Properties=Excel8.0')...[SheetName$]Values(....)

如执行以下语句：

INSERT INTO OPENDATASOURCE('M icrosoft.Jet. OLEDB.4.0','DATA Source=D:ook1.xls;Extended Properties=Excel8.0')...Sheet1$SELECT*FROM xy

该语句将数据库表 xy中的内容存入 D盘根下的book1.xls表中。需要注意的是，book1.xls要已存在，且Sheet1中必须有和数据库表xy相对应的字段。

在SQLServer Express中，为提高系统安全性，减少对系统潜在的攻击途径，一些外围应用如某些功能、服务和连接是被禁用或停止的，其中包括使用OPENROWSET和OPENDATASOURCE进行的 Ad Hoc Distributed Queries即席分布式查询）。因此，要想使用此方法，需要启用Ad Hoc Distributed Queries。

可利用系统存储过程sp_configure实现。新建查询，编写并执行如下语句：

exec sp_configure'show advanced options',1

reconfigure

exec sp_configure'Ad Hoc Distributed Queries',1

reconfigure

此后，上述对excel表的查询即可成功。为提高系统安全性，查询结束后可利用如下语句进行Ad Hoc Distributed Queries的关闭。

exec sp_configure'Ad Hoc Distributed Queries',0

reconfigure

exec sp_configure'show advanced options',0

reconfigure

三、利用SQL Server实现情报数据分析

在建立起excel表和SQL Server的连接后，即可利用SQL Server的T-SQL语句，对待分析数据进行查询分析，主要利用的就是select命令。Select命令是SQLServer中使用频率最高的语句，可用来实现各种各类查询。

SELECT语句的完整语法较复杂，但其主要子句可归纳如下：SELECT select_list[INTO new_table][FROM table_source][WHERE search_condition][GROUPBY group_by_expression][HAVING search_condition][ORDER BY order_expression[ASC|DESC]]

需要注意的是，SELECT语句中的子句顺序非常重要。可以省略可选子句，但这些子句在使用时必须按适当的顺序出现。

（一）话单次数时长分析

话单分析是现在情报人员最常用的数据分析研判手段之一。话单信息客观记录了通话双方的详细情况，反映出持机人与被叫人时间、空间活动变化的轨迹以及双方之间的内在联系，将话单信息与案件中嫌疑人的活动轨迹、涉案电话相关联，可以为甄别作案嫌疑，明确侦查方向，摸清团伙成员，跟踪锁定对象提供重要依据。目前，主要利用的话单信息有以下几种：机主资料、主叫号码、被叫号码、通话时间、通话时长、基站、主叫归属地、主叫到访地、被叫归属地、被叫到访地、电子串号等。

话单分析中最常见的是话单次数时长分析，也称频率分析。即在一定范围内，将联系人号码重复出现的次数、频率或通话时长进行排列，进而分析规律。次数时长分析是查找机主密切联系人的常用手段。

话单次数时长分析体现在数据库中就是利用group by子句，按照不同的通话号码进行分组统计，利用count()、sum ()等统计函数，计算每个号码的通话次数和时间总和，利用orderby子句，按照由多到少、由长到短实现统计结果的排序。

以移动手机的语音话单为例，话单中包括类型、对方号码、起始时间、时长、通信地点、通信类型、基本话费、长途话费、实收通信费、套餐费、作用套餐等内容。在分析次数时长时，主要统计的就是“时长”一项。但不同的通讯运营商话单格式不同，有的话单中的时长采用的是hh-mm-ss格式，为精确统计，可统一换算成以秒为单位。

首先将手机语音话单的excel表导入数据库中，生成库表，设定表名为“话单”。需要注意的是，所生成的库表属性名称自动与excel表的列名称相同，但是数据类型均为字符型。然后，编写如下语句，实现话单次数时长的统计排序。

select对方号码,通话次数=count(*),通话总长=sum (substring(时长,1,2)*3600+substring(时长,4,2)*60+substring(时长,7,2))from话单group by对方号码order by通话次数desc,通话总长desc

SQL Server中可以自动进行简单的数据类型转换，所以上述语句中直接进行了数学计算。否则，还需利用函数进行字符型数据到数值型数据的转换。

情报分析人员可通过简单的语句调整，重新确定排序关键字的顺序，也可通过增加where子句，限定分析数据的通话日期、时间范围，或者通过增加having子句，筛选次数时长在某个阈值以上的对方号码。

当然，在具体案件中，次数时长排位靠前的未必就是案件重点联系人，要考虑可能会受到嫌疑人亲情号码的干扰，要注意甄别。

（二）话单中新出现号码的分析

在很多案件中，常常出现案发后嫌疑人将涉案手机停机不用的情况。这时，利用技侦手段通过手机定位找人难以实现。一种侦查思路是利用涉案手机的通话清单，分析出其密切联系人，再对其密切联系人的通话清单进行分析研判，查找在案发后的一定时间段内，当涉案手机停用后该话单中出现的新号码，该号码就有可能是案件嫌疑人新的联系方式。侦查人员可以此为线索进行进一步的排查。

为确定是否为“新”，需依据一段时间的通话情况进行判断。比如选择案发前3个月的通话号码为参照，若在前3个月中没有出现过，就可认为是新号码。

新出现号码反映到数据库表中，就是某属性值在参照表中不存在。可利用select语句的嵌套查询实现。

仍然以移动手机的语言话单为例。假设案发时间为2013年5月1日，利用如下语句生成参照号码表b1。

Selectdistinct对方号码into b1 from话单where起始时间between'2013-02-01'and'2013-05-01'

假定案发后的通话清单表为b2，可编写T-SQL语句：

select*from b2where对方号码notin(select对方号码from b1)

该语句可获得所有新出现号码的通话清单，在此基础上，可利用前述的方法进行次数时长的分析，再由侦查人员进行研判排查。

利用SQL Server还可以实现多话单中共同联系人的查询、通话清单中时段特征分析等很多功能。只要对T-SQL语句灵活运用，SQL Server可辅助情报人员实现很多情报数据分析功能。

（三）数据比对碰撞

数据比对碰撞也称为信息碰撞、批量数据比对等，是现在常用的可疑数据分析处理方法。犯罪分子再狡猾，也离不开社会生活，他的住、行、通讯等总会留下痕迹。数据比对碰撞就可充分利用航班、旅馆业、暂住人口、网吧管理等各种信息库资料以及社会基础信息资料，根据串并案信息制作批量比对文件，或者与全国在逃人员、涉毒人员、违法犯罪人员、本地工作对象等资源库数据资料进行碰撞比对，从而为侦查人员缩小排查范围，为发现嫌疑人提供线索。

数据比对碰撞体现在数据库中是在两个（或多个）表中查找某关键字相同的元组。由关键字相同进行表的连接查询是操作关键。

比如在案件侦查中，侦查人员发现可以将2013年1月在遵义的一起盗窃案和2013年4月在兴义的案件进行串并。情报人员进行碰撞比对，调出在两个城市相应时间段内的住宿信息共1万6千多条，由专人进行逐条比对，耗费了一天半的时间比对出两个嫌疑人。利用本文提到的方法，分别将两个城市的住宿信息文档读入数据库中，设定库表文件名分别为遵义、兴义，利用身份证号码相同进行碰撞比对，几分钟的时间即可比对成功。语句描述为：

Select a.姓名,a.身份证号,a.入住时间,a.宾馆名称,b.入住时间,b.宾馆名称from遵义a join兴义b on a.身份证号= b.身份证号

一条语句即可查出所有在涉案时段内、在涉案两地都有住宿的人员。根据需要，也可在select中增加退房时间、身份证住址等属性列，以方便侦查人员的排查。若在系统调出的原始住宿记录中时间、地点等与要求不符，也可先利用select中的where语句进行筛选，将筛选结果再进行比对碰撞。

四、结语

本文介绍的方法适合在没有情报数据分析软件的基层处所使用。只要情报人员对数据库技术有初步的认识，对T-SQL语句特别是select命令稍加学习，就可灵活运用。由情报人员自己编写语句进行数据分析，可最大限度地体现情报人员自身的侦查思路，同时使用免费版的 SQL Server2005Express，不需构建硬件服务器，不需软件费用支出，在公安类数据库数据查询结果和情报人员对案情初步分析的基础上，对数据进行进一步的分析，为侦查人员工作提供线索和方向，为案件的研判提供帮助。

D631

A

1673―2391(2013)12―0033―03

2013-07-04 责任编校：边 草

中央高校基本科研业务费专项资金项目“面向案件文本的敏感数据抽取技术研究”成果之一，项目编号LGZD201324。