萬盛學電腦網

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

SQL GROUP BY 分組聚合完全指南:語法、多列、ROLLUP、性能優化

將行數據按維度聚合為指標的核心語句

GROUP BY 將滿足條件的行按指定列分桶,聚合函數(SUM/COUNT/MAX/MIN/AVG/GROUP_CONCAT)在每桶內計算單一值。本文覆蓋基礎語法、多列分組、超級聚合、HAVING 篩選、索引優化、大數據近似計算。

標準語法

SELECT col1, col2, aggregate_func(expr)
FROM table
[WHERE condition]
GROUP BY col1, col2
[HAVING aggregate_condition]
[ORDER BY ...] [LIMIT ...];

基礎實例

-- 各項目總價格
SELECT Item, SUM(Price) AS TotalSum FROM Antiques GROUP BY Item;

-- 各項目最高價
SELECT Item, MAX(Price) AS MaxPrice FROM Antiques GROUP BY Item;

-- 買家 15 的賣家統計
SELECT SellerID, COUNT(*) AS Number_of_Sellers
FROM Antiques
WHERE BuyerID = 15
GROUP BY SellerID;

多列分組(層級聚合)

SELECT region, country, SUM(population)
FROM bbc
GROUP BY region, country;  -- 先按 region,再按 country 細分

ROLLUP / CUBE 超級聚合(MySQL 支援 ROLLUP)

SELECT region, country, SUM(population)
FROM bbc
GROUP BY region, country WITH ROLLUP;
-- 額外輸出:
-- region 小計行(country=NULL)
-- 總計行(region=NULL, country=NULL)

HAVING 篩選分組結果

SELECT store_name, SUM(sales) AS total
FROM Store_Information
GROUP BY store_name
HAVING SUM(sales) > 1500;  -- 僅保留總銷售額 > 1500 的店

索引優化鐵律

場景推薦索引原理
單列 GROUP BYINDEX (group_col)索引掃描天然有序,避免臨時表排序
多列 GROUP BYINDEX (col1, col2, ...)最左前綴匹配分組順序
WHERE + GROUP BYINDEX (where_col, group_col)先過濾再分組,覆蓋索引最優
GROUP BY + ORDER BY同分組索引MySQL 8.0+ 可消除排序

大數據近似聚合(MySQL 8.0.11+)

-- 近似去重計數,誤差 < 1%,毫秒級
SELECT APPROX_COUNT_DISTINCT(user_id) FROM huge_logs;

-- 近似分位數
SELECT APPROX_PERCENTILE(latency, 0.95) FROM api_logs;

常見陷阱

  1. ONLY_FULL_GROUP_BY:默認開啟,SELECT 非聚合列必須在 GROUP BY 中,否則報錯
  2. NULL 參與分組:所有 NULL 值歸為一組
  3. 隱式排序:GROUP BY 默認按分組列排序,不需要順序加 ORDER BY NULL 抑制
  4. 聚合函數嵌套:SUM(COUNT(*)) 不合法,需子查詢

總結

GROUP BY 分桶,聚合算指標,HAVING 篩桶,索引支撐序。
多列遵循最左,ROLLUP 見總計,大數據近似算。
ONLY_FULL_GROUP_BY 別關閉,規範寫法最省心。

copyright © 萬盛學電腦網 all rights reserved