如何產生比較像樣的假資料[收藏]

 

 

在做項目的時候經常會遇到這樣的問題：

• 根據資料模型建立了資料庫，但是資料庫中卻沒有資料，在給客戶做Demo的時候必須要一條一條的添加假資料，而且這些假資料還得像模像樣的，不能亂輸入，儘是看不出任何意義的“aaaaa”、“ttttttttttttt”、“123123”、“是打發斯蒂芬”這樣的資料。
• 已經做好了一個系統，並且上線給部分客戶使用了，現在要將該系統推廣到所有的客戶，所以需要做一個虛擬客戶的系統，系統中需要有許多像樣的資料，但是由於保密方面的原因，原有客戶的資料必須經過處理，不能出現真實的資訊。
• 系統開發完成了，需要製造大量的假資料，以進行壓力測試，看在有幾百萬上千萬資料量的情況下的系統效能。

方案

其中要產生大量的沒有意義的測試資料，以便進行壓力測試，這個資料是最好產生的，只需要寫幾條SQL語句，多運行幾次即可。如果不想寫SQL語句，也可以使用資料產生工具：VisualStudio、PowerDesigner、DataFactory等都可以使用。我推薦使用DataFactory，有較強的定製性。

下面主要說一下另外一種假資料，那就是前面2種情況，具有一定商務規則和可讀性的假資料。要產生比較像樣的假資料主要是基於已有的系統，在真實資料的基礎上進行隨機的混淆和交叉，從而產生大量看起來比較真實但是實際上卻全是假的資料。對於第一種情況，可以將其他系統中的對應實體表的資料匯入到Demo環境中，然後再進行混淆交叉。

我們可以將系統中的資料分為：數字、日期和字串3種類型分別進行混淆。

1. 數字類型的資料混淆最簡單，使用隨機函數RAND()即可，如果是整數則可以再乘以一個係數後取整，也可以用原來的資料加上產生的隨機數，從而使得資料的範圍保持在原真實資料相同的分布。比如有Revenue欄位，是從客戶處的收入，大客戶和小客戶參數的收入數不能完全隨機，可以在原有Revenue的基礎上隨機增加10000以內的數即可：Revenue+RAND()*10000
2. 日期類型的資料混淆可以在原日期或者當前日期的基礎上加減一個隨機的天數形成，使用DATEADD()函數和RAND()函數即可。比如產生隨機的最近100天內的日期：DATEADD("day",0-RAND()*100,GETDATE())
3. 字串類型的資料混淆最為複雜，因為字串具有很明確的意義，比如名字欄位、公司名欄位等，如果隨機的產生字元將沒有任何意義。這時可以考慮將字串拆分成兩部分然後進行交叉組合，用隨機的交叉組合來代替真是的資料。比如原來的姓名是：李宇春、曾軼可、劉著，經過交叉組合就會形成：李著、曾宇春、劉軼可之類的組合。

姓名的拆分是分為姓和名，而公司的拆分可以拆分成前2個字和後面的字。如果是英文姓名或者英文公司名則可以按照第一個空格將英文字串拆分成第一個單詞和後面的單詞。然後將產生的兩個欄位存入暫存資料表，用兩個暫存資料表進行交叉聯結，得到兩個欄位的所有組合，然後再隨機選出一定條數的資料，用選出的隨機資料將原有資料替換即可。

樣本

以一個HR系統為例。假設其中有一個Employee表，該表記錄了員工的工號、姓名等資訊，現在要對姓名進行處理，具體操作如下：

1.區分出中文名和英文名，分別進行拆分。中文姓名以第一個字為A列，剩下的字尾B列，英文名以第一個單詞為A列，剩下的單詞為B列，將拆分的資料存入暫存資料表，具體SQL語句如下：

select SUBSTRING(Name,1,1) A,SUBSTRING(Name,2,10) B into #CNamefrom Employeewhere UNICODE( Name)>255 --中文 select Name,SUBSTRING(Name,1,CHARINDEX(' ',Name,1)) A,SUBSTRING(Name,CHARINDEX(' ',Name,1),50) B into #ENamefrom Employeewhere UNICODE( Name)<255 --英文

2.讓A列和B列進行交叉聯結，得到姓名組合的全集，然後隨機選出與來源資料相同資料量的姓名存入暫存資料表（暫存資料表中有ID流水號欄位）。假設員工表裡有5000員工資料，則可以選取5000個隨機姓名，代碼如下：

create table #newCName(ID int identity primary key,Name nvarchar(50))
insert into #newCName(Name)
select top 5000 n1.A+n2.B
from #CName n1
cross join #CName n2
order by NEWID() --隨機選取行

3.由於Employee中沒有自增的ID欄位，只有字串形式的員工號作為主鍵，所以需要給每個員工號編一個流水號，用於和隨機姓名中的流水號對應，以便接下來的UPDATE操作：

create table #newEmployeeID(ID int identity primary key,EmployeeID varchar(10))
insert into #newEmployeeID
select EmployeeId
from Employee
where UNICODE( Name)>255 --中文

4.更新Employee表中的姓名欄位為隨機產生的姓名：

update Employee
set Name=n.Name
from Employee e
inner join #newEmployeeID i
on e.EmployeeId=i.EmployeeID
inner join #newCName n
on i.ID=n.ID
where UNICODE(e.Name)>255 --只更新中文姓名

5.用同樣的方法，可以對英文姓名進行混淆交叉替換。

最佳化

這裡需要注意的是第2步，使用了CROSS JOIN操作，也就是求兩個表的笛卡爾積，如果一個表中有10W條資料，那麼將會產生100億行結果，然後再進行排序，那將是近乎不可能完成的任務，所以必須減少進行笛卡爾積的表的資料量，比如每個表只取500條不重複的資料，那麼修改後的SQL語句是：

select top 5000 n1.A+n2.B
from
(select distinct top 500 A from #CName )n1 --取不重複的500個姓
cross join
(select distinct top 500 B from #CName ) n2--取不重複的500個名
order by NEWID() --隨機選取行

這樣最多隻是500*500條記錄，進行排序選取隨機行將會很快完成。