萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MySQL 教學

MySQL SUM() 聚合函數入門到進階:分組統計與實戰範例

函數定義

SUM(expr) 返回指定數值列的總和,忽略 NULL 值。若無匹配行則返回 NULL。

基礎範例:employee_tbl 範例表

+----+------+------------+--------------------+
| id | name | work_date  | daily_typing_pages |
+----+------+------------+--------------------+
| 1  | John | 2007-01-24 | 250                |
| 2  | Ram  | 2007-05-27 | 220                |
| 3  | Jack | 2007-05-06 | 170                |
| 3  | Jack | 2007-04-06 | 100                |
| 4  | Jill | 2007-04-06 | 220                |
| 5  | Zara | 2007-06-06 | 300                |
| 5  | Zara | 2007-02-06 | 350                |
+----+------+------------+--------------------+

全表求和

SELECT SUM(daily_typing_pages) FROM employee_tbl;
-- 結果:1610

分組統計:GROUP BY 搭配 SUM

SELECT name, SUM(daily_typing_pages) AS total_pages
FROM employee_tbl
GROUP BY name;
+------+--------------+
| name | total_pages  |
+------+--------------+
| Jack | 270          |
| Jill | 220          |
| John | 250          |
| Ram  | 220          |
| Zara | 650          |
+------+--------------+

進階用法

條件聚合:CASE WHEN 搭配 SUM

SELECT name,
       SUM(CASE WHEN work_date >= '2007-05-01' THEN daily_typing_pages ELSE 0 END) AS may_jun_pages
FROM employee_tbl
GROUP BY name;

多欄位同時聚合

SELECT name,
       SUM(daily_typing_pages) AS total_pages,
       COUNT(*) AS record_cnt,
       AVG(daily_typing_pages) AS avg_pages
FROM employee_tbl
GROUP BY name;

窗口函數(MySQL 8.0+):累計求和

SELECT name, work_date, daily_typing_pages,
       SUM(daily_typing_pages) OVER (PARTITION BY name ORDER BY work_date) AS running_total
FROM employee_tbl;

常見陷阱與優化

  • NULL 值:SUM 自動忽略 NULL,但 SUM(COALESCE(col,0)) 寫法更明確。
  • 溢位:整數列求和可能超出類型範圍,建議 CAST(col AS BIGINT) 或 DECIMAL。
  • 索引利用:單純 SUM 無法用索引,但配合 WHERE 篩選可走索引減少掃描行。
  • 大表分組:考慮物化視圖/彙總表預計算,避免即時全表掃描。

總結

SUM() 是最基礎也最常用的聚合函數,配合 GROUP BY、CASE WHEN、窗口函數可覆蓋 90% 以上統計報表需求。掌握其 NULL 處理、類型溢位與索引配合細節,能寫出更健壯的查詢。

copyright © 萬盛學電腦網 all rights reserved