基于SQL Server的情報(bào)數(shù)據(jù)分析實(shí)踐

楊雁瑩

（南京森林警察學(xué)院 信息技術(shù)系，江蘇 南京210023）

基于SQL Server的情報(bào)數(shù)據(jù)分析實(shí)踐

楊雁瑩

（南京森林警察學(xué)院 信息技術(shù)系，江蘇 南京210023）

對涉案基礎(chǔ)數(shù)據(jù)進(jìn)行深度分析研判是獲取有效情報(bào)的重要手段，然而人工分析耗時(shí)太久，貽誤戰(zhàn)機(jī)，而情報(bào)數(shù)據(jù)分析軟件耗資巨大。利用SQLServer數(shù)據(jù)庫管理系統(tǒng)實(shí)現(xiàn)話單次數(shù)時(shí)長分析、新號碼分析、數(shù)據(jù)比對碰撞等常用情報(bào)數(shù)據(jù)分析功能，在不增加任何費(fèi)用的基礎(chǔ)上完成了軟件系統(tǒng)的類似功能，且簡單易學(xué)、使用靈活，對基層公安機(jī)關(guān)的情報(bào)人員學(xué)習(xí)有一定的幫助。

SQL Server；情報(bào)數(shù)據(jù)；數(shù)據(jù)分析

一、引言

隨著公安信息化建設(shè)的發(fā)展，各種類型的公安數(shù)據(jù)庫應(yīng)用系統(tǒng)相繼建立，同時(shí)也催生了各種各樣的情報(bào)數(shù)據(jù)分析軟件。這些軟件依托公安信息基礎(chǔ)數(shù)據(jù)庫，利用數(shù)據(jù)倉庫、數(shù)據(jù)挖掘技術(shù)對涉案數(shù)據(jù)進(jìn)行分析處理，為情報(bào)人員、偵查人員對案件的研判提供了便利。但這些軟件系統(tǒng)的費(fèi)用動(dòng)輒幾十萬甚至上百萬，為公安機(jī)關(guān)增加了經(jīng)費(fèi)開支，特別是基層處所購買這類軟件負(fù)擔(dān)很重。

筆者在使用這類情報(bào)數(shù)據(jù)分析軟件時(shí)發(fā)現(xiàn)，其中很多常用數(shù)據(jù)分析功能都可以用SQL Server中的T-SQL語句實(shí)現(xiàn)。筆者使用免費(fèi)的SQLServer2005Express進(jìn)行了嘗試，發(fā)現(xiàn)其簡單易學(xué)，同時(shí)不增加任何經(jīng)費(fèi)開支。

二、SQL Server與excel表數(shù)據(jù)的互訪

民警在日常工作中經(jīng)常使用各個(gè)公安信息基礎(chǔ)數(shù)據(jù)庫，大多提供了多種查詢操作。情報(bào)人員根據(jù)案情分析，可首先進(jìn)行數(shù)據(jù)的初步查詢，篩選得到待分析的批量嫌疑數(shù)據(jù)。因?yàn)閿?shù)據(jù)庫的構(gòu)建可能基于不同的數(shù)據(jù)庫管理系統(tǒng)，比較通用的方法是將查詢結(jié)果導(dǎo)出，生成excel表，然后利用SQLServer訪問excel表數(shù)據(jù)，或者直接將excel表導(dǎo)入SQL Server中進(jìn)行分析研判。

SQLServer數(shù)據(jù)庫和異構(gòu)數(shù)據(jù)庫之間的數(shù)據(jù)傳遞常用的有兩種方式，使用鏈接服務(wù)器和使用分布式查詢。鏈接服務(wù)器的建立稱作數(shù)據(jù)源的永久性連接，如果經(jīng)常進(jìn)行excel表和SQL Server數(shù)據(jù)庫之間的導(dǎo)入導(dǎo)出，可使用該種方法。分布式查詢則是為單個(gè)查詢建立的臨時(shí)連接。因?yàn)镾QLServer2005express中沒有設(shè)置數(shù)據(jù)導(dǎo)入導(dǎo)出功能，可以使用T-SQL語句實(shí)現(xiàn)這兩種連接的建立，從而完成excel表和數(shù)據(jù)庫表的數(shù)據(jù)交互。在此，以臨時(shí)連接、即分布式查詢方法為例，excel以2003版本為例進(jìn)行詳細(xì)介紹。

（一）將excel表數(shù)據(jù)導(dǎo)入數(shù)據(jù)庫表

