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

阳光穿透心脏的1/2处 2022-03-27 06:24 391阅读 0赞

### 文章目录 ###

*  1、分组查询
 *   *  ①GROUP BY
     *  ②HAVING
     *  总结
 *  2、 聚合查询-- max, min, sum, avg,count

# 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