mysql中的group by用法詳解

MySQL中的GROUP BY是數(shù)據(jù)聚合分析的核心功能，主要用于將結果集按指定列分組，并結合聚合函數(shù)進行統(tǒng)計計算。以下從基本語法到高級用法進行詳細解析：

一、基本語法與核心功能

SELECT 分組列, 聚合函數(shù)(計算列)
FROM 表名
[WHERE 條件]
GROUP BY 分組列
[HAVING 分組過濾條件]
[ORDER BY 排序列];

核心功能：

• 數(shù)據(jù)分組：按一列或多列的值將數(shù)據(jù)劃分為邏輯組。
• 聚合計算：對每個分組應用聚合函數(shù)（如COUNT、SUM、AVG、MAX、MIN）進行統(tǒng)計。
• 結果過濾：通過HAVING對分組后的結果進行篩選（區(qū)別于WHERE的分組前過濾）。

二、基礎用法示例

1. 單列分組統(tǒng)計

統(tǒng)計每個部門的員工數(shù)量和平均工資：

SELECT department, COUNT(*) AS emp_count, AVG(salary) AS avg_salary
FROM employees
GROUP BY department;  -- 

2. 多列組合分組

按部門和職位統(tǒng)計員工數(shù)量：

SELECT department, job_title, COUNT(*) 
FROM employees
GROUP BY department, job_title;  -- 

3. 與WHERE結合使用

僅統(tǒng)計薪資超過2000元的員工部門平均工資：

SELECT department, AVG(salary)
FROM employees
WHERE salary > 2000
GROUP BY department;  -- 

三、高級特性與擴展

1. HAVING子句過濾分組

篩選員工數(shù)量超過5人的部門：

SELECT department, COUNT(*) AS emp_count
FROM employees
GROUP BY department
HAVING emp_count > 5;  -- 

2. WITH ROLLUP生成匯總行

生成部門及職位的薪資小計和總計：

SELECT department, job_title, SUM(salary)
FROM employees
GROUP BY department, job_title WITH ROLLUP;  -- 

3. GROUP_CONCAT合并列值

統(tǒng)計每個用戶購買的所有產品（逗號分隔）：

SELECT user_id, GROUP_CONCAT(product_name SEPARATOR ', ') 
FROM orders
GROUP BY user_id;  -- 

4. 按表達式/函數(shù)分組

按年份統(tǒng)計訂單數(shù)量：

SELECT YEAR(order_date) AS year, COUNT(*)
FROM orders
GROUP BY YEAR(order_date);  -- 

四、注意事項與常見錯誤

ONLY_FULL_GROUP_BY模式
MySQL 8.0+默認啟用該模式，要求SELECT中的非聚合列必須出現(xiàn)在GROUP BY中，否則報錯。

-- 錯誤示例（salary未聚合且未分組）
SELECT department, salary FROM employees GROUP BY department;
-- 修正方法：添加聚合函數(shù)或分組字段
SELECT department, MAX(salary) FROM employees GROUP BY department;

WHERE與HAVING的區(qū)別

• WHERE在分組前過濾行數(shù)據(jù)，不可使用聚合函數(shù)。
• HAVING在分組后過濾組數(shù)據(jù)，必須與聚合條件結合。

性能優(yōu)化建議

• 在分組列上創(chuàng)建索引（如ALTER TABLE employees ADD INDEX(department)）。
• 避免對大表直接分組，可先通過臨時表或子查詢縮小數(shù)據(jù)范圍。

五、經典案例場景

1. 按時間維度聚合

統(tǒng)計每月的銷售總額：

SELECT YEAR(sale_date) AS year, MONTH(sale_date) AS month, SUM(amount)
FROM sales
GROUP BY year, month;  -- 

2. 多層級統(tǒng)計

分析每個客戶每年的訂單總金額及平均金額：

SELECT customer_id, YEAR(order_date), 
       SUM(total_amount), AVG(total_amount)
