# 语法-GROUP BY

# 介绍

GROUP BY 语句用于结合聚合函数，根据一个或多个列对结果集进行分组。

# 使用

分组查询，分组查询时，如果需要条件过滤请使用 having

使用时需要注意 ONLY_FULL_GROUP_BY 错误。MySOL中如果在select 或 haveing 中查询的字段没有使用聚合函数（如 SUM AVG）等会出现报错。也就是 SELECT语句后的列，要么是GROUP BY语句后面出现的列，要么是使用了聚合函数。

MySql 5.7以后的版本，比如5.7.21，默认情况下，sql_mode里的only_full_group_by是被打开的

解决方案

使用ANY_VALUE() 对非聚合函数进行包裹
禁用mysql的 ONLY_FULL_GROUP_BY 模式

SELECT ANY_VALUE(column_name), aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name;

其它语法示例

/**
现在运营想查看每个学校用户的平均发贴和回帖情况，寻找低活跃度学校进行重点运营，
请取出平均发贴数低于5的学校或平均回帖数小于20的学校。
**/
select  
 avg(question_cnt) question_cnt_avg,
 avg(answer_cnt) answer_cnt_avg,
 university
from user_profile
group by university 
having question_cnt_avg < 5 or answer_cnt_avg < 20 


/**
题目：现在运营想要查看不同大学的用户平均发帖情况，
并期望结果按照平均发帖情况进行升序排列，请你取出相应数据
**/
select 
 university,
 avg(question_cnt) avg_question_cnt
from user_profile
group by university
order by avg_question_cnt asc

# 原理分析

select city ,count(*) as num from staff group by city;

这个sql的执行流程(group by后面的列如果不使用索引)：

创建内存临时表，表里有两个字段city和num；
全表扫描staff的记录，依次取出city = 'X’的记录。
- 判断临时表中是否有为 city='X’的行，没有就插入一个记录 (X,1);
- 如果临时表中有city='X’的行，就将x 这一行的num值加 1；
遍历完成后，再根据字段city做排序，得到结果集返回给客户端。

通过上面的分析我们可以看出，group by 后面没有索引时，sql查询会进行临时表和排序操作，而这两个操作是非常消耗性能的，so, we can do something based on the analysis results.

# 优化思路

方向1：既然它默认会排序，我们不给它排是不是就行了？
方向2：既然临时表是影响group by性能的X因素，我们是不是可以不用临时表？

# 解决方案

group by 后面的字段加索引（给需要分组的字段加索引，这样查询时既不会创建临时表，也不会进行排序 perfect）
order by null 不用排序（适合不创建索引的场景）
尽量只使用内存临时表：如果group by需要统计的数据不多，可以尽量只使用内存临时表；因为如果group by 的过程因为内存临时表放不下数据，从而用到磁盘临时表的话，是比较耗时的。因此可以适当调大tmp_table_size参数，来避免用到磁盘临时表。
使用SQL_BIG_RESULT：如果预估数据量比较大，我们使用SQL_BIG_RESULT 这个提示直接用磁盘临时表。MySQl优化器发现，磁盘临时表是B+树存储，存储效率不如数组来得高。因此会直接用数组来存储。

← 语法-Exists 语法-函数 →

语法-GROUP BY

介绍
使用
原理分析
优化思路
解决方案