浅析MySQL使用 GROUP BY 分组聚合与分割聚合

发布时间:2019-05-13  栏目:sqlite  评论:0 Comments

***  原创小说,转发请注解出处:http://www.cnblogs.com/weix-l/p/7521278.html


  若有荒唐,请谈论建议,感激!

1. 聚合函数(Aggregate Function)

  MySQL(5.柒 ) 官方文书档案中提交的聚合函数列表(图片)如下:

图片 1

详细情况点击https://dev.mysql.com/doc/refman/5.7/en/group-by-functions.html 。

  除非另有证实,否则聚合函数都会忽略空值(NULL values)。

贰. 聚合函数的应用

  聚合函数一般对 GROUP BY
语句进行分组后的各种分组起效用,即,假若在查询语句中不选取 GROUP BY
对结果集分组,则聚合函数就对结果集的享有行起效用。为声明聚合函数的应用,现创设测试表
member 实行测试,member 的数据结构如下(使用 SELECT * FROM member
查询所得):

  图片 2

 

  壹)对结果集直接动用聚合函数

  举个例子,使用聚合函数SUM () 计算有所会员(member)
的会费总和,则可使用:

SELECT SUM(fee) AS total_fee FROM member    #计算所有会员会费总和

询问结果为:

  图片 3

SUM 函数会对任何字段列 fee 进行求和。当然,也能够求平均值、最大值等。

  其它,也得以行使 WHERE 语句举行限定条件的集聚查询。比如,纵然要查询
country 为 China 的会员会费之和,则为:

SELECT SUM(fee) AS China_total_fee, country FROM member WHERE country = 'China'

结果突显如下:

  图片 4

 

  2)GROUP BY 对结果集分组后选拔聚合函数——组内聚合

  • GROUP BY 怎么着分组?

   ——将字段值一样的记录归为壹组,可用COUNT(*)
计算组内成员个数;

  • “组内聚合”为啥意?

   ——以分组为单位,对组内各种成员使用聚合函数实行总结,即聚合函数是有关分组成员的函数。

  试想,如若要从测试表中询问各种国家的会费总和呢?各类国家的会费,即先将有着结果集按
country 字段实行分组,country
值同样的行归为壹组,然后以组为单位实行求和,那样查询的结果记录数等于分组字段分化值的个数。总共有出自八个国家(China,
US, UK)的会员,所以分组聚合查询的结果记录数为3:

#查询每个国家的会费之和

SELECT SUM(fee) AS country_group_total_fee, country FROM member GROUP BY country

该查询语句会总结各个国家的会费之和,然后呈现按各样国家分组的询问结果:

  图片 5

   规范SQL( standard SQL) 和 MySQL 都提供 HAVING 语句对利用 GROUP BY
分组之后的结果实行典型化筛选并发出新的结果集。举个例子,对于前述 1)中查询中国会员会费总和的主题素材,能够行使HAVING
语句:

#使用HAVING语句查询中国会员会费总和

SELECT SUM(fee) AS country_group_total_fee, country FROM member GROUP BY country HAVING country = 'China'

结果和方面同样:

  图片 6

  这种措施与前述 1)中一贯利用WHERE实行界定比较有个别画蛇添足,为啥吧?因为
country 在此是分组字段(group column),对分组字段使用 HAVING
再一次开始展览限定则就显得分组毫无意义,因为这时候完全能够经过行使 WHERE
举行筛选后直接求和达成。那么,能采纳非聚合列(nonaggregated column)
为限量条件吧?答案是,不唯有没有意义,而且不一致意。非聚合列指的是未曾用聚合函数而是要询问的表本人的字段,因为运用
GROUP BY
分组查询后的集结结果列中向来就不分包非聚合字段列,所以在深入分析SQL语句时根本找不到那么些字段。举例,当您想博得每种国家性别为
man 的会员的会费之和时恐怕尝试在地点这几个讲话中选用 HAVING 对 sex
进行限制,像上面那样:

#错误:尝试使用HAVING 语句对非聚合字段进行限定

SELECT SUM(fee) AS country_group_total_fee FROM member m GROUP BY country HAVING m.sex = 'man'

进行后会报错 Err 105肆:

[Err] 1054 – Unknown column ‘m.sex’ in
‘having clause’,提醒未知的列m.sex,纵然此处使用外号进行求证也非常。那么什么样兑现查询每一个国家性别为 man
的会员的会费之和呢?当然还是利用WHERE 语句在 GROUP BY
举办分组在此之前就开始展览限制:

#在分组之前使用 WHERE 进行条件筛选

SELECT SUM(fee) AS country_group_total_fee, country FROM member WHERE sex = 'man' GROUP BY country

发出下边结果:

  图片 7 

  所以,HAVING
不可能对分组本人起效果,但能够对分组后的结果进行查询范围,而限定的尺码只好为聚合列(aggregated
column),聚合列指的是在 SELECT 列 (SELECT
list)中利用聚合函数发生的列,比如,此处的SUM(fee) 就是聚合列。在HAVING
中对聚合列进行界定,能够获取知足一定标准的聚合列结果。比方,在上头拿到每一个国家会员费用之和后再限定查询哪些会员费用之和超过一千0,则可以使用上边包车型大巴SQL
语句:

