mysql中的group by用法詳解

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

一、基本語法與核心功能

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

核心功能：

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

二、基礎(chǔ)用法示例

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

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

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

2. 多列組合分組

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

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

3. 與WHERE結(jié)合使用

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

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

三、高級(jí)特性與擴(kuò)展

1. HAVING子句過濾分組

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

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

2. WITH ROLLUP生成匯總行

生成部門及職位的薪資小計(jì)和總計(jì)：

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

3. GROUP_CONCAT合并列值

統(tǒng)計(jì)每個(gè)用戶購買的所有產(chǎn)品（逗號(hào)分隔）：

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

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

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

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

四、注意事項(xiàng)與常見錯(cuò)誤

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

-- 錯(cuò)誤示例（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ù)，必須與聚合條件結(jié)合。

性能優(yōu)化建議

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

五、經(jīng)典案例場景

1. 按時(shí)間維度聚合

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

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

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

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

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

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

查找重復(fù)郵箱的用戶：

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

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

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

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

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

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

利用函數(shù)索引應(yīng)對(duì)復(fù)雜分組
• 對(duì)含表達(dá)式的分組（如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、查詢?cè)O(shè)計(jì)與執(zhí)行優(yōu)化

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

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

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

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

3、高級(jí)優(yōu)化技術(shù)

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

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

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

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

4、診斷工具與注意事項(xiàng)

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

• 嚴(yán)格模式規(guī)避錯(cuò)誤
啟用ONLY_FULL_GROUP_BY模式，防止非聚合列誤用導(dǎo)致結(jié)果不穩(wěn)定。

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

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

七、擴(kuò)展知識(shí)

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

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

補(bǔ)充：mysql中between的用法

mysql中between的用法

between的介紹

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

between的語法

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

expr [NOT] BETWEEN begin_expr AND end_expr;

在整個(gè)表達(dá)式中，expr表示的是一個(gè)單一的屬性或者是一個(gè)計(jì)算的表達(dá)式，整個(gè)表達(dá)式中的三個(gè)參數(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 (暫未驗(yàn)證)

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 '目標(biāo)收入',
  `create_time` datetime NOT NULL COMMENT '創(chuàng)建時(shí)間',
  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 這個(gè)日期范圍內(nèi)的數(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的總結(jié)

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

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

最新評(píng)論