文章目录
第12章 汇总数据
1. 聚集函数
我们经常需要汇总数据而不用把它们实际检索出来。这种类型的检索例子有以下几种。
- 确定表中行数(或者满足某个条件或包含某个特定值的行数)
- 获得表中行组的和
- 找出表列(或所有行或某些特定的行)的最大值、最小值和平均值
聚集函数: 运行在行组上,计算和返回单个值的函数。
常见的聚集函数如下:
1.1 AVG()函数
AVG()可用来返回所有列的平均值,也可以用来返回特定列或行的平均值。
SELECT AVG(prod_price) AS avg_price
FROM products;
SELECT AVG(prod_price) AS avg_price
FROM products
WHERE vend_id = 1003;
- AVG()只能用来确定特定数值列的平均值,而且列名必须作为函数参数给出。
- AVG()函数忽略列值为NULL的行。
1.2 COUNT()函数
COUNT()函数可用来确定表中行的数目或符合特定条件的行的数目。COUNT()函数有两种使用方式。
- 使用COUNT(*)对表中行的数目进行计数,包含列值为NULL的行。
- 使用COUNT(column)对特定列中具有值的行进行计算,忽略NULL值。
SELECT COUNT(*) AS num_cust
FROM customers;
1.3 MAX()函数
MAX()返回指定列中的最大值,要求指定列名。MAX()函数忽略列值为NULL的行。
对非数值数据使用MAX()时,如果数据按相应的列排序,则MAX()返回最后一行。
1.4 MIN()函数
MIN()的功能和MAX()功能相反。
1.5 SUM()函数
SUM()用来返回指定列值的和(总计)。此外,SUM()也可以用来合计计算值。
SELECT SUM(item_price*quantity) AS total_price
FROM orderitems
WHERE order_num = 20005;
SUM()函数忽略列值为NULL的行
2. 聚集不同值
以上5个聚集函数都可以如下使用:
- 对所有行执行计算,指定ALL参数或不给参数(因为ALL时默认行为)
- 只包含不同的值,指定DISTINCT参数
SELECT AVG(DISTINCT prod_price) AS avg_price
FROM products
WHERE vend_id = 1003;
注意 ,DISTINCT必须使用列名,不能用于计算或表达式。
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;
第13章 分组数据
1. 数据分组
有时我们需要把数据分为多个逻辑组,以便能对每个组进行聚集计算。分组是在SELECT语句的GROUP BY子句中建立的。下面是一个例子:
SELECT vend_id, COUNT(*) AS num_prods
FROM products
GROUP BY vend_id;
上面的GROUP BY子句指示MySQL按vend_id排序并分组数据,这导致对每个vend_id而不是整个表计算num_prods。
下面是使用GROUP BY子句的一些重要规定:
- GROUP BY子句可以包含任意数目的列,这使得能对分组进行嵌套
- 如果在GROUP BY子句中嵌套了分组,数据将在最后规定的分组上进行汇总
- GROUP BY子句中列出的每个列都必须是检索列或有效的表达式(但不能是聚集函数)
- 除聚集计算语句外,SELECT语句中的每个列都必须在GROUP BY子句中给出
- 如果分组列中具有NULL值,则NULL将作为一个分组返回
- GROUP BY子句必须出现在WHERE子句之后,ORDER BY子句之前
2. 过滤分组
MySQL允许过滤分组,规定包括哪些分组,排除哪些分组。过滤分组需要使用HAVING子句(不能使用WHERE,因为WHERE子句过滤的是行,而不是分组)。
HAVING支持所有WHERE操作符。
下面是一个过滤分组的例子,它过滤两个以上订单的分组。
SELECT cust_id, COUNT(*) AS orders
FROM orders
GROUP BY cust_id
HAVING COUNT(*) >= 2;
HAVING与WHERE的差别: 这里有一种理解方法,WHERE在数据分组前进行过滤,HAVING在数据分组后进行过滤。
也可以同时使用WHERE子句和HAVING子句。
SELECT vend_id, COUNT(*) AS num_prods
FROM products
WHERE prod_price >= 10
GROUP BY vend_id
HAVING COUNT(*) >= 2;
3. 分组和排序
GROUP BY语句的输出并不一定是分组的顺序,所以,如果我们想按照某种顺序排列输出,一定要使用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;