mysql汇总数据

• 汇总数据

第12章 汇总数据

★12.1 聚集函数

• 我们经常需要汇总数据而不用把它们实际检索出来，为此MySQL提供了专门的函数。使用这些函数，MySQL查询可用于检索数据，以便分析和报表生成。这种类型的检索例子有以下几种。

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

• 上述例子都需要对表中数据（而不是实际数据本身）汇总。因此，返回实际表数据是对时间和处理资源的一种浪费（更不用说带宽了）。重复一遍，实际想要的是汇总信息。为方便这种类型的检索，MySQL给出了5个聚集函数，见下表。这些函数能进行上述罗列的检索。

  聚集函数用来汇总数据。MySQL支持一系列聚集函数，可以用多种方法使用它们以返回所需的结果。这些函数是高效设计的，它们返回结果一般比你在自己的客户机应用程序中计算要快得多。

函数	说明
AVG()	返回某列的平均值
COUNT()	返回某列的行数
MAX()	返回某列的最大值
MIN()	返回某列的最小值
SUM()	返回某列值之和

• 聚集函数（aggregate function）：运行在行组上，计算和返回单个值的函数。

12.1.1 AVG()函数

• AVG() 通过对表中行数计数并计算特定列值之和，求得该列的平均值。AVG() 可用来返回所有列的平均值，也可以用来返回特定列或行的平均值。

  SELECT AVG(列名) {, AVG(列名)} FROM table_name
  	[WHERE where_definition]

• 下面的例子使用AVG()返回products表中所有产品的平均价格：

  SELECT AVG(prod_price) AS avg_price FROM products;

  AVG()也可以用来确定特定列或行的平均值。下面的例子返回特定供应商所提供产品的平均价格：

  SELECT AVG(prod_price) AS avg_price FROM products WHERE vend_id = 1003;

  这条SELECT语句与前一条的不同之处在于它包含了WHERE子句。此WHERE子句仅过滤出vend_id为1003的产品，因此avg_price中返回的值只是该供应商的产品的平均值。

• 注意：AVG()只用于单个列，即AVG()只能用来确定特定数值列的平均值，而且列名必须作为函数参数给出。为了获得多个列的平均值，必须使用多个AVG()函数。

  NULL值：AVG()函数忽略列值为NULL的行。

12.1.2 COUNT()函数

• COUNT()函数进行计数。可利用COUNT()确定表中行的数目或符合特定条件的行的数目。

  COUNT()函数有两种使用方式：

  • 使用COUNT(*)对表中行的数目进行计数，不管表列中包含的是空值（NULL）还是非空值。
  • 使用COUNT(column)对特定列中具有值的行进行计数，忽略NULL值。

  SELECT COUNT(*)|COUNT(列名) FROM table_name
  	[WHERE where_definition];

• 下面的例子返回customers表中客户的总数：

  SELECT COUNT(*) AS cust_number FROM customers;

  在此例子中，利用COUNT(*)对所有行计数，不管行中各列有什么值。计数值在cust_number中返回。

  下面的例子只对具有电子邮件地址的客户计数：

  SELECT COUNT(cust_email) AS cust_number FROM customers;

  这条SELECT语句使用COUNT(cust_email)对cust_email列中有值的行进行计数。在此例子中，cust_email的计数为3（表示5个客户中只有3个客户有电子邮件地址）。

• NULL 值：如果指定列名，则指定列的值为 NULL 的行被 COUNT() 函数忽略，但如果 COUNT() 函数中用的是星号（*），则不忽略。

12.1.3 MAX()函数

• MAX()返回指定列中的最大值。MAX()要求指定列名，如下所示：

  SELECT MAX(prod_price) AS max_price FROM products;

  这里，MAX()返回products表中最贵的物品的价格。

• 对非数值数据使用MAX()：虽然MAX()一般用来找出最大的数值或日期值，但MySQL允许将它用来返回任意列中的最大值，包括返回文本列中的最大值。在用于文本数据时，如果数据按相应的列排序，则MAX()返回最后一行。

• NULL值：MAX()函数忽略列值为NULL的行。

12.1.4 MIN()函数

• MIN()的功能正好与MAX()功能相反，它返回指定列的最小值。与MAX()一样，MIN()要求指定列名，如下所示：

  SELECT MIN(prod_price) AS min_price FROM products;

  其中MIN()返回products表中最便宜物品的价格。

• 对非数值数据使用MIN()：MIN()函数与MAX()函数类似，MySQL允许将它用来返回任意列中的最小值，包括返回文本列中的最小值。在用于文本数据时，如果数据按相应的列排序，则MIN()返回最前面的行。

• NULL值：MIN()函数忽略列值为NULL的行。

12.1.5 SUM()函数

• SUM()用来返回指定列值的和（总计）。

  SELECT SUM(列名) {,SUM(列名)} FROM table_name
  	[WHERE where_definition]

• 下面举一个例子，orderitems表包含订单中实际的物品，每个物品有相应的数量（quantity）。可如下检索所订购物品的总数（所有quantity值之和）：

  SELECT SUM(quantity) AS items_ordered FROM orderitems WHERE order_num = 20005;

  函数SUM(quantity)返回订单中所有物品数量之和，WHERE子句保证只统计某个物品订单中的物品。