FROM orders
GROUP BY customer_id, YEAR(order_date);  -- 

3. 數(shù)據(jù)去重

查找重復郵箱的用戶：

SELECT email, COUNT(*) 
FROM users 
GROUP BY email 
HAVING COUNT(*) > 1;  -- 

六、聚合效率優(yōu)化

在MySQL中優(yōu)化GROUP BY聚合效率需要從索引設計、查詢邏輯、執(zhí)行引擎特性等多維度入手。以下基于最新優(yōu)化實踐和數(shù)據(jù)庫引擎特性，總結9大核心優(yōu)化策略：

1、索引優(yōu)化策略

復合索引精準匹配分組列
• 創(chuàng)建與GROUP BY順序完全匹配的復合索引（如GROUP BY a,b則創(chuàng)建(a,b)索引），可觸發(fā)松散索引掃描，減少90%以上的磁盤I/O。
• 典型案例：當對(department, job_title)分組時，復合索引idx_dept_job可使查詢跳過全表掃描，直接通過索引完成分組。

覆蓋索引避免回表
• 確保SELECT列與聚合函數(shù)涉及的列均包含在索引中。例如索引(category, sales)，查詢SELECT category, SUM(sales)時可直接通過索引完成計算，無需訪問數(shù)據(jù)行。

利用函數(shù)索引應對復雜分組
• 對含表達式的分組（如YEAR(date_col)），創(chuàng)建虛擬列或函數(shù)索引（MySQL 8.0+支持）。例如：

ALTER TABLE orders ADD COLUMN year_date INT AS (YEAR(order_date)) VIRTUAL;
CREATE INDEX idx_year ON orders(year_date);

2、查詢設計與執(zhí)行優(yōu)化

減少分組字段數(shù)量與復雜度
• 每增加一個分組字段，排序復雜度呈指數(shù)級增長。優(yōu)先合并相關字段（如將province和city合并為region字段）。
• 避免在GROUP BY中使用函數(shù)，否則索引失效。需改寫為基于原字段分組，如將GROUP BY DATE(created_at)改為GROUP BY created_at_date預計算列。

分階段過濾與聚合
• 先通過子查詢過濾無關數(shù)據(jù)再分組：

SELECT department, AVG(salary) 
FROM (SELECT * FROM employees WHERE salary > 5000) AS filtered 
GROUP BY department;  -- 比直接HAVING效率提升40%

內存排序與臨時表優(yōu)化
• 調整tmp_table_size和max_heap_table_size參數(shù)（建議設置為物理內存的20%），避免臨時表落盤。
• 監(jiān)控Created_tmp_disk_tables狀態(tài)變量，若頻繁出現(xiàn)磁盤臨時表，需優(yōu)化索引或拆分查詢。

3、高級優(yōu)化技術

分區(qū)表加速大數(shù)據(jù)處理
• 按時間或業(yè)務維度分區(qū)（如按月分區(qū)），使GROUP BY僅掃描特定分區(qū)。例如對10億級日志表按event_date分區(qū)后，月度統(tǒng)計耗時從分鐘級降至秒級。

物化視圖與結果緩存
• 對高頻聚合查詢使用物化視圖（如通過CREATE TABLE mv AS SELECT...定期刷新），減少實時計算壓力。
• 應用層緩存重復查詢結果（如Redis緩存日匯總數(shù)據(jù)），降低數(shù)據(jù)庫負載。

并行查詢（MySQL 8.0+）
• 啟用parallel_query功能，通過多線程處理復雜分組：

SET SESSION optimizer_switch='parallel_query=on';
SELECT region, SUM(revenue) FROM sales GROUP BY region;  -- 利用多核CPU加速

4、診斷工具與注意事項

• 執(zhí)行計劃分析
使用EXPLAIN FORMAT=JSON觀察using_index（是否用索引）、using_temporary（是否用臨時表）、filesort（排序方式）等關鍵指標。

