Mysql基础-聚合、分组

Mysql基础-聚合、分组

1.聚合函数

实际上 SQL 函数还有一类，叫做聚合（或聚集、分组）函数，它是对 一组数据进行汇总的函数，输入的是一组数据的集合，输出的是单个值

单行函数:
$$
f(x)=y
$$
聚合函数(又称之为多行函数)：
$$
f(x1,x2,x3...xn)=y
$$

2.常用聚合函数

• 问题1：用count(*)，count(1)，count(列名)谁好呢?

  其实，对于MyISAM引擎的表是没有区别的。这种引擎内部有一计数器在维护着行数。 Innodb引擎的表用count(*),count(1)直接读行数，复杂度是O(n)，因为innodb真的要去数一遍。但好于具体的count(列名)。

• 问题2：能不能使用count(列名)替换count(*)?

  不要使用 count(列名)来替代 count() ， count() 是 SQL92 定义的标准统计行数的语法，跟数 据库无关，跟 NULL 和非 NULL 无关。 说明：count(*)会统计值为 NULL 的行，而 count(列名)不会统计此列为 NULL 值的行。

3.Group By使用

• group by 字段1，字段2，...

  按照字段1，字段2将数据进行分组（分割成多组记录）

• select 搭配group by使用注意：

  • 在SELECT列表中所有未包含在组函数中的列都应该包含在 GROUP BY子句中,包含在 GROUP BY 子句中的列不必包含在SELECT 列表中(即select 出现的字段必须在group by 字段中，group by 字段不必全部出现在select字段中)

    # Group By使用
    SELECT e.department_id  ,AVG(e.salary) 
    FROM employees e 
    group by e.department_id,e.job_id  ;
    
    
    # 错误使用group by
    SELECT e.department_id ,e.job_id ,AVG(e.salary) 
    FROM employees e 
    group by e.department_id ;
    

    select 其他字段（不出现在group by 中）本身就失去了分组的意义，分组的目的是处理一组数据，而不是随机选取其中的一条数据的一个字段进行展示。

  • 搭配group by使用时，select后面还可以出现使用聚合函数的字段

    SELECT e.department_id  ,AVG(e.salary),MAX(e.salary),MIN(e.salary)  
    FROM employees e 
    group by e.department_id,e.job_id  ;
    

    group by分组后，使用聚合函数处理一组数据（即多行数据），而使用单行函数就不符合要求

• with rollup

  使用 WITH ROLLUP 关键字之后，在所有查询出的分组记录之后增加一条记录，该记录计算查询出的所 有记录的总和，即统计记录数量。

SELECT department_id,AVG(salary)
FROM employees
WHERE department_id > 80
GROUP BY department_id WITH ROLLUP;

注意： 当使用ROLLUP时，不能同时使用ORDER BY子句进行结果排序，即ROLLUP和ORDER BY是互相排斥 的。

4.Having

• 作用

  过滤group by分组后的结果

  SELECT e.department_id  ,AVG(e.salary) avgSal  
  FROM employees e 
  group by e.department_id,e.job_id
  HAVING avgSal >3000
  

• having和where使用区别

  • 区别1：WHERE 可以直接使用表中的字段作为筛选条件，但不能使用分组中的计算函数作为筛选条件； HAVING 必须要与 GROUP BY 配合使用，可以把分组计算的函数和分组字段作为筛选条件。

    这决定了，在需要对数据进行分组统计的时候，HAVING 可以完成 WHERE 不能完成的任务。这是因为， 在查询语法结构中，WHERE 在 GROUP BY 之前，所以无法对分组结果进行筛选。HAVING 在 GROUP BY 之 后，可以使用分组字段和分组中的计算函数，对分组的结果集进行筛选，这个功能是 WHERE 无法完成 的。另外，WHERE排除的记录不再包括在分组中。

  • 区别2：如果需要通过连接从关联表中获取需要的数据，WHERE 是先筛选后连接，而 HAVING 是先连接 后筛选。

    这一点，就决定了在关联查询中，WHERE 比 HAVING 更高效。因为 WHERE 可以先筛选，用一 个筛选后的较小数据集和关联表进行连接，这样占用的资源比较少，执行效率也比较高。HAVING 则需要 先把结果集准备好，也就是用未被筛选的数据集进行关联，然后对这个大的数据集进行筛选，这样占用 的资源就比较多，执行效率也较低。

  

• 总结

  开发中的选择：

  WHERE 和 HAVING 也不是互相排斥的，我们可以在一个查询里面同时使用 WHERE 和 HAVING。包含分组 统计函数的条件用 HAVING，普通条件用 WHERE。这样，我们就既利用了 WHERE 条件的高效快速，又发 挥了 HAVING 可以使用包含分组统计函数的查询条件的优点。当数据量特别大的时候，运行效率会有很 大的差别。