• SUM()也可以用来合计计算值。在下面的例子中，合计每项物品的item_price*quantity，得出总的订单金额：

  SELECT SUM(item_price*quantity) AS total_price FROM orderitems WHERE order_num = 20005;

  函数SUM(item_price*quantity)返回订单中所有物品价钱之和，WHERE子句同样保证只统计某个物品订单中的物品。

• 在多个列上进行计算：如本例所示，利用标准的算术操作符，所有聚集函数都可用来执行多个列上的计算。

• NULL值：SUM()函数忽略列值为NULL的行。

12.2 聚集不同值

• 以上5个聚集函数都可以如下使用：

  • 对所有的行执行计算，指定ALL参数或不给参数（因为ALL是默认行为）；
  • 只包含不同的值，指定DISTINCT参数。

• ALL为默认：ALL参数不需要指定，因为它是默认行为。如果不指定DISTINCT，则假定为ALL。

• 下面的例子使用AVG()函数返回特定供应商提供的产品的平均价格。它与上面的SELECT语句相同，但使用了DISTINCT参数，因此平均值只考虑各个不同的价格：

  SELECT AVG(DISTINCT prod_price) AS avg_price FROM products WHERE vend_id = 1003;

  可以看到，在使用了DISTINCT后，此例子中的avg_price比较高，因为有多个物品具有相同的较低价格。排除它们提升了平均价格。

• 注意：如果指定列名，则DISTINCT只能用于COUNT()。DISTINCT不能用于COUNT(*)，因此不允许使用COUNT（DISTINCT），否则会产生错误。类似地，DISTINCT必须使用列名，不能用于计算或表达式。

12.3 组合聚集函数

• 目前为止的所有聚集函数例子都只涉及单个函数。但实际上SELECT语句可根据需要包含多个聚集函数。请看下面的例子：

  SELECT COUNT(*) AS num_items, MIN(prod_price) AS price_min, MAX(prod_price) AS price_max, AVG(prod_price) AS price_avg FROM products;

  这里用单条SELECT语句执行了4个聚集计算，返回4个值（products表中物品的数目，产品价格的最高、最低以及平均值）。

★第13章 分组数据GROUP BY

13.1 数据分组

• 目前为止的所有计算都是在表的所有数据或匹配特定的WHERE子句的数据上进行的。例如，下面的例子返回供应商1003提供的产品数目：SELECT COUNT(*) AS num_prods FROM products WHERE vend_id = 1003;

  但如果要返回每个供应商提供的产品数目怎么办？或者返回只提供单项产品的供应商所提供的产品，或返回提供10个以上产品的供应商怎么办？

  这就是分组显身手的时候了。分组允许把数据分为多个逻辑组，以便能对每个组进行聚集计算。

★13.2 创建分组

• 分组是在SELECT语句的GROUP BY子句中建立的。理解分组的最好办法是看一个例子：

  SELECT vend_id, COUNT(*) AS num_prods FROM products GROUP BY vend_id;

  上面的SELECT语句指定了两个列，vend_id包含产品供应商的ID，num_prods为计算字段（用COUNT(*)函数建立）。GROUP BY 子句指示 MySQL 按 vend_id 排序并分组数据。这导致对每个vend_id而不是整个表计算num_prods一次。从输出中可以看到，供应商1001有3个产品，供应商1002有2个产品，供应商1003有7个产品，而供应商1005有2个产品。

  因为使用了GROUP BY，就不必指定要计算和估值的每个组了。系统会自动完成。GROUP BY子句指示MySQL分组数据，然后对每个组而不是整个结果集进行聚集。

• 在具体使用GROUP BY子句前，需要知道一些重要的规定。

  • GROUP BY子句可以包含任意数目的列。这使得能对分组进行嵌套，为数据分组提供更细致的控制。
  • 如果在GROUP BY子句中嵌套了分组，数据将在最后规定的分组上进行汇总。换句话说，在建立分组时，指定的所有列都一起计算（所以不能从个别的列取回数据）。
  • GROUP BY子句中列出的每个列都必须是检索列或有效的表达式（但不能是聚集函数）。如果在SELECT中使用表达式，则必须在GROUP BY子句中指定相同的表达式。不能使用别名。
  • 除聚集计算语句外，SELECT语句中的每个列都必须在GROUP BY子句中给出。
  • 如果分组列中具有NULL值，则NULL将作为一个分组返回。如果列中有多行NULL值，它们将分为一组。
  • GROUP BY子句必须出现在WHERE子句之后，ORDER BY子句之前。

• 使用ROLLUP：使用WITH ROLLUP关键字，可以得到每个分组以及每个分组汇总级别（针对每个分组）的值，如下所示：

  SELECT vend_id, COUNT(*) AS num_prods FROM products GROUP BY vend_id WITH ROLLUP;

★13.3 过滤分组