分布式查詢可以使用SQL Server中的OPENDATASOURCE或OPENROWSET函數(shù)來實(shí)現(xiàn)。OPENDATASOURCE可以打開任何支持OLEDB的數(shù)據(jù)庫，并且可以將OPENDATASOURCE做為SELECT、UPDATE、INSERT和DELETE后所跟的表名。OPENROWSET函數(shù)和OPENDATASOURCE函數(shù)類似，只是它可以在打開數(shù)據(jù)庫的同時(shí)對數(shù)據(jù)庫中的表進(jìn)行查詢。本文以O(shè)PENDATASOURCE為例進(jìn)行說明。

OPENDATASOURCE的語法格式為：OPENDATASOURCE(provider_name,init_string)

其中的參數(shù)：provider_name為注冊用于訪問數(shù)據(jù)源的OLE DB訪問接口的PROGID的名稱。init_string為連接字符串，該字符串將要傳遞給目標(biāo)提供程序的 IDataInitialize接口。

比如下面的T-SQL語句：

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

將創(chuàng)建與excel電子表格的臨時(shí)鏈接，并查詢本地服務(wù)器D盤根目錄下的bill.xls文件中sheet1工作表中的數(shù)據(jù)。

該方法也可在select語句中，增加into子句，將excel表直接存儲(chǔ)為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表數(shù)據(jù)存儲(chǔ)為當(dāng)前數(shù)據(jù)庫中的表hd，庫表的屬性根據(jù)excel表列自動(dòng)生成。數(shù)據(jù)庫表生成后，即可方便地進(jìn)行各種查詢。

（二）將數(shù)據(jù)庫表輸出到excel

利用數(shù)據(jù)庫管理系統(tǒng)對數(shù)據(jù)進(jìn)行各種查詢分析后，獲得的有效信息可直接用庫表的形式存儲(chǔ)。如果需要將該信息與別人交流，轉(zhuǎn)換成excel格式是更方便的渠道。常用的一種做法是用Insertinto語句實(shí)現(xiàn)。INSERT into用于將新行追加到表中，可以利用OPENDATASOURCE函數(shù)直接將數(shù)據(jù)庫表追加的excel表中。語法格式為：

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

如執(zhí)行以下語句：

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

該語句將數(shù)據(jù)庫表 xy中的內(nèi)容存入 D盤根下的book1.xls表中。需要注意的是，book1.xls要已存在，且Sheet1中必須有和數(shù)據(jù)庫表xy相對應(yīng)的字段。

在SQLServer Express中，為提高系統(tǒng)安全性，減少對系統(tǒng)潛在的攻擊途徑，一些外圍應(yīng)用如某些功能、服務(wù)和連接是被禁用或停止的，其中包括使用OPENROWSET和OPENDATASOURCE進(jìn)行的 Ad Hoc Distributed Queries即席分布式查詢）。因此，要想使用此方法，需要啟用Ad Hoc Distributed Queries。

可利用系統(tǒng)存儲(chǔ)過程sp_configure實(shí)現(xiàn)。新建查詢，編寫并執(zhí)行如下語句：

exec sp_configure'show advanced options',1

reconfigure

exec sp_configure'Ad Hoc Distributed Queries',1

reconfigure

此后，上述對excel表的查詢即可成功。為提高系統(tǒng)安全性，查詢結(jié)束后可利用如下語句進(jìn)行Ad Hoc Distributed Queries的關(guān)閉。

exec sp_configure'Ad Hoc Distributed Queries',0

reconfigure

exec sp_configure'show advanced options',0

reconfigure

三、利用SQL Server實(shí)現(xiàn)情報(bào)數(shù)據(jù)分析

在建立起excel表和SQL Server的連接后，即可利用SQL Server的T-SQL語句，對待分析數(shù)據(jù)進(jìn)行查詢分析，主要利用的就是select命令。Select命令是SQLServer中使用頻率最高的語句，可用來實(shí)現(xiàn)各種各類查詢。

SELECT語句的完整語法較復(fù)雜，但其主要子句可歸納如下：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語句中的子句順序非常重要?？梢允÷钥蛇x子句，但這些子句在使用時(shí)必須按適當(dāng)?shù)捻樞虺霈F(xiàn)。

（一）話單次數(shù)時(shí)長分析

話單分析是現(xiàn)在情報(bào)人員最常用的數(shù)據(jù)分析研判手段之一。話單信息客觀記錄了通話雙方的詳細(xì)情況，反映出持機(jī)人與被叫人時(shí)間、空間活動(dòng)變化的軌跡以及雙方之間的內(nèi)在聯(lián)系，將話單信息與案件中嫌疑人的活動(dòng)軌跡、涉案電話相關(guān)聯(lián)，可以為甄別作案嫌疑，明確偵查方向，摸清團(tuán)伙成員，跟蹤鎖定對象提供重要依據(jù)。目前，主要利用的話單信息有以下幾種：機(jī)主資料、主叫號碼、被叫號碼、通話時(shí)間、通話時(shí)長、基站、主叫歸屬地、主叫到訪地、被叫歸屬地、被叫到訪地、電子串號等。

