SQL 聚合查詢

黃子毅發表於2022-03-08
SQL

SQL 為什麼要支援聚合查詢呢？

這看上去是個幼稚的問題，但我們還是一步步思考一下。資料以行為粒度儲存，最簡單的 SQL 語句是 select * from test，拿到的是整個二維表明細，但僅做到這一點遠遠不夠，出於以下兩個目的，需要 SQL 提供聚合函式：

  1. 明細資料沒有統計意義，比如我想知道今天的營業額一共有多少，而不太關心某桌客人消費了多少。
  2. 雖然可以先把資料查到記憶體中再聚合，但在資料量非常大的情況下很容易把記憶體撐爆，可能一張表一天的資料量就有 10TB，而 10TB 資料就算能讀到記憶體裡，聚合計算可能也會慢到難以接受。

另外聚合本身也有一定邏輯複雜度，而 SQL 提供了聚合函式與分組聚合能力，可以方便快速的統計出有業務價值的聚合資料，這奠定了 SQL 語言的分析價值，因此大部分分析軟體直接採用 SQL 作為直接面向使用者的表示式。

聚合函式

常見的聚合函式有：

  • COUNT：計數。
  • SUM：求和。
  • AVG：求平均值。
  • MAX：求最大值。
  • MIN：求最小值。

COUNT

COUNT 用來計算有多少條資料，比如我們看 id 這一列有多少條：

SELECT COUNT(id) FROM test

但我們發現其實查任何一列的 COUNT 都是一樣的，那傳入 id 有什麼意義呢？沒必要特殊找一個具體列指代呀，所以也可以寫成：

SELECT COUNT(*) FROM test

但這兩者存在微妙差異。SQL 存在一種很特殊的值型別 NULL，如果 COUNT 指定了具體列，則統計時會跳過此列值為 NULL 的行，而 COUNT(*) 由於未指定具體列，所以就算包含了 NULL，甚至某一行所有列都為 NULL，也都會包含進來。所以 COUNT(*) 查出的結果一定大於等於 COUNT(c1)。

當然任何聚合函式都可以跟隨查詢條件 WHERE，比如：

SELECT COUNT(*) FROM test
WHERE is_gray = 1

SUM

SUM 求和所有項，因此必須作用於數值欄位，而不能用於字串。

SELECT SUM(cost) FROM test

SUM 遇到 NULL 值時當 0 處理，因為這等價於忽略。

AVG

AVG 求所有項均值，因此必須作用於數值欄位，而不能用於字串。

SELECT AVG(cost) FROM test

AVG 遇到 NULL 值時採用了最徹底的忽略方式，即 NULL 完全不參與分子與分母的計算，就像這一行資料不存在一樣。

MAX、MIN

MAX、MIN 分別求最大與最小值，上面不同的時，也可以作用於字串上，因此可以根據字母判斷大小，從大到小依次對應 a-z，但即便能算，也沒有實際意義且不好理解，因此不建議對字串求極值。

SELECT MAX(cost) FROM test

多個聚合欄位

雖然都是聚合函式，但 MAX、MIN 嚴格意義上不算是聚合函式，因為它們只是尋找了滿足條件的行。可以看看下面兩段查詢結果的對比：

SELECT MAX(cost), id FROM test -- id: 100
SELECT SUM(cost), id FROM test -- id: 1

第一條查詢可以找到最大值那一行的 id，而第二條查詢的 id 是無意義的，因為不知道歸屬在哪一行，所以只返回了第一條資料的 id。

當然，如果同時計算 MAX、MIN，那麼此時 id 也只返回第一條資料的值，因為這個查詢結果對應了複數行：

SELECT MAX(cost), MIN(cost), id FROM test -- id: 1

基於這些特性，最好不要混用聚合與非聚合，也就是一條查詢一旦有一個欄位是聚合的，那麼所有欄位都要聚合。

現在很多 BI 引擎的自定義欄位都有這條限制，因為混用聚合與非聚合在自定義記憶體計算時處理起來邊界情況很多，雖然 SQL 能支援，但業務自定義的函式可能不支援。

分組聚合

分組聚合就是 GROUP BY，其實可以把它當作一種高階的條件語句。

