SQL：分组查询&&聚合查询

1、分组查询

分组运行把数据分为多个逻辑组，以便能对每个组进行聚集计算。
聚合键中包含NULL时，在结果中会以“不确定”行的形式表现出来
使用聚合函数&GROUP BY子句时需要注意

• 只能写在SELECT子句中
• GROUP BY 子句中不能使用SELECT子句中的列
• GROUP BY子句的聚合结果是无序的。
• WHERE子句中不能使用聚合函数。

①GROUP BY

-- 每个部门的人数 【执行计划一样】
select dept_no, count(dept_no) from dept_emp group by dept_no ;

select dept_no, count(dept_no) from dept_emp group by dept_no with ROLLUP ;

可以用逗号分隔指定多列。

* Expression #3 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'userinfo.t_long.user_name' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
* 出错原因：mysql和up实现了对功能依赖的检测，如果启用了only_full_group_by：默认的sql-mode里的ONLY_FULL_GROUP_BY字段导致不能直接查询group_by包裹的之外的字段，也就是说select的列都要在group中或者本身是聚合列，也是就说GROUP BY的用法：
	* SELECT row_name1, row_name2 FROM table_name GROUP BY row_name1,row_name2; 顺序要一一对应
	* SELECT AVG(row_name2) FROM table_name GROUP BY row_name1;
	* SELECT AVG(row_name2), row_name1 FROM table_name GROUP BY row_name1;
* 查看SQL是什么模式：
	* SELECT @@sql_mode;
* 解决方法参考：https://blog.csdn.net/fansili/article/details/78664267
               https://www.jianshu.com/p/9e53216db6aa

SELECT vend_id AS vi, COUNT(*) AS num_prods FROM products GROUP BY vi;
不建议在GROUP BY子句中使用SELECT子句中定义的别名的别名，虽然MYSQL、PostgreSQL支持。因为SQL语句在DBMS内部的执行顺序：SELECT子句在GROUP BY 子句之后执行

除了MySQL以外，使用GROUP BY子句时，SELECT子句中不能出现聚合键之外的列名

SELECT子句的执行结果顺序是随机的

②HAVING

HAVING子句可以对分组之后的结果再进行过滤

>#人数大于50000的人
select dept_no, count(dept_no) from dept_emp group by dept_no HAVING count(dept_no) > 50000 ;

只有SELECT子句和HAVING子句以及ORDER BY 子句中能够使用聚合函数

• HAVING子句必须要写在GROUP BY子句之后
• 使用COUNT()函数等对表中数据进行汇总操作时，为其指定条件的不是WHERE子句，而是HAVING子句。where子句用来指定数据行的条件，HAVING子句用来指定分组的条件

WHERE子句和HAVING子句的执行速度

• 在WHERE子句和HAVING子句都可以使用的条件，最好写在WHERE子句中，因此执行速度更快。以及WHERE子句可以使用索引
• 使用COUNT(*)等对表中的数据进行聚合操作时，DBMS内部会进行排序处理。排序处理会大大增加机器负担[虽然Oracel等用hash处理来代替排序，但是也会增加负担]。因此，之哟与尽可能减少排序的行数，才能提高处理速度。使用WHERE子句指定条件时，由于排序之前就会数据进行了过滤，因此能够减少排序的数据。但是HAVING子句是先排序然后对数据分组的。

总结

• 语法：
  select 分组函数, 分组后的字段
  FROM 表
  【where 筛选条件】
  group by 分组的字段
  【having 分组后的筛选】
  【order by 子句】

• 执行顺序：from 表：先找查询哪张表; where 筛选条件：找出符合条件的列; group by 分组的字段:对不同列进行分组;having 分组后的筛选：分组之后再进行筛选; select 分组函数, 分组后的字段：根据分组筛选出来的列中找到需要查询的属性；order by 子句：对找到的属性进行排序

• 特点：
  1、分组查询中的筛选条件分为两类

        				数据源              位置           关键字
   		分组前筛选   原始表              group by前面   where
   		分组后筛选   分组后的结果集      group by后面   having
   		
   		分组函数做条件肯定时放在having子句中
   	    能用分组前筛选的就优先考虑分组前筛选
  

  2、group by 子句支持单个字段，多个字段[多个字段之间用逗号隔开没有顺序要求]，表达式或者函数(用的较小)

• 注意：

  • GROUP BY子句中列出的每一个列都必修是索引列或者有效的表达式，但是不能是聚集函数。如果select中使用表达式，则必须在GROUP BY子句中指定相同的表达式，不能使用别名
  • 除了聚集计算语句外，select语句中每个列必须在GROUP BY子句中给出
  • 如果分组列中具有NULL值，则NULL值将作为一个分组返回。如果列中有多行NULL值，它们将分为一组。
  • GROUP BY子句必须出现在WHERE子句之后，ORDER BY子句之前。
  • HAVING可以支持所有的WHERE子句

2、 聚合查询-- max, min, sum, avg,count

• 通常，聚合函数会对NULL之外的对象进行汇总。但是只有COUNT函数例外：使用COUNT()可以查出包含NULL再内的全部数据的行数。也就是聚合函数会将NULL排除在外，但是COUNT()除外，并不会排除NULL。
• 所谓聚合，就是将多行汇汇总为第一行。
• 使用聚合函数对表中的列进行计算合计值或者平均值等汇总操作，常用于进行分析和生成报表。
  • 确定表中函数或者满足某个条件或包含某个特定值的行数。
  • 获取表中行组的和
  • 找出某些列的最大值、最小值和平均值

max, min, sum, avg,count

• 功能：做统计使用，又称为统计函数
• 语法：select max(【distinct】字段) from 表名
• 支持： sum和avg一般用于处理数值型，max、min、count可以处理任何数据类型。以上分组函数都可以支持null
• 这五个聚集函数都可以使用：
  • 对所有行执行计算，指定ALL，ALL时默认的，可以忽略
  • 只包含不同的值，指定DISTINCT参数[DISTINCT只能放在字段前面]
• 四则运算中如果存在NULL，结果一定是NULL。聚合函数中列中包含NULL结果不是NULL的原因是在计算之前就已经把NULL的行排除在外了。也就是先选择不为NULL的行，然后再进行计算。
• 使用SUM()函数是，将NULL除外与等同于0的结果相同，但是AVG函数时这两种情况就完全不同了。
• MAX/MIN VS SUM/AVG：SUM/AVG函数只能对数值类型的列使用，而MAX、MIN函数原则上可以适用任何数据类型的列。

max, min, sum, avg 均忽略NULL值

SELECT AVG(item_price) FROM orderitems;

---

SELECT MAX(item_price), MIN(item_price) FROM orderitems;

---

SELECT SUM(quantity) AS qun_sum, SUM(item_price*quantity) AS zonghe FROM orderitems WHERE order_num = 20005;

---

SELECT AVG(DISTINCT item_price) FROM orderitems;

COUNT 与NULL

SELECT COUNT(*) AS ‘客户总数’ FROM customers; #不忽略NULL
SELECT COUNT(cust_email) FROM customers #计算除了NULL之外的数据的行数

• 总结COUNT：

• count(字段)：统计该字段非空值的个数
  count(*)：统计所有记录的条数
  count(distinct 字段)：字段中不重复的记录条数

• 使用须知

  • 建议尽量使用数据库的汇总函数，它们的返回结果一般比客户机应用程序计算要快的多。
  • 除了count(*)不会忽略NULL之外，其他都会忽略NULL