• 除了能用GROUP BY分组数据外，MySQL还允许过滤分组，规定包括哪些分组，排除哪些分组。例如，可能想要列出至少有两个订单的所有顾客。为得出这种数据，必须基于完整的分组而不是个别的行进行过滤。

• 我们已经看到了WHERE子句的作用（第6章中引入）。但是，在这个例子中WHERE不能完成任务，因为WHERE过滤指定的是行而不是分组。事实上，WHERE没有分组的概念。那么，不使用WHERE使用什么呢？MySQL为此目的提供了另外的子句，那就是HAVING子句。HAVING非常类似于WHERE。事实上，目前为止所学过的所有类型的WHERE子句都可以用HAVING来替代。唯一的差别是★WHERE过滤行，而HAVING过滤分组。

• 那么，怎么过滤分组呢？请看以下的例子：

  SELECT cust_id, COUNT(*) AS orders FROM orders GROUP BY cust_id HAVING COUNT(*) >=2;

  这条SELECT语句的前3行类似于上面的语句。最后一行增加了HAVING子句，它过滤COUNT(*) >=2（两个以上的订单）的那些分组。正如所见，这里WHERE子句不起作用，因为过滤是基于分组聚集值 而不是特定行值的。

• ★HAVING和WHERE的差别：这里有另一种理解方法，WHERE在数据分组前进行过滤，HAVING在数据分组后进行过滤。这是一个重要的区别，WHERE排除的行不包括在分组中。这可能会改变计算值，从而影响HAVING子句中基于这些值过滤掉的分组。

• 那么，有没有在一条语句中同时使用WHERE和HAVING子句的需要呢？事实上，确实有。假如想进一步过滤上面的语句，使它返回过去12个月内具有两个以上订单的顾客。为达到这一点，可增加一条WHERE子句，过滤出过去12个月内下过的订单。然后再增加HAVING子句过滤出具有两个以上订单的分组。

  为更好地理解，请看下面的例子，它列出具有2个（含）以上、价格为10（含）以上的产品的供应商：

  SELECT vend_id, COUNT(*) AS num_prods FROM products WHERE prod_price >=10 GROUP BY vend_id HAVING COUNT(*) >=2;

  这条语句中，第一行是使用了聚集函数的基本SELECT，它与前面的例子很相像。WHERE子句过滤所有prod_price至少为10的行。然后按vend_id分组数据，HAVING子句过滤计数为2或2以上的分组。如果没有WHERE子句，将会多检索出两行（供应商1002，销售的所有产品价格都在10以下；供应商1001，销售3个产品，但只有一个产品的价格大于等于10）：

13.4 分组和排序

• 虽然GROUP BY和ORDER BY经常完成相同的工作，但它们是非常不同的。下表汇总了它们之间的差别。

  ORDER　BY	GROUP BY
  排序产生的输出	分组行。但输出可能不是分组的顺序
  任意列都可以使用（甚至非选择的列也可以使用）	只可能使用选择列或表达式列，而且必须使用每个选择列表达式
  不一定需要	如果与聚集函数一起使用列（或表达式），则必须使用

  表中列出的第一项差别极为重要。我们经常发现用GROUP BY分组的数据确实是以分组顺序输出的。但情况并不总是这样，它并不是SQL规范所要求的。此外，用户也可能会要求以不同于分组的顺序排序。仅因为你以某种方式分组数据（获得特定的分组聚集值），并不表示你需要以相同的方式排序输出。应该提供明确的ORDER BY子句，即使其效果等同于GROUP BY子句也是如此。

• 注意：不要忘记ORDER BY：一般在使用GROUP BY子句时，应该也给出ORDER BY子句。这是保证数据正确排序的唯一方法。千万不要仅依赖GROUP BY排序数据。

• 为说明GROUP BY和ORDER BY的使用方法，请看一个例子。下面的SELECT语句类似于前面那些例子。它检索总计订单价格大于等于50的订单的订单号和总计订单价格：

  SELECT order_num, SUM(quantity*item_price) AS ordertotal FROM orderitems GROUP BY order_num HAVING SUM(quantity*item_price) >=50;

  为按总计订单价格排序输出，需要添加ORDER BY子句，如下所示：

  SELECT order_num, SUM(quantity*item_price) AS ordertotal FROM orderitems GROUP BY order_num HAVING SUM(quantity*item_price) >=50 ORDER BY ordertotal;

  在这个例子中，GROUP BY子句用来按订单号（order_num列）分组数据，以便SUM(*)函数能够返回总计订单价格。HAVING子句过滤数据，使得只返回总计订单价格大于等于50的订单。最后，用ORDER BY子句排序输出。

★★★13.5 SELECT子句顺序

• 下面回顾一下SELECT语句中子句的顺序。下表是使用SELECT语句时必须遵循的次序，列出迄今为止所学过的子句。

子句	说明	是否必须使用
SELECT	要返回的列或表达式	是
FROM	从中检索数据的表	仅在从表选择数据时使用
WHERE	行级过滤	否
GROUP BY	分组说明	仅在按组计算聚集时使用
HAVING	组级过滤	否
ORDER BY	输出排序顺序	否
LIMIT	要检索的行数	否