話單分析中最常見的是話單次數(shù)時(shí)長分析，也稱頻率分析。即在一定范圍內(nèi)，將聯(lián)系人號碼重復(fù)出現(xiàn)的次數(shù)、頻率或通話時(shí)長進(jìn)行排列，進(jìn)而分析規(guī)律。次數(shù)時(shí)長分析是查找機(jī)主密切聯(lián)系人的常用手段。

話單次數(shù)時(shí)長分析體現(xiàn)在數(shù)據(jù)庫中就是利用group by子句，按照不同的通話號碼進(jìn)行分組統(tǒng)計(jì)，利用count()、sum ()等統(tǒng)計(jì)函數(shù)，計(jì)算每個(gè)號碼的通話次數(shù)和時(shí)間總和，利用orderby子句，按照由多到少、由長到短實(shí)現(xiàn)統(tǒng)計(jì)結(jié)果的排序。

以移動(dòng)手機(jī)的語音話單為例，話單中包括類型、對方號碼、起始時(shí)間、時(shí)長、通信地點(diǎn)、通信類型、基本話費(fèi)、長途話費(fèi)、實(shí)收通信費(fèi)、套餐費(fèi)、作用套餐等內(nèi)容。在分析次數(shù)時(shí)長時(shí)，主要統(tǒng)計(jì)的就是“時(shí)長”一項(xiàng)。但不同的通訊運(yùn)營商話單格式不同，有的話單中的時(shí)長采用的是hh-mm-ss格式，為精確統(tǒng)計(jì)，可統(tǒng)一換算成以秒為單位。

首先將手機(jī)語音話單的excel表導(dǎo)入數(shù)據(jù)庫中，生成庫表，設(shè)定表名為“話單”。需要注意的是，所生成的庫表屬性名稱自動(dòng)與excel表的列名稱相同，但是數(shù)據(jù)類型均為字符型。然后，編寫如下語句，實(shí)現(xiàn)話單次數(shù)時(shí)長的統(tǒng)計(jì)排序。

select對方號碼,通話次數(shù)=count(*),通話總長=sum (substring(時(shí)長,1,2)*3600+substring(時(shí)長,4,2)*60+substring(時(shí)長,7,2))from話單group by對方號碼order by通話次數(shù)desc,通話總長desc

SQL Server中可以自動(dòng)進(jìn)行簡單的數(shù)據(jù)類型轉(zhuǎn)換，所以上述語句中直接進(jìn)行了數(shù)學(xué)計(jì)算。否則，還需利用函數(shù)進(jìn)行字符型數(shù)據(jù)到數(shù)值型數(shù)據(jù)的轉(zhuǎn)換。

情報(bào)分析人員可通過簡單的語句調(diào)整，重新確定排序關(guān)鍵字的順序，也可通過增加where子句，限定分析數(shù)據(jù)的通話日期、時(shí)間范圍，或者通過增加having子句，篩選次數(shù)時(shí)長在某個(gè)閾值以上的對方號碼。

當(dāng)然，在具體案件中，次數(shù)時(shí)長排位靠前的未必就是案件重點(diǎn)聯(lián)系人，要考慮可能會(huì)受到嫌疑人親情號碼的干擾，要注意甄別。

（二）話單中新出現(xiàn)號碼的分析

在很多案件中，常常出現(xiàn)案發(fā)后嫌疑人將涉案手機(jī)停機(jī)不用的情況。這時(shí)，利用技偵手段通過手機(jī)定位找人難以實(shí)現(xiàn)。一種偵查思路是利用涉案手機(jī)的通話清單，分析出其密切聯(lián)系人，再對其密切聯(lián)系人的通話清單進(jìn)行分析研判，查找在案發(fā)后的一定時(shí)間段內(nèi)，當(dāng)涉案手機(jī)停用后該話單中出現(xiàn)的新號碼，該號碼就有可能是案件嫌疑人新的聯(lián)系方式。偵查人員可以此為線索進(jìn)行進(jìn)一步的排查。

為確定是否為“新”，需依據(jù)一段時(shí)間的通話情況進(jìn)行判斷。比如選擇案發(fā)前3個(gè)月的通話號碼為參照，若在前3個(gè)月中沒有出現(xiàn)過，就可認(rèn)為是新號碼。

新出現(xiàn)號碼反映到數(shù)據(jù)庫表中，就是某屬性值在參照表中不存在。可利用select語句的嵌套查詢實(shí)現(xiàn)。

仍然以移動(dòng)手機(jī)的語言話單為例。假設(shè)案發(fā)時(shí)間為2013年5月1日，利用如下語句生成參照號碼表b1。

