MySQL实践-汇总数据与聚集函数

说明

有时候我们需要汇总数据信息而不是将数据本身的内容检索出来，比如统计某张表中的行数（不关心具体内容），MySQL提供了5个聚集函数来进行数据汇总操作：
1. AVG()：返回某列的平均值
2. COUNT()：返回某列的行数
3. MAX()：返回某列的最大值
4. MIN()：返回某列的最小值
5. SUM()：返回某列之和

操作

1. AVG()

mysql> SELECT AVG(prod_price) AS avg_price FROM products;
+-----------+
| avg_price |
+-----------+
| 16.133571 |
+-----------+
1 row in set (0.00 sec)

可以指定求特定行的平均值

mysql> SELECT AVG(prod_price) AS abg_price FROM products WHERE vend_id = 1003;
+-----------+
| abg_price |
+-----------+
| 13.212857 |
+-----------+
1 row in set (0.01 sec)

1. COUNT()

使用COUNT(*)对表中行的数目进行计数，不管表中包含的是否是空值(NULL)

mysql> SELECT COUNT(*) AS num_cust FROM customers;
+----------+
| num_cust |
+----------+
|        5 |
+----------+
1 row in set (0.00 sec)

使用COUNT(column)对指定的列进行计数，忽略值为NULL的项

mysql> SELECT COUNT(*) AS num_cust FROM customers;
+----------+
| num_cust |
+----------+
|        5 |
+----------+
1 row in set (0.00 sec)

1. MAX()

mysql> SELECT COUNT(*) AS num_cust FROM customers;
+----------+
| num_cust |
+----------+
|        5 |
+----------+
1 row in set (0.00 sec)

1. MIN()

mysql> SELECT MIN(prod_price) AS min_price FROM products;
+-----------+
| min_price |
+-----------+
|      2.50 |
+-----------+
1 row in set (0.00 sec)

1. SUM

SUM()用来返回指定列值得和，也可以迎来合计计算值，执行时会忽略值为NULL的行，例如orderitems表中包含了订单汇总的实际物品，表达内容如下：

mysql> SELECT * FROM orderitems;
+-----------+------------+---------+----------+------------+
| order_num | order_item | prod_id | quantity | item_price |
+-----------+------------+---------+----------+------------+
|     20005 |          1 | ANV01   |       10 |       5.99 |
|     20005 |          2 | ANV02   |        3 |       9.99 |
|     20005 |          3 | TNT2    |        5 |      10.00 |
|     20005 |          4 | FB      |        1 |      10.00 |
|     20006 |          1 | JP2000  |        1 |      55.00 |
|     20007 |          1 | TNT2    |      100 |      10.00 |
|     20008 |          1 | FC      |       50 |       2.50 |
|     20009 |          1 | FB      |        1 |      10.00 |
|     20009 |          2 | OL1     |        1 |       8.99 |
|     20009 |          3 | SLING   |        1 |       4.49 |
|     20009 |          4 | ANV03   |        1 |      14.99 |
+-----------+------------+---------+----------+------------+
11 rows in set (0.00 sec)

检索指定订单所订购的物品总数

mysql> SELECT SUM(quantity) AS items_ordered FROM orderitems WHERE order_num = 20005;
+---------------+
| items_ordered |
+---------------+
|            19 |
+---------------+
1 row in set (0.00 sec)

计算指定订单每项物品的单价乘以订购数量，得出总的订单金额

mysql> SELECT SUM(quantity) AS items_ordered FROM orderitems WHERE order_num = 20005;
+---------------+
| items_ordered |
+---------------+
|            19 |
+---------------+
1 row in set (0.00 sec)

聚集不同值

以上5个聚集函数默认都是对所有行执行计算，默认参数为ALL,也可以只包含不同的值，需要指定参数DISTINCT,如返回特定供应商提供的产品的平均价格，正确做应该是每个价钱不同的商品只取一件来进行计算，只考虑不同的价格

mysql> SELECT AVG(DISTINCT prod_price) AS avg_price FROM products WHERE vend_id = 1003;
+-----------+
| avg_price |
+-----------+
| 15.998000 |
+-----------+
1 row in set (0.00 sec)

可以看到求出的平均价格比上面求得的要高，因为之前的计算中有多个价格较低的相同物品一起被计算。
注意 使用DISTINCT参数时必须指定列名，不能用于子计算或表达式，同时对MAX()和MIN()使用DISTINCT没有意义，最大值最小值计算不受重复值影响。
一条语句中也可以包含多个聚集函数

mysql> 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;
+-----------+-----------+-----------+-----------+
| num_items | price_min | price_max | price_avg |
+-----------+-----------+-----------+-----------+
|        14 |      2.50 |     55.00 | 16.133571 |
+-----------+-----------+-----------+-----------+
1 row in set (0.00 sec)