GROUP BY簡介

GROUP BY 子句是 在MySQL 中用于將查詢結果按照指定的列或表達式進行分組的關鍵字。它通常與聚合函數(shù)一起使用，能夠?qū)γ總€分組進行統(tǒng)計或計算，并在需要時進行篩選，是處理數(shù)據(jù)庫中大量數(shù)據(jù)并生成匯總報表的重要工具。

常用的聚合函數(shù)有：count() 計數(shù), sum() 求和 , avg() 求平均值, max() 求最大值, min()求最小值。

基本用法

我們拿一張學生表舉例

創(chuàng)建表：

CREATE TABLE students (
        student_id INT AUTO_INCREMENT PRIMARY KEY, -- 學生ID，自增主鍵
        name VARCHAR(50), -- 學生姓名
        major VARCHAR(50), -- 專業(yè)
        grade VARCHAR(10), -- 年級
        age INT, -- 年齡
        admission_year datetime -- 入學日期
)

插入數(shù)據(jù)：

INSERT INTO students (name, major, grade, age, admission_year) VALUES
('張三', '計算機科學', '大一', 18, '2024-01-01 00:00:00'),
('李四', '數(shù)學', '大一', 19, '2024-01-01 00:00:00'),
('王五', '物理', '大二', 20, '2023-01-01 00:00:00'),
('趙六', '化學', '大二', 19, '2023-01-01 00:00:00'),
('小明', '生物', '大三', 21, '2022-01-01 00:00:00'),
('小紅', '歷史', '大三', 22, '2022-01-01 00:00:00'),
('小李', '地理', '大四', 23, '2021-01-01 00:00:00'),
('小張', '經(jīng)濟學', '大四', 22, '2021-01-01 00:00:00'),
('小王', '文學', '大一', 18, '2024-01-01 00:00:00'),
('小劉', '計算機科學', '大一', 19, '2024-01-01 00:00:00'),
('小楊', '數(shù)學', '大二', 20, '2023-01-01 00:00:00'),
('小白', '物理', '大二', 19, '2023-01-01 00:00:00'),
('小黑', '化學', '大三', 21, '2022-01-01 00:00:00'),
('小貓', '生物', '大三', 22, '2022-01-01 00:00:00'),
('小狗', '歷史', '大四', 23, '2021-01-01 00:00:00'),
('小鳥', '地理', '大四', 22, '2021-01-01 00:00:00'),
('小魚', '經(jīng)濟學', '大一', 18, '2024-01-01 00:00:00'),
('小蟲', '文學', '大一', 19, '2024-01-01 00:00:00'),
('小兔', '計算機科學', '大二', 20, '2023-01-01 00:00:00'),
('小雞', '數(shù)學', '大二', 19, '2023-01-01 00:00:00'),
('小鴨', '物理', '大三', 21, '2022-01-01 00:00:00'),
('小狐', '化學', '大三', 22, '2022-01-01 00:00:00'),
('小牛', '生物', '大四', 23, '2021-01-01 00:00:00'),
('小馬', '歷史', '大四', 22, '2021-01-01 00:00:00'),
('小羊', '地理', '大一', 18, '2024-01-01 00:00:00'),
('小豬', '經(jīng)濟學', '大一', 19, '2024-01-01 00:00:00'),
('小狗', '文學', '大二', 20, '2023-01-01 00:00:00'),
('小雞', '計算機科學', '大二', 19, '2023-01-01 00:00:00'),
('小鴨', '數(shù)學', '大三', 21, '2022-01-01 00:00:00'),
('小貓', '物理', '大三', 22, '2022-01-01 00:00:00'),
('小猴', '化學', '大四', 23, '2021-01-01 00:00:00'),
('小狗', '生物', '大四', 22, '2021-01-01 00:00:00'),
('小鳥', '歷史', '大一', 18, '2024-01-01 00:00:00'),
('小貓', '地理', '大一', 19, '2024-01-01 00:00:00'),
('小魚', '經(jīng)濟學', '大二', 20, '2023-01-01 00:00:00'),
('小蟲', '文學', '大二', 19, '2023-01-01 00:00:00');

單列分組

例如：按照年級對學生進行分組，并計算每個年級的學生數(shù)量。

SELECT grade, COUNT(*)
FROM students
GROUP BY grade;
 

多列分組

例如：按照年級和年齡對學生進行分組，并計算每個年級、年齡組合的學生數(shù)量。

SELECT grade, age, COUNT(*)
FROM students
GROUP BY grade, age;

使用聚合函數(shù) 

例如：計算每個年級的學生平均年齡

SELECT grade, AVG(age)
FROM students
GROUP BY grade;

過濾分組結果 

HAVING 子句在 GROUP BY 之后對分組進行過濾。它允許篩選哪些組將包含在結果中，類似于 WHERE 子句對行進行過濾。通常，HAVING 子句用于過濾聚合后的結果，根據(jù)某些條件選擇性地包括或排除分組。

例如：篩選出平均年齡超過 20 歲的年級

SELECT grade, AVG(age)
FROM students
GROUP BY grade
HAVING AVG(age) > 20;

按表達式分組

例如：按照入學年份（在 "admission_year" 列中）對學生進行分組，并計算每個入學年份的學生數(shù)量。

SELECT YEAR(admission_year), COUNT(*)
FROM students
GROUP BY YEAR(admission_year);

使用 GROUP BY 的排序 

例如：按照年級對學生進行分組，并按照每個年級的學生數(shù)量從高到低排序。

SELECT grade, COUNT(*)
FROM students
GROUP BY grade
ORDER BY COUNT(*) DESC;

