MySQL使用GROUP BY使用技巧和注意事项总结！
MySQL使用GROUP BY使用技巧和注意事项总结！
GROUP BY简介
GROUP BY 子句是 在MySQL 中用于将查询结果按照指定的列或表达式进行分组的关键字。它通常与聚合函数一起使用，能够对每个分组进行统计或计算，并在需要时进行筛选，是处理数据库中大量数据并生成汇总报表的重要工具。
常用的聚合函数有：count() 计数, sum() 求和 , avg() 求平均值, max() 求最大值, min()求最小值。
基本用法
我们拿一张学生表举例
创建表：
|
1
2
3
4
5
6
7
8
|
CREATE TABLE students (student_id INT AUTO_INCREMENT PRIMARY KEY, -- 学生ID，自增主键name VARCHAR(50), -- 学生姓名major VARCHAR(50), -- 专业grade VARCHAR(10), -- 年级age INT, -- 年龄admission_year datetime -- 入学日期) |
插入数据：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
|
INSERT INTO students (name, major, grade, age, admission_year) VALUES('张三', '计算机科学', '大一', 18, '2024-01-01 00:00:00'),('李四', '数学', '大一', 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'),('小张', '经济学', '大四', 22, '2021-01-01 00:00:00'),('小王', '文学', '大一', 18, '2024-01-01 00:00:00'),('小刘', '计算机科学', '大一', 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'),('小鸟', '地理', '大四', 22, '2021-01-01 00:00:00'),('小鱼', '经济学', '大一', 18, '2024-01-01 00:00:00'),('小虫', '文学', '大一', 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'),('小马', '历史', '大四', 22, '2021-01-01 00:00:00'),('小羊', '地理', '大一', 18, '2024-01-01 00:00:00'),('小猪', '经济学', '大一', 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'),('小狗', '生物', '大四', 22, '2021-01-01 00:00:00'),('小鸟', '历史', '大一', 18, '2024-01-01 00:00:00'),('小猫', '地理', '大一', 19, '2024-01-01 00:00:00'),('小鱼', '经济学', '大二', 20, '2023-01-01 00:00:00'),('小虫', '文学', '大二', 19, '2023-01-01 00:00:00'); |
单列分组
例如：按照年级对学生进行分组，并计算每个年级的学生数量。
|
1
2
3
|
SELECT grade, COUNT(*)FROM studentsGROUP BY grade; |
多列分组
例如：按照年级和年龄对学生进行分组，并计算每个年级、年龄组合的学生数量。
|
1
2
3
|
SELECT grade, age, COUNT(*)FROM studentsGROUP BY grade, age; |
使用聚合函数
例如：计算每个年级的学生平均年龄
|
1
2
3
|
SELECT grade, AVG(age)FROM studentsGROUP BY grade; |
过滤分组结果
HAVING 子句在 GROUP BY 之后对分组进行过滤。它允许筛选哪些组将包含在结果中，类似于 WHERE 子句对行进行过滤。通常，HAVING 子句用于过滤聚合后的结果，根据某些条件选择性地包括或排除分组。
例如：筛选出平均年龄超过 20 岁的年级
|
1
2
3
4
|
SELECT grade, AVG(age)FROM studentsGROUP BY gradeHAVING AVG(age) > 20; |
按表达式分组
例如：按照入学年份（在 "admission_year" 列中）对学生进行分组，并计算每个入学年份的学生数量。
|
1
2
3
|
SELECT YEAR(admission_year), COUNT(*)FROM studentsGROUP BY YEAR(admission_year); |
使用 GROUP BY 的排序
例如：按照年级对学生进行分组，并按照每个年级的学生数量从高到低排序。
|
1
2
3
4
|
SELECT grade, COUNT(*)FROM studentsGROUP BY gradeORDER BY COUNT(*) DESC; |
注意事项
遵循原则
确保在SELECT子句中使用的列都包含在GROUP BY子句中，或者是聚合函数的参数。否则，查询可能会产生错误的结果或语法错误。
换句话说group by 有一个原则,就是 select 后面的所有列中,没有使用聚合函数的列,必须出现在 group by 后面
正例：
一共select了grade ,age ,student_id三列，只有student_id列使用了count聚合函数，grade ,age列没用聚合函数就必须跟在group by 后面
|
1
2
3
|
SELECT grade, age, COUNT(student_id) as 学生数量FROM studentsGROUP BY grade, age; |
查询结果返回了年纪跟年龄的所有组合下的学生数量。
反例：
在 SELECT 子句中，除了 COUNT(student_id) 使用了聚合函数外，其余的两列 grade 和 age 都没有使用聚合函数。但是在 GROUP BY 子句中，只列出了 grade 列，而没有包括 age 列。因此，这个查询违反了该原则。
|
1
2
3
|
SELECT grade, age, COUNT(student_id) as 学生数量FROM studentsGROUP BY grade; |
由于违背了group by的原则，age列没有跟在group by后面导致只查询了不同年级的学生数量统计，然而结果出现的age列仅仅是对应年级下第一个学生的年龄，这样是没有意义的，这样的结果是混乱的。
使用能够唯一标识每个分组的字段或字段组合
正例：
比如专业，年级。
反例：
唯一标识符字段：如果字段中的值对每个数据行都是唯一的，那么使用这样的字段进行 GROUP BY 将会使每个分组中只有一行数据，且分组数量大。
包含大量不同值的字段：如果某个字段的取值范围非常广泛，例如一个具有高基数（cardinality）的字段，使用它进行 GROUP BY 可能会导致大量的小分组，从而使结果变得难以理解或者过于细粒度化。
文本字段：虽然您可以使用文本字段进行 GROUP BY，但是它可能会导致分组的数量庞大，并且对结果的解释会变得更加困难。在这种情况下，最好先对文本字段进行分析或预处理，以便将其转换为更具可分组性的特征。
包含 NULL 值的字段：如果一个字段大部分值都是 NULL，那么使用它进行 GROUP BY 可能会使得 NULL 值形成一个单独的分组，而其他分组则非常少。
性能
GROUP BY操作可能会导致查询的性能下降，特别是在处理大量数据时。确保索引和适当的优化策略可以帮助提高查询性能。
创建索引：为 GROUP BY 子句中的字段创建索引，这样数据库可以更快地定位并处理数据。如果您经常使用某个字段进行 GROUP BY，考虑为该字段创建索引以加快查询速度。
使用覆盖索引：创建覆盖索引以覆盖 GROUP BY 查询中涉及的所有字段。这样可以避免数据库执行额外的查找操作，从而提高性能。
限制结果集：在 GROUP BY 子句之前使用 WHERE 子句过滤数据，以减少处理的数据量。只选择必要的数据行可以显著提高查询性能。
使用聚合函数：考虑使用聚合函数（如SUM、COUNT、AVG等）来减少数据量。尽量在 GROUP BY 之前使用聚合函数，以便减少处理的数据量。
避免使用复杂表达式：在 GROUP BY 子句中尽量避免使用复杂的表达式或函数。这些表达式可能会增加处理时间，并使索引失效。
注意数据类型
在MySQL中，虽然可以在几乎任何数据类型的列上使用GROUP BY子句，但某些数据类型可能在实际应用中带来挑战或性能问题。
适合分组的数据类型
- INT、BIGINT等整数类型：这些类型在进行分组和比较时比较可靠。
- VARCHAR、CHAR等字符类型：字符类型在比较和分组时更具可预测性。
不适合分组的数据类型
BLOB和TEXT：用于存储大型文本或二进制数据，比较和分组时性能较差。
JSON：嵌套结构复杂，直接比较不可靠，导致分组性能问题。
GEOMETRY：用于存储空间数据，比较复杂且计算量大，难以进行分组。
VARBINARY/BINARY：存储二进制数据，可能导致非字符内容的比较问题。
FLOAT和DOUBLE：由于浮点数精度问题，分组结果可能不稳定。
总的来说，适合在 GROUP BY 中使用的数据类型通常是具有明确顺序或可数性质的数据类型，而不是基于文本或二进制的数据类型。
学习资料见知识星球。
以上就是今天要分享的技巧，你学会了吗？若有什么问题，欢迎在下方留言。
快来试试吧，小琥 my21ke007。获取 1000个免费 Excel模板福利！
更多技巧， www.excelbook.cn
欢迎 加入 零售创新 知识星球，知识星球主要以数据分析、报告分享、数据工具讨论为主；
1、价值上万元的专业的PPT报告模板。
2、专业案例分析和解读笔记。
3、实用的Excel、Word、PPT技巧。
4、VIP讨论群，共享资源。
5、优惠的会员商品。
6、一次付费只需99元，即可下载本站文章涉及的文件和软件。
共有 0 条评论