在对数据进行分组时可能还需要根据数据分组而非原始数据从结果集中过滤掉不想要的数据由于group by 子句是在where子句被评估之后运行的因此无法为此对where子句增加过滤条件例如尝试过滤掉租借电影少于40部的客户无法在where子句中引用聚合函数count(*)因为在评估where子句时分组尚未生成因而必须将分组过滤条件放入having子句下面来看使用having子句的查询聚合函数聚合函数对分组中的所有行执行特定的操作尽管每种数据库服务器都有自己专有的聚合函数但所有的主流服务器都提供了下列常见的聚合函数max()返回集合中的最大值min()返回集合中的最小值avg()返回集合中的平均值sum()返回集合中所有值之和count()返回集合中所有值的个数下列查询使用了以上这些常见的聚合函数来分析电影租借付款数据隐式分组与显式分组在上一个示例中查询返回的每个值都是由聚合函数生成的因为没有使用group by子句所以只有一个隐式分组payment数据表中的所有行然而在大多数情况下除了聚合函数生成的列还需要检索其他列假设想要扩展之前的查询对于每位客户执行同样的5个聚合函数而不是在所有客户中查询为此在查询中检索customer_id列以及5个聚合函数执行该查询会报错原因在于没有明确指定数据应该如何分组所以需要添加一个group by子句来指定聚合函数应该应用于哪个分组有了group by子句服务器就知道先将customer_id列中相同的值分组然后将这5个聚合函数应用于所有的599个分组统计不同的值使用count函数确定每个分组的成员数量时可以选择是统计分组中的所有成员数量还是只对于某列统计不同的值例如考虑下列查询它以两种不同的方式对customer_id列使用count()函数查询中的第一列只是简单地统计payment数据表中的行数而第二列则检查customer_id列中的值仅计算其中不同值的数量使用表达式除了使用列作为聚合函数得参数也可以使用表达式例如找出一部电影从被租借到后来归还之间相隔的最大天数可以通过下列查询实现处理null在执行聚合函数或其他任何数值计算时应当首先考虑null是否会影响计算结果下面对此进行说明即使数据表中增加了 null值函数sum()、max()和avg()的返回值也没有发生变化这表明他们忽略了任何遇到的null值count(*)函数的返回值为4这是由于该数据表包含4行而count(val)函数的返回值为3两者的区别在于count(*)统计行数而count(val)统计val列包含多少个值并且忽略所有遇到的null值生成分组单列分组单列分组是最简单也是最常用的分组类型多列分组在某些情况下需要跨越多列生成分组例如找出每位演员参演的各种分级电影G、PG…的数量select fa.actor_id,f.rating,count(*) cnt from film f join film_actor fa using(film_id) group by actor_id,rating order by 1,3;这里order by1,3 表示结果先按照第一列升序再按第二列升序通过表达式分组除了使用列进行数据分组也可以根据表达式产生的值构建分组下列查询按年份对租借数据进行分组该查询使用了一个非常简单的表达式该表达式利用extract()函数返回日期的年份部分用于对rental数据表中的行进行分组生成汇总在多列分组的示例中统计了每位演员参演的各种评级电影的数量假设在计算每位演员/评级组合的总计数的同时还想知道不同演员参演的电影总数这时可以使用with rollup实现多了201行200位演员分别对应一行还有一行对应总数所有演员加一起参演的电影数量rollup 往上卷逐级算汇总GROUP BY只分组明细GROUP BY … WITH ROLLUP明细 各级小计 总计分组过滤条件不能把聚合函数放入查询的where子句where子句中的过滤条件是在数据被分组之前评估的所以服务器无法对分组执行任何函数向包含group by 子句的查询中添加过滤条件时仔细考虑是过滤原始数据将过滤条件放入where子句还是过滤分组后的数据将过滤条件放入having子句