舉個例子，查詢每個國家的 GDP 總量：

SELECT COUNT(GDP) FROM amazing_table
GROUP BY country

返回的結果就會按照國家進行分組，這時，聚合函式就變成了在組內聚合。

其實如果我們只想看中、美的 GDP，用非分組也可以查，只是要分成兩條 SQL：

SELECT COUNT(GDP) FROM amazing_table
WHERE country = '中國'

SELECT COUNT(GDP) FROM amazing_table
WHERE country = '美國'

所以 GROUP BY 也可理解為，將某個欄位的所有可列舉的情況都查了出來，並整合成一張表，每一行代表了一種列舉情況，不需要分解為一個個 WHERE 查詢了。

多欄位分組聚合

GROUP BY 可以對多個維度使用，含義等價於表格查詢時行/列拖入多個維度。

上面是 BI 查詢工具視角，如果沒有上下文，可以看下面這個遞進描述：

  • 按照多個欄位進行分組聚合。
  • 多欄位組合起來成為唯一 Key，即 GROUP BY a,b 表示 a,b 合在一起描述一個組。
  • GROUP BY a,b,c 查詢結果第一列可能看到許多重複的 a 行，第二列看到重複 b 行，但在同一個 a 值內不會重複，c 在 b 行中同理。

下面是一個例子：

SELECT SUM(GDP) FROM amazing_table
GROUP BY province, city, area

查詢結果為：

浙江 杭州 餘杭區
浙江 杭州 西湖區
浙江 寧波 海曙區
浙江 寧波 江北區
北京 .........

GROUP BY + WHERE

WHERE 是根據行進行條件篩選的。因此 GROUP BY + WHERE 並不是在組內做篩選，而是對整體做篩選。

但由於按行篩選，其實組內或非組內結果都完全一樣，所以我們幾乎無法感知這種差異：

SELECT SUM(GDP) FROM amazing_table
GROUP BY province, city, area
WHERE industry = 'internet'

然而，忽略這個差異會導致我們在聚合篩選時碰壁。

比如要篩選出平均分大於 60 學生的成績總和，如果不使用子查詢，是無法在普通查詢中在 WHERE 加聚合函式實現的，比如下面就是一個語法錯誤的例子：

SELECT SUM(score) FROM amazing_table
WHERE AVG(score) > 60

不要幻想上面的 SQL 可以執行成功，不要在 WHERE 裡使用聚合函式。

GROUP BY + HAVING

HAVING 是根據組進行條件篩選的。因此可以在 HAVING 使用聚合函式：

SELECT SUM(score) FROM amazing_table
GROUP BY class_name
HAVING AVG(score) > 60

上面的例子中可以正常查詢，表示按照班級分組看總分，且僅篩選出平均分大於 60 的班級。

所以為什麼 HAVING 可以使用聚合條件呢？因為 HAVING 篩選的是組，所以可以對組聚合後過濾掉不滿足條件的組，這樣是有意義的。而 WHERE 是針對行粒度的，聚合後全表就只有一條資料，無論過濾與否都沒有意義。

但要注意的是，GROUP BY 生成派生表是無法利用索引篩選的，所以 WHERE 可以利用給欄位建立索引優化效能，而 HAVING 針對索引欄位不起作用。

總結

聚合函式 + 分組可以實現大部分簡單 SQL 需求，在寫 SQL 表示式時，需要思考這樣的表示式是如何計算的，比如 MAX(c1), c2 是合理的，而 SUM(c1), c2 這個 c2 就是無意義的。

最後記住 WHERE 是 GROUP BY 之前執行的，HAVING 針對組進行篩選。

討論地址是：精讀《SQL 聚合查詢》· Issue #401 · ascoders/weekly

如果你想參與討論，請 點選這裡，每週都有新的主題，週末或週一釋出。前端精讀 - 幫你篩選靠譜的內容。

關注 前端精讀微信公眾號

<img width=200 src="https://img.alicdn.com/tfs/TB165W0MCzqK1RjSZFLXXcn2XXa-258-258.jpg">

版權宣告：自由轉載-非商用-非衍生-保持署名（創意共享 3.0 許可證）

相關文章