MySQL使用GROUP BY使用技巧和注意事项总结

蚝酒

GROUP BY简介

1. GROUP BY 子句是 在MySQL 中用于将查询结果按照指定的列或表达式进行分组的关键字。它通常与聚合函数一起使用，能够对每个分组进行统计或计算，并在需要时进行筛选，是处理数据库中大量数据并生成汇总报表的重要工具。
   
2. 常用的聚合函数有：count() 计数, sum() 求和 , avg() 求平均值, max() 求最大值, min()求最小值。

复制代码

基本用法

我们拿一张学生表举例
创建表：

1. CREATE TABLE students (
   
2.         student_id INT AUTO_INCREMENT PRIMARY KEY, -- 学生ID，自增主键
   
3.         name VARCHAR(50), -- 学生姓名
   
4.         major VARCHAR(50), -- 专业
   
5.         grade VARCHAR(10), -- 年级
   
6.         age INT, -- 年龄
   
7.         admission_year datetime -- 入学日期
   
8. )

复制代码

插入数据：

1. INSERT INTO students (name, major, grade, age, admission_year) VALUES
   
2. ('张三', '计算机科学', '大一', 18, '2024-01-01 00:00:00'),
   
3. ('李四', '数学', '大一', 19, '2024-01-01 00:00:00'),
   
4. ('王五', '物理', '大二', 20, '2023-01-01 00:00:00'),
   
5. ('赵六', '化学', '大二', 19, '2023-01-01 00:00:00'),
   
6. ('小明', '生物', '大三', 21, '2022-01-01 00:00:00'),
   
7. ('小红', '历史', '大三', 22, '2022-01-01 00:00:00'),
   
8. ('小李', '地理', '大四', 23, '2021-01-01 00:00:00'),
   
9. ('小张', '经济学', '大四', 22, '2021-01-01 00:00:00'),
   
10. ('小王', '文学', '大一', 18, '2024-01-01 00:00:00'),
    
11. ('小刘', '计算机科学', '大一', 19, '2024-01-01 00:00:00'),
    
12. ('小杨', '数学', '大二', 20, '2023-01-01 00:00:00'),
    
13. ('小白', '物理', '大二', 19, '2023-01-01 00:00:00'),
    
14. ('小黑', '化学', '大三', 21, '2022-01-01 00:00:00'),
    
15. ('小猫', '生物', '大三', 22, '2022-01-01 00:00:00'),
    
16. ('小狗', '历史', '大四', 23, '2021-01-01 00:00:00'),
    
17. ('小鸟', '地理', '大四', 22, '2021-01-01 00:00:00'),
    
18. ('小鱼', '经济学', '大一', 18, '2024-01-01 00:00:00'),
    
19. ('小虫', '文学', '大一', 19, '2024-01-01 00:00:00'),
    
20. ('小兔', '计算机科学', '大二', 20, '2023-01-01 00:00:00'),
    
21. ('小鸡', '数学', '大二', 19, '2023-01-01 00:00:00'),
    
22. ('小鸭', '物理', '大三', 21, '2022-01-01 00:00:00'),
    
23. ('小狐', '化学', '大三', 22, '2022-01-01 00:00:00'),
    
24. ('小牛', '生物', '大四', 23, '2021-01-01 00:00:00'),
    
25. ('小马', '历史', '大四', 22, '2021-01-01 00:00:00'),
    
26. ('小羊', '地理', '大一', 18, '2024-01-01 00:00:00'),
    
27. ('小猪', '经济学', '大一', 19, '2024-01-01 00:00:00'),
    
28. ('小狗', '文学', '大二', 20, '2023-01-01 00:00:00'),
    
29. ('小鸡', '计算机科学', '大二', 19, '2023-01-01 00:00:00'),
    
30. ('小鸭', '数学', '大三', 21, '2022-01-01 00:00:00'),
    
31. ('小猫', '物理', '大三', 22, '2022-01-01 00:00:00'),
    
32. ('小猴', '化学', '大四', 23, '2021-01-01 00:00:00'),
    
33. ('小狗', '生物', '大四', 22, '2021-01-01 00:00:00'),
    
34. ('小鸟', '历史', '大一', 18, '2024-01-01 00:00:00'),
    
35. ('小猫', '地理', '大一', 19, '2024-01-01 00:00:00'),
    
36. ('小鱼', '经济学', '大二', 20, '2023-01-01 00:00:00'),
    
37. ('小虫', '文学', '大二', 19, '2023-01-01 00:00:00');

复制代码

单列分组

例如：按照年级对学生进行分组，并计算每个年级的学生数量。

1. SELECT grade, COUNT(*)
   
2. FROM students
   
3. GROUP BY grade;
   

复制代码

多列分组

例如：按照年级和年龄对学生进行分组，并计算每个年级、年龄组合的学生数量。

1. SELECT grade, age, COUNT(*)
   
2. FROM students
   
3. GROUP BY grade, age;

复制代码

使用聚合函数

例如：计算每个年级的学生平均年龄

1. SELECT grade, AVG(age)
   
2. FROM students
   
3. GROUP BY grade;

复制代码

过滤分组结果

1. HAVING 子句在 GROUP BY 之后对分组进行过滤。它允许筛选哪些组将包含在结果中，类似于 WHERE 子句对行进行过滤。通常，HAVING 子句用于过滤聚合后的结果，根据某些条件选择性地包括或排除分组。

复制代码

例如：筛选出平均年龄超过 20 岁的年级

1. SELECT grade, AVG(age)
   
2. FROM students
   
3. GROUP BY grade
   
4. HAVING AVG(age) > 20;

复制代码