Selectdistinct對方號碼into b1 from話單where起始時(shí)間between'2013-02-01'and'2013-05-01'

假定案發(fā)后的通話清單表為b2，可編寫T-SQL語句：

select*from b2where對方號碼notin(select對方號碼from b1)

該語句可獲得所有新出現(xiàn)號碼的通話清單，在此基礎(chǔ)上，可利用前述的方法進(jìn)行次數(shù)時(shí)長的分析，再由偵查人員進(jìn)行研判排查。

利用SQL Server還可以實(shí)現(xiàn)多話單中共同聯(lián)系人的查詢、通話清單中時(shí)段特征分析等很多功能。只要對T-SQL語句靈活運(yùn)用，SQL Server可輔助情報(bào)人員實(shí)現(xiàn)很多情報(bào)數(shù)據(jù)分析功能。

（三）數(shù)據(jù)比對碰撞

數(shù)據(jù)比對碰撞也稱為信息碰撞、批量數(shù)據(jù)比對等，是現(xiàn)在常用的可疑數(shù)據(jù)分析處理方法。犯罪分子再狡猾，也離不開社會(huì)生活，他的住、行、通訊等總會(huì)留下痕跡。數(shù)據(jù)比對碰撞就可充分利用航班、旅館業(yè)、暫住人口、網(wǎng)吧管理等各種信息庫資料以及社會(huì)基礎(chǔ)信息資料，根據(jù)串并案信息制作批量比對文件，或者與全國在逃人員、涉毒人員、違法犯罪人員、本地工作對象等資源庫數(shù)據(jù)資料進(jìn)行碰撞比對，從而為偵查人員縮小排查范圍，為發(fā)現(xiàn)嫌疑人提供線索。

數(shù)據(jù)比對碰撞體現(xiàn)在數(shù)據(jù)庫中是在兩個(gè)（或多個(gè)）表中查找某關(guān)鍵字相同的元組。由關(guān)鍵字相同進(jìn)行表的連接查詢是操作關(guān)鍵。

比如在案件偵查中，偵查人員發(fā)現(xiàn)可以將2013年1月在遵義的一起盜竊案和2013年4月在興義的案件進(jìn)行串并。情報(bào)人員進(jìn)行碰撞比對，調(diào)出在兩個(gè)城市相應(yīng)時(shí)間段內(nèi)的住宿信息共1萬6千多條，由專人進(jìn)行逐條比對，耗費(fèi)了一天半的時(shí)間比對出兩個(gè)嫌疑人。利用本文提到的方法，分別將兩個(gè)城市的住宿信息文檔讀入數(shù)據(jù)庫中，設(shè)定庫表文件名分別為遵義、興義，利用身份證號碼相同進(jìn)行碰撞比對，幾分鐘的時(shí)間即可比對成功。語句描述為：

Select a.姓名,a.身份證號,a.入住時(shí)間,a.賓館名稱,b.入住時(shí)間,b.賓館名稱from遵義a join興義b on a.身份證號= b.身份證號

一條語句即可查出所有在涉案時(shí)段內(nèi)、在涉案兩地都有住宿的人員。根據(jù)需要，也可在select中增加退房時(shí)間、身份證住址等屬性列，以方便偵查人員的排查。若在系統(tǒng)調(diào)出的原始住宿記錄中時(shí)間、地點(diǎn)等與要求不符，也可先利用select中的where語句進(jìn)行篩選，將篩選結(jié)果再進(jìn)行比對碰撞。

四、結(jié)語

本文介紹的方法適合在沒有情報(bào)數(shù)據(jù)分析軟件的基層處所使用。只要情報(bào)人員對數(shù)據(jù)庫技術(shù)有初步的認(rèn)識，對T-SQL語句特別是select命令稍加學(xué)習(xí)，就可靈活運(yùn)用。由情報(bào)人員自己編寫語句進(jìn)行數(shù)據(jù)分析，可最大限度地體現(xiàn)情報(bào)人員自身的偵查思路，同時(shí)使用免費(fèi)版的 SQL Server2005Express，不需構(gòu)建硬件服務(wù)器，不需軟件費(fèi)用支出，在公安類數(shù)據(jù)庫數(shù)據(jù)查詢結(jié)果和情報(bào)人員對案情初步分析的基礎(chǔ)上，對數(shù)據(jù)進(jìn)行進(jìn)一步的分析，為偵查人員工作提供線索和方向，為案件的研判提供幫助。

D631

A

1673―2391(2013)12―0033―03

2013-07-04 責(zé)任編校：邊 草

中央高?；究蒲袠I(yè)務(wù)費(fèi)專項(xiàng)資金項(xiàng)目“面向案件文本的敏感數(shù)據(jù)抽取技術(shù)研究”成果之一，項(xiàng)目編號LGZD201324。