以賣香蕉為例，從4個方面瞭解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