#查询会员费总和超过10000 的国家

SELECT SUM(fee) country FROM member GROUP BY country HAVING SUM(fee) > 10000

其结果就只剩余中国了:)

  图片 8

 那是在正规SQL语句中的语法。在MySQL中增添了HAVING
的用法,使其还不错聚合列的外号作为限制条件,比如地点的渴求运用外号的查询语句为:

#在HAVING 中使用别名

SELECT SUM(fee) AS country_group_total_fee, country FROM member GROUP BY country HAVING country_group_total_fee > 10000

其结果仍为:

  图片 9

3)GROUP BY 按四个分组字段分组后选取聚合函数——细分组内会集

   纵然运用一个分组字段分组后的群集结果记录数等于该分组字段分歧值的个数,那么,使用四个分组字段未来呢?比如,在上边的询问的功底上,假如想要查询每种国家男、女分别的会费总和时,能够利用上边包车型大巴语句:

#查询每个国家男、女会员的总和会费

SELECT SUM(fee) AS sex_and_country_group_total_fee, country, sex FROM member GROUP BY country,sex

结果如下:

  图片 10

从地方的结果能够看出来,“中华夏族民共和国的男性会员出的总会费最多,而United Kingdom的男子会员的总会费最少”。总共八个国家,假诺只按国家(country)
举办分组,唯有叁条记下,如果再按性别 (sex)
分,则会在分组后的各种组(也即每一行、每一条记下)里按性别的两样再进行划分,因为性别值只有二种,所以每种国家的分组又被分成两小组,则四个国家一同就有陆小组(陆= 三 × 2),那样结尾也就能够有6条记下,如上海体育场所示。

  为明白各样细分小组的个数,在SELECT
查询列的终极加上计算分组个数的聚合函数 COUNT(*):

#多分组字段分组,并统计每组个数

SELECT SUM(fee) AS sex_and_country_group_total_fee, country, sex, COUNT(*) AS row_num FROM member GROUP BY country, sex

结果如下:

图片 11

  上面包车型客车结果默许按接近GROUP BY
的1一进行排序,但假如要钦定排序一句,则可选用OEscortDELacrosse BY
,比方,对地方的结果按 sex 排序:

#将分组结果按sex 排序

SELECT SUM(fee) AS sex_and_country_group_total_fee, country, sex, count(*) AS row_num FROM member GROUP BY country, sex ORDER BY sex

结果如下:

图片 12

  假诺用任何字段对结果再实行划分呢?原理与上述多个字段进展分组时一样的,只是分组的深浅更加多,很生硬结果的记录行数也愈来愈多,但好歹,你会发觉每一条分组后的结果都是不等同的,那多亏分组结果的性状,因为O陆风X8DER
BY
本身就有所聚合作用,各类聚合列的结果是透过分组归类的结果,所以唯有一条记下。

  那么,就算用表的 主键 或 非空唯壹性字段
进行分组,结果会怎么样呢?比如,在本测试表中,id 是其主键,name
是非空的具备唯壹性约束的字段,下边分别是以 id 和 name 举办分组的MySQL
语句和结果:

#以主键id进行分组

SELECT SUM(fee) AS sex_and_country_group_total_fee, id, COUNT(*) AS row_num FROM member GROUP BY id

结果如下:

  图片 13

#以非空唯一性约束字段进行分组

SELECT SUM(fee) AS sex_and_country_group_total_fee, name, COUNT(*) AS row_num FROM member GROUP BY name

结果如下:

  图片 14

很鲜明,这三种分组的结果中聚合函数结果列是同样的,每组的结果记录行数也相同,而且都为壹,那表达按主键或非空唯一性约束字段进行分组其结果同样,且结果就是表的凡事每1行记录。那样做或许未有太大要思,但拉动精晓GROUP BY 分组的原理。

3. 总结

  一) 可直接对某些字段使用聚合函数,也可用 WHERE
语句筛选后对某些字段使用聚合函数;

  二) 聚合函数一般意义于选用 GROUP BY
分组后的分组成员,用于总结每种分组的数目;

  三) 不可能对未有行使 GROUP BY 分组的聚合函数使用 HAVING  进行限定;

  四) 可对选择 GROUP BY 分组查询后的结果使用 HAVING
进行界定,其范围条件最棒为聚合函数列(自个儿或其余聚合函数);

  伍) 可在接纳 GROUP BY 分组前应用 WHERE 对结果开始展览筛选,在分组后使用
HAVING 对聚合函数列实行界定;

  6) 可采用 OLANDDE景逸SUV BY 对结果遵照某些字段(任性字段或列,使用 GROUP BY
分组时也可使用聚合函数列)进行排序;

  7)
当遵照主键或非空唯1性约束字段实行分组时,其结果为全部表的百分百记录。

4. 参考文献

  [1]. MySQL
官方文书档案  U冠道L: https://dev.mysql.com/doc/refman/5.7/en/group-by-functions.html

留下评论

网站地图xml地图