2.9 隨機抽樣

在審計或IT審計中，常常使用抽樣，也就是對具有審計相關性的總體中低于百分之百的項目實施審計程序，使所有抽樣單元都有被選取的機會，為注冊會計師針對整個總體得出結論提供合理基礎。簡單來說，就是我們需要從總體中選出一些樣本，通過對樣本的檢查，得出結論，再推導出總體的結論。

在Excel中或一些軟件中，我們可以非常容易在數據中抽樣，但當數據量超過Excel表最大限制時或數據很大時，我們需要在數據庫中處理，那么如何用SQL抽樣，就是我們應該掌握的。本節我們介紹隨機抽樣的三種方法，數據仍采用2.6節中APP數據集里AppleStore表作為講解示例。首先，我們通過select count(*) from AppleStore求出表中有7142條數據，假設我們需要隨機抽樣出100條數據。

「方法一」

select * from AppleStoreorder by rand()limit 100;

解釋：rand()函數會產生0-1之間的隨機數，order by rand()根據產生的隨機數進行排序。limit 100截取前100行數據。從而達到隨機抽樣100條數據的目的。當然如果需要抽樣出N條數據就使用limit N。即：

select * from 表名order by rand()limit N;

需要注意的是，雖然這是最簡單的方法，并且在數據較小的表執行速度較快，但是當數據較大的時候，查詢速度就會較慢。

「方法二」

我們先計算我們需要抽樣數據與總體的占比100/7142=0.014,然后通過查詢語句隨機抽樣出上述比例的數據：

select * from AppleStorewhere rand()<0.014

解釋：對于每一行數據，都會執行判斷條件where rand()<0.014，而rand()是產生0-1的隨機函數，那么每條數據都有0.014的機率被篩選出來，最終會篩選出總體×0.014條數據。當然實際篩選出的數據條數不是固定的，它隨著數據量越大越接近0.014這個比例。我們為了抽取出剛好100條數據，我們可以稍微提高抽取的比例，然后使用limit 100截取前100條數據：

select * from AppleStorewhere rand()<0.015limit 100;

「方法三」

本方法需要表中有一列是連續編號的數字，一般的表中id或序號都是連續編號的，我們可以直接使用。如果沒有連續編號的列，那么我們需要人為的創建一列序號數據。在AppleStore中無論是id還是序號都是不連續的，所以我們首先新增一列，列名為‘num’。

新增列語法：

alter table 表名 add column 列名 數據類型;

比如，給AppleStore新增一列num,數據類型為int：

alter table AppleStore add column num int;

刪除列語法：

alter table 表名 drop column 列名;

修改列名語法：

alter table 表名 change 原列名 新列名 數據類型;

以上是對列操作的語句用法，下面我們給創建的num列添加連續的序號：

set @rn=0;update AppleStore set num=(@rn:=@rn+1);

解釋：在SQL中命名一個變量需要在前面加一個@符號，這里我們命名一個代表行數的變量@rn(row number)。update 表名 set 列名=值 where 列名=某值是修改值的語法。這里我們用num=(@rn:=@rn+1)使列num的值遞增增加。

需要注意的是在使用update語句里，可能會報錯：Error Code 1175 You are using safe update mode。也就是如果沒有加where限制條件更新值是不允許的。這里我們關閉安全更新模式：

SET SQL_SAFE_UPDATES = 0;

通過上述步驟，我們給原表增加了遞增的連續編號列num列。我們現在就可以根據num列進行隨機抽樣。

set @max=7142;set @min=1;select * from AppleStore a join (select floor(@min+(@max-@min+1)*rand()) as num from AppleStore limit 100) b on a.num=b.numlimit 100;

解釋：我們將num列的最大編號、最小編號分別命名為變量@max和@min。

select floor(@min+(@max-@min+1)*rand()) as numfrom AppleStorelimit 100

該子句的作用是隨機生成100個num序號。其中floor()函數是對一個數向下取整，如floor(1.2)=1，這里采用向下取整，而不使用四舍五入的原因是防止抽樣出的數據超過序號的最大值。該子句查詢結果，我們重命名為表b，將表a,表b通過序號內連接，查找出b表中隨機抽樣出的100個序號所對應的數據。可以看出我們對序號的要求并不需要從1開始，它可以從任意值開始，只要序號是連續的。本方法在數據量大的情況下也有較快的查詢速度。

上面我們添加num列是直接在原表上添加列并生成遞增連續編號，很多時候修改原數據表是不妥的，可以使用with as將新增num列后的查詢表結果作為可以被引用的片斷。

select *,row_number() over (order by id) as numfrom AppleStore

row_number() over (order by id) as num，row_number函數將原表數據按id排序后生成序號，重命名為num。該查詢語句實際就是在原AppleStore表基礎上增加了列num，只是序號只顯示在查詢結果中，而不是對原表進行修改。我們利用with as將AppleStore用增加的num列來替換。

set @max=7142;set @min=1;with AppleStore as (select *,row_number() over (order by id) as num from AppleStore)select * from AppleStore a join (select floor(@min+(@max-@min+1)*rand()) as num from AppleStore limit 100) b on a.num=b.numlimit 100;

青藤數據-審計師自己的平臺

總結

以上是生活随笔為你收集整理的sql随机抽取数据50条_MySQL中随机抽样的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。