• 嚴格模式規(guī)避錯誤
啟用ONLY_FULL_GROUP_BY模式，防止非聚合列誤用導致結果不穩(wěn)定。

性能優(yōu)化對比案例

通過上述策略組合，可系統(tǒng)性解決GROUP BY性能瓶頸。實際應用中建議結合EXPLAIN分析和A/B測試，選擇最適合業(yè)務場景的優(yōu)化方案。

七、擴展知識

• NULL值的處理：GROUP BY將NULL視為獨立分組。
• 排序結合：分組后使用ORDER BY對結果排序（如按平均工資降序）。
• 動態(tài)分組：通過CASE WHEN實現(xiàn)條件分組（如按薪資區(qū)間統(tǒng)計）。

通過靈活組合這些功能，GROUP BY可滿足復雜的數(shù)據(jù)分析需求。實際應用中需結合索引優(yōu)化和查詢邏輯設計，以提升執(zhí)行效率。

補充：mysql中between的用法

mysql中between的用法

between的介紹

日常sql查詢過程中經常要篩選某個屬性或某個表達式結果的某個范圍內的數(shù)據(jù)，這個時候我們經常通過 > 或者 < 來進行篩選，有的時候再項目中由于 > 和 < 經常會和起始標志符沖突，所以需要進行轉義，這個過程很容易出現(xiàn)一些問題，其實在sql的關鍵字中，有一個非常實用的關鍵字可以進行范圍查詢，這個關鍵字就是between，接下來我們就來深入的了解一下between的用法。

between的語法

between關鍵字是一個邏輯操作符用來篩選指定屬性或表達式某一范圍內或范圍外的數(shù)據(jù)。between關鍵字常用在where關鍵字后與select或update或delete共同使用。between的使用語法如下：

expr [NOT] BETWEEN begin_expr AND end_expr;

在整個表達式中，expr表示的是一個單一的屬性或者是一個計算的表達式，整個表達式中的三個參數(shù) expr、begin_expr、end_expr 必須是同一種數(shù)據(jù)類型。

• between篩選的是 expr >= begin_expr并且 expr <= end_expr 的數(shù)據(jù)，如果不存在則返回的是0；
• not between篩選的是 expr < begin_expr或者 expr > end_expr 的數(shù)據(jù)，如果不存在則返回的是0；
• 如果 expr 返回的是 NULL，則between 也返回的是null (暫未驗證)

between的用法

假如我們有一張數(shù)據(jù)庫表如下所示

CREATE TABLE `t_income` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '唯一自增id',
  `income_date` varchar(255) NOT NULL COMMENT '收入年月',
  `amount` float NOT NULL COMMENT '收入金額',
  `target_amount` float NOT NULL DEFAULT '0' COMMENT '目標收入',
  `create_time` datetime NOT NULL COMMENT '創(chuàng)建時間',
  PRIMARY KEY (`id`)  ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

• 查詢表中amount>=10并且amount<=50的數(shù)據(jù)

select * from t_income where amount between 10 and 50;

• 查詢表中amount 和 target_amount 總和 >=100并且<=500的數(shù)據(jù)

select * from t_income where (amount + target_amount) between 100 and 500;

• 查詢表中create_time 在 2019-01-01 到 2019-09-01 這個日期范圍內的數(shù)據(jù)

select * from t_income where create_time between cast('2019-01-01' as DATE) and cast('2019-09-01' as DATE);

• 查詢表中amount < 10 或者 amount > 50 的數(shù)據(jù)

select * from t_income where amount not between 10 and 50;

between的總結

通過上面的講解，我們現(xiàn)在應該已經基本的學會了between的用法，但是如果在開發(fā)中，我們要查詢某個屬性大于某一個值 并且小于某個值的話，我們就只能用 > and < 啦

到此這篇關于mysql中的group by高級用法詳解的文章就介紹到這了,更多相關mysql group by用法內容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持腳本之家！

最新評論