MySQL聚合函数与数据分组

时间：2021-06-01 19:18:08

　　我们最常需要的是汇总数据而不是把他们实际检索出来

  • 确定表中行数（或满足某个条件或包含某个特定值的行数）
  • 确定表中行组的和
  • 找出表列（或所有行或特定列）的最大值，最小值和平均值

　　聚集函数是运行在行组上，计算和返回单个值的函数。

AVG([distinct] expr)

求平均值

COUNT({*|[distinct] } expr)

统计行的数量

MAX([distinct] expr)

求最大值

MIN([distinct] expr)

求最小值

SUM([distinct] expr)

求累加和

AVG，SUM

　　只用于单个列，为获得多个列的平均值要使用多个AVG，表中列值为null的行不参与计算

　　要想列值为NULL的行也参与组函数的计算，必须使用IFNULL函数对NULL值做转换。

COUNT

  1. count(*)：返回表中行的数量，不管列表中包含NULL值还是非NULL值，distinct不能用于count(*)
  2. count(列)：返回列值非空的行的数量
  3. count(distinct 列)：返回列值非空的、并且列值不重复的行的数量
  4. count(expr)：根据表达式统计数据
select UNIT as '单位',

    ->     COUNT(TO_DAYS(DATE)=TO_DAYS(NOW()) or null) as '今日统计',

    ->     COUNT(YEAR(DATE)=YEAR(NOW()) or null) as '今年统计'

    -> from v_jjd

    -> group by JJDW;

MAX、MIN

　　返回任意列的最大值，最小值（包括文本列），如果统计的列中只有NULL值，那么MAX和MIN就返回NULL

注意

  1. 每个组函数接收一个参数
  2. 默认情况下，组函数忽略列值为null的行，不参与计算
  3. 有时，会使用关键字distinct剔除字段值重复的条数
  4. 组函数不允许嵌套，例如：count(max(…))
  5. 组函数的参数可以是列或是函数表达式；
  6. 一个SELECT子句中可出现多个聚集函数

数据分组

　　把数据分为多个逻辑组，以便对每个组进行聚集计算

  1. group by可以包含任意数目得列，这使得能够对分组进行嵌套
  2. 如果在group by中间套了分组，数据将在最后的分组上汇总，换句话说，建立分组时，所有指定的列都一起计算
  3. group by中的每个列都必须是检索列或有效的表达式（不能使聚合函数），必须与在select中使用相同的表达式不能使别名
  4. 除聚集计算语句外，select中的每个列都必须在group by中给出,也就是说：通过select在返回集字段中，这些字段要么就要包含在group by语句后面，作为分组的依据，要么就要被包含在聚合函数中
  5. 如果分组列中有NULL值，NULL作为一个分组返回，若有多个NULL，他们分为一组返回
  6. 使用with rollup可以得到分组及分组汇总级别（针对每个分组的值）

　　sql一个执行过程：先执行的是from负责把数据库的表文件加载到内存中去，WHERE取出符合条件的记录行，生成一张临时表，select读取根据sql中有无group by（没有GROUP BY时，SELECT 会根据后面的字段名称对内存中的一张临时表整列读取，有GROUP BY时，会对内存中的若干临时表分别执行SELECT，而且只取各临时表中的第一条记录，然后再形成新的临时表。这就决定了查询sql使用GROUP BY的场景下，SELECT后面跟的一般是参与分组的字段和聚合函数，否则查询出的数据要是情况而定。另外聚合函数中的字段可以是表中的任意字段，需要注意的是聚合函数会自动忽略空值得到一个结果集，然后根据分组字段，将具有相同分组字段的记录归并成了一条记录（行）。这个时候剩下的那些不存在与group by语句后面作为分组依据的字段就很有可能出现多个值，但是目前一种分组情况只有一条记录（行），一个数据格是无法放入多个数值的，所以这个时候就需要通过一定的处理将这些多值的列转化成单值，然后将其放在对应的数据格中，那么完成这个步骤的就是前面讲到的聚合函数，这也就是为什么这些函数叫聚合函数），然后having对生成的临时表再次过滤，order by对生成的临时表排序，limit取数据个数。

having与where区别

  • where过滤指定的行，在分组之前过滤数据，where条件中不能包含聚组函数，使用where条件过滤出特定的行。
  • having过滤分组，在分组之后过滤数据，条件中经常包含聚组函数，使用having 条件过滤出特定的组，也可以使用多个分组标准进行分组。
  • HAVING可以单独使用而不和GROUP BY配合,如果只有HAVING子句而没有GROUP BY，表中所有的行分为一组
  • HAVING子句中可以使用组函数
  • HAVING子句中的列，要么出现在一个组函数中，要么出现在GROUP BY子句中(否则出错)

order by与group by

order by group by
排序产生的输出 分组行，单输出可能不是分组的顺序
任意列可使用（甚至是非选择的列） 只能使用选择列或表达式列，而必须使用每个选择列表达式
不一定需要

如果与聚合函数一起使用（或表达式列），则必须使用

select句子顺序

子句 说明 是否必须使用
select 要返回的列或表达式 YES
from 从检索数据的表 仅在从表中选择数据时
where 行级过滤 NO
group by 分组说明 仅在按组计算聚集时使用
having 组级过滤 NO
order by 要输出排序顺序 NO
limit 要检索的行数 NO

相关文章