歡迎光臨
每天分享高質量文章

以賣香蕉為例,從4個方面瞭解SQL的資料彙總

導讀:面對一個新資料集時,人們往往會關心資料中的異常值、資料的分佈形式、行列之間的關係等。SQL是一種專為資料計算設計的語言,其中已經內置了許多資料彙總函式,也支持用戶編寫SQL命令實現更為複雜的彙總需求。


作者:Matt DeLand,Wagon聯合創始人與資料科學家

來源:Datartisan資料工匠(ID:shujugongjiang)

許多電腦使用Excel在面對上千行資料時已力不從心,而R較難部署在集群上運行,人眼顯然不可能直接從大量資料中總結出規律。如何才能快速理解你的資料集?SQL可以幫助你! 對資料進行統計彙總是能最快瞭解資料的方法。面對一個新資料集時,人們往往會關心資料中的異常值、資料的分佈形式、行列之間的關係等。SQL是一種專為資料計算設計的語言,其中已經內置了許多資料彙總函式,也支持用戶編寫SQL命令實現更為複雜的彙總需求。本文以香蕉銷售相關資料為例,從4個方面介紹如何用SQL進行資料彙總。 

01 基礎彙總

我們可以通過一段很短的SQL命令實現如計算個數(count)、去重(distinct)、求和(sum)、求平均(average)、求方差(variance)等彙總需求。假設我們有一個關於香蕉交易的資料表格,需要計算每天的顧客總數(num_customers)、去重顧客數(distinct_customers)、香蕉銷量(total_bananas)、總收入(total_revenue)和每筆平均收入(revenue_per_sale),可以通過以下命令實現:

得到的結果如下:

僅通過一次命令請求,我們就可以在非常大的資料集上計算出這些重要的彙總結果。如果再加上where或join命令,我們還可以高效地對資料進行切分。當然,有些需求並不能完全由一般的SQL函式實現。

02 計算分位數

如果資料的分佈存在較大的偏斜,平均值並不能告訴我們平均等待時間的分佈情況。因此我們往往需要知道資料的25%、50%、75%分位數是多少。

許多資料庫已經內建了分位數函式(包括Postgres 9.4、Redshift、SQL Server)。下麵的例子使用percentile_cont函式計算等待時間的分位數。該函式是一個視窗函式,可以按天進行分組計算。

計算結果如下:

其他視窗函式的結構和percentile_cont函式類似,我們可以指定對資料如何排序、如何分組。如果我們想要增加更多分組維度(如具體時間段),只需要將它們添加到partition和group by子句中。對於不支持percentile_cont的資料庫,命令會更複雜一些,但仍然可以實現。主要問題是如何將每天的訂單各自按等待時間遞增的順序排序,然後取出其中位數值。在MySQL中我們可以使用區域性變數來跟蹤訂單,在Postgres中,我們可以使用row_number函式:

計算結果如下:

03 直方圖

直方圖是大致瞭解資料分佈的好方法。我們可以用以下命令來計算每筆交易收入的分佈:

由於每個不同的收入都會占用一行,以上命令的結果行數將會非常多。我們需要將收入值分組以方便我們得到資料分佈的大致印象,比如分為$0-$5、$5-$10等組。如何分組並沒有一個標準的做法,需要我們自己根據需要,進行實驗來選擇。組別過多和過少都不合理,一般使用20個左右的組即可,也可以指定分組的寬度,分組越寬,分組數就越少。以下是指定分組寬度的例子:

計算結果如下:

這個命令將每個收入資料值向下取整到5的倍數並以此分組,即分組寬度為5。這種方法有個缺點,當某個區間內沒有記錄(比如在55-60美元之間沒有人購買),那麼結果中將不會有這個組別,這也可以通過編寫更複雜的SQL陳述句來解決。如果我們想要自行選擇區間的大小,首先需要計算資料的最大值和最小值,以便我們瞭解需要設定多少個區間。我們還可以用以下命令來使得每個區間有一個好看的標簽:

得到的結果如下:

04 聯合分佈

比較兩個不同的指標也是總結資料時的重要步驟。比如我們可能關心等待時間太久的人,最終是否會花費較少的錢。為了得到等待時間和收入之間的大致關係,我們可以使用以下命令:

得到的結果如下:

我們可能也關心諸如協方差、方差這類統計指標。大多數SQL實現已經內建了這些統計函式,比如在Postgres或Redshift中我們可以使用以下命令:

Postgres中內建了諸多彙總函式,甚至包括線性回歸。

原文:http://blog.yhat.com/posts/summarizing-data-in-SQL.html

推薦閱讀


全球100款大資料工具彙總(前50款)

大資料時代的10個重大變革

日本老爺爺堅持17年用Excel作畫,我可能用了假的Excel···

為什麼要學數學?因為這是一場戰略性的投資

Q: 好了,現在你的香蕉賣出去了嗎?

歡迎留言與大家分享

覺得不錯,請把這篇文章分享給你的朋友

轉載 / 投稿請聯繫:[email protected]

更多精彩文章,請在公眾號後臺點擊“歷史文章”查看

赞(0)

分享創造快樂