按表达式分组

例如：按照入学年份（在 "admission_year" 列中）对学生进行分组，并计算每个入学年份的学生数量。

1. SELECT YEAR(admission_year), COUNT(*)
   
2. FROM students
   
3. GROUP BY YEAR(admission_year);

复制代码

使用 GROUP BY 的排序

例如：按照年级对学生进行分组，并按照每个年级的学生数量从高到低排序。

1. SELECT grade, COUNT(*)
   
2. FROM students
   
3. GROUP BY grade
   
4. ORDER BY COUNT(*) DESC;

复制代码

注意事项


遵循原则

1. 确保在SELECT子句中使用的列都包含在GROUP BY子句中，或者是聚合函数的参数。否则，查询可能会产生错误的结果或语法错误。

复制代码

换句话说group by 有一个原则,就是 select 后面的所有列中,没有使用聚合函数的列,必须出现在 group by 后面
正例：
一共select了grade ,age ,student_id三列，只有student_id列使用了count聚合函数，grade ,age列没用聚合函数就必须跟在group by 后面

1. SELECT grade, age, COUNT(student_id) as 学生数量
   
2. FROM students
   
3. GROUP BY grade, age;

复制代码

查询结果返回了年纪跟年龄的所有组合下的学生数量。
反例：
在 SELECT 子句中，除了 COUNT(student_id) 使用了聚合函数外，其余的两列 grade 和 age 都没有使用聚合函数。但是在 GROUP BY 子句中，只列出了 grade 列，而没有包括 age 列。因此，这个查询违反了该原则。

1. SELECT grade, age, COUNT(student_id) as 学生数量
   
2. FROM students
   
3. GROUP BY grade;

复制代码

由于违背了group by的原则，age列没有跟在group by后面导致只查询了不同年级的学生数量统计，然而结果出现的age列仅仅是对应年级下第一个学生的年龄，这样是没有意义的，这样的结果是混乱的。

使用能够唯一标识每个分组的字段或字段组合

正例：
比如专业，年级。
反例：
唯一标识符字段：如果字段中的值对每个数据行都是唯一的，那么使用这样的字段进行 GROUP BY 将会使每个分组中只有一行数据，且分组数量大。
包含大量不同值的字段：如果某个字段的取值范围非常广泛，例如一个具有高基数（cardinality）的字段，使用它进行 GROUP BY 可能会导致大量的小分组，从而使结果变得难以理解或者过于细粒度化。
文本字段：虽然您可以使用文本字段进行 GROUP BY，但是它可能会导致分组的数量庞大，并且对结果的解释会变得更加困难。在这种情况下，最好先对文本字段进行分析或预处理，以便将其转换为更具可分组性的特征。
包含 NULL 值的字段：如果一个字段大部分值都是 NULL，那么使用它进行 GROUP BY 可能会使得 NULL 值形成一个单独的分组，而其他分组则非常少。

性能

1. GROUP BY操作可能会导致查询的性能下降，特别是在处理大量数据时。确保索引和适当的优化策略可以帮助提高查询性能。

复制代码

创建索引：为 GROUP BY 子句中的字段创建索引，这样数据库可以更快地定位并处理数据。如果您经常使用某个字段进行 GROUP BY，考虑为该字段创建索引以加快查询速度。
使用覆盖索引：创建覆盖索引以覆盖 GROUP BY 查询中涉及的所有字段。这样可以避免数据库执行额外的查找操作，从而提高性能。
限制结果集：在 GROUP BY 子句之前使用 WHERE 子句过滤数据，以减少处理的数据量。只选择必要的数据行可以显著提高查询性能。
使用聚合函数：考虑使用聚合函数（如SUM、COUNT、AVG等）来减少数据量。尽量在 GROUP BY 之前使用聚合函数，以便减少处理的数据量。
避免使用复杂表达式：在 GROUP BY 子句中尽量避免使用复杂的表达式或函数。这些表达式可能会增加处理时间，并使索引失效。

注意数据类型

在MySQL中，虽然可以在几乎任何数据类型的列上使用GROUP BY子句，但某些数据类型可能在实际应用中带来挑战或性能问题。
适合分组的数据类型

1. <ul><li><strong>INT、BIGINT等整数类型</strong>：这些类型在进行分组和比较时比较可靠。</li><li><strong>VARCHAR、CHAR等字符类型</strong>：字符类型在比较和分组时更具可预测性。</li></ul>

复制代码

不适合分组的数据类型

1. <strong>BLOB和TEXT</strong>：用于存储大型文本或二进制数据，比较和分组时性能较差。
   
2. <strong>JSON</strong>：嵌套结构复杂，直接比较不可靠，导致分组性能问题。
   
3. <strong>GEOMETRY</strong>：用于存储空间数据，比较复杂且计算量大，难以进行分组。
   
4. <strong>VARBINARY/BINARY</strong>：存储二进制数据，可能导致非字符内容的比较问题。
   
5. <strong>FLOAT和DOUBLE</strong>：由于浮点数精度问题，分组结果可能不稳定。

复制代码

总的来说，适合在 GROUP BY 中使用的数据类型通常是具有明确顺序或可数性质的数据类型，而不是基于文本或二进制的数据类型。

写在最后

以上就是MySQL使用GROUP BY使用技巧和注意事项总结的详细内容，更多关于MySQL GROUP BY使用的资料请关注脚本之家其它相关文章！

来源:https://www.jb51.net/database/320773dq7.htm
免责声明：由于采集信息均来自互联网，如果侵犯了您的权益，请联系我们【E-Mail:cb@itdo.tech】 我们会及时删除侵权内容，谢谢合作！