注意事項

遵循原則

確保在SELECT子句中使用的列都包含在GROUP BY子句中，或者是聚合函數(shù)的參數(shù)。否則，查詢可能會產(chǎn)生錯誤的結果或語法錯誤。

換句話說group by 有一個原則,就是 select 后面的所有列中,沒有使用聚合函數(shù)的列,必須出現(xiàn)在 group by 后面

正例：

一共select了grade ,age ,student_id三列，只有student_id列使用了count聚合函數(shù)，grade ,age列沒用聚合函數(shù)就必須跟在group by 后面

SELECT grade, age, COUNT(student_id) as 學生數(shù)量
FROM students
GROUP BY grade, age;

查詢結果返回了年紀跟年齡的所有組合下的學生數(shù)量。

反例：

在 SELECT 子句中，除了 COUNT(student_id) 使用了聚合函數(shù)外，其余的兩列 grade 和 age 都沒有使用聚合函數(shù)。但是在 GROUP BY 子句中，只列出了 grade 列，而沒有包括 age 列。因此，這個查詢違反了該原則。

SELECT grade, age, COUNT(student_id) as 學生數(shù)量
FROM students
GROUP BY grade;

由于違背了group by的原則，age列沒有跟在group by后面導致只查詢了不同年級的學生數(shù)量統(tǒng)計，然而結果出現(xiàn)的age列僅僅是對應年級下第一個學生的年齡，這樣是沒有意義的，這樣的結果是混亂的。

使用能夠唯一標識每個分組的字段或字段組合

正例：

比如專業(yè)，年級。

反例：

唯一標識符字段：如果字段中的值對每個數(shù)據(jù)行都是唯一的，那么使用這樣的字段進行 GROUP BY 將會使每個分組中只有一行數(shù)據(jù)，且分組數(shù)量大。

包含大量不同值的字段：如果某個字段的取值范圍非常廣泛，例如一個具有高基數(shù)（cardinality）的字段，使用它進行 GROUP BY 可能會導致大量的小分組，從而使結果變得難以理解或者過于細粒度化。

文本字段：雖然您可以使用文本字段進行 GROUP BY，但是它可能會導致分組的數(shù)量龐大，并且對結果的解釋會變得更加困難。在這種情況下，最好先對文本字段進行分析或預處理，以便將其轉(zhuǎn)換為更具可分組性的特征。

包含 NULL 值的字段：如果一個字段大部分值都是 NULL，那么使用它進行 GROUP BY 可能會使得 NULL 值形成一個單獨的分組，而其他分組則非常少。

性能

GROUP BY操作可能會導致查詢的性能下降，特別是在處理大量數(shù)據(jù)時。確保索引和適當?shù)膬?yōu)化策略可以幫助提高查詢性能。

創(chuàng)建索引：為 GROUP BY 子句中的字段創(chuàng)建索引，這樣數(shù)據(jù)庫可以更快地定位并處理數(shù)據(jù)。如果您經(jīng)常使用某個字段進行 GROUP BY，考慮為該字段創(chuàng)建索引以加快查詢速度。

使用覆蓋索引：創(chuàng)建覆蓋索引以覆蓋 GROUP BY 查詢中涉及的所有字段。這樣可以避免數(shù)據(jù)庫執(zhí)行額外的查找操作，從而提高性能。

限制結果集：在 GROUP BY 子句之前使用 WHERE 子句過濾數(shù)據(jù)，以減少處理的數(shù)據(jù)量。只選擇必要的數(shù)據(jù)行可以顯著提高查詢性能。

使用聚合函數(shù)：考慮使用聚合函數(shù)（如SUM、COUNT、AVG等）來減少數(shù)據(jù)量。盡量在 GROUP BY 之前使用聚合函數(shù)，以便減少處理的數(shù)據(jù)量。

避免使用復雜表達式：在 GROUP BY 子句中盡量避免使用復雜的表達式或函數(shù)。這些表達式可能會增加處理時間，并使索引失效。

注意數(shù)據(jù)類型

在MySQL中，雖然可以在幾乎任何數(shù)據(jù)類型的列上使用GROUP BY子句，但某些數(shù)據(jù)類型可能在實際應用中帶來挑戰(zhàn)或性能問題。

適合分組的數(shù)據(jù)類型

• INT、BIGINT等整數(shù)類型：這些類型在進行分組和比較時比較可靠。
• VARCHAR、CHAR等字符類型：字符類型在比較和分組時更具可預測性。

不適合分組的數(shù)據(jù)類型

BLOB和TEXT：用于存儲大型文本或二進制數(shù)據(jù)，比較和分組時性能較差。

JSON：嵌套結構復雜，直接比較不可靠，導致分組性能問題。

GEOMETRY：用于存儲空間數(shù)據(jù)，比較復雜且計算量大，難以進行分組。

VARBINARY/BINARY：存儲二進制數(shù)據(jù)，可能導致非字符內(nèi)容的比較問題。

FLOAT和DOUBLE：由于浮點數(shù)精度問題，分組結果可能不穩(wěn)定。

總的來說，適合在 GROUP BY 中使用的數(shù)據(jù)類型通常是具有明確順序或可數(shù)性質(zhì)的數(shù)據(jù)類型，而不是基于文本或二進制的數(shù)據(jù)類型。

寫在最后

以上就是MySQL使用GROUP BY使用技巧和注意事項總結的詳細內(nèi)容，更多關于MySQL GROUP BY使用的資料請關注腳本之家其它相關文章！

最新評論