MySQL汇总数据和分组数据

汇总数据

聚集函数

我们经常需要汇总数据而不用把它们实际检索出来，比如：

确定表中行数
获得表中行组的和
找出表列的最大值、最小值和平均值

返回实际表数据是对时间和处理资源的一种浪费（带宽啊），我们只要汇总信息，为此，MySQL 提供了5个聚集函数。

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

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

mysql> SELECT AVG(prod_price) AS avg_price FROM products;
+-----------+
| avg_price |
+-----------+
| 16.133571 |
+-----------+

mysql> SELECT AVG(prod_price) AS avg_price FROM products WHERE vend_id = 1003;
+-----------+
| avg_price |
+-----------+
| 13.212857 |
+-----------+

AVG 函数只能用来确定特定数值列的平均值。

COUNT(*)对所有行计数：

mysql> SELECT COUNT(*) AS num_cust FROM customers;
+----------+
| num_cust |
+----------+
|        5 |
+----------+

mysql> SELECT COUNT(cust_email) AS num_cust FROM customers;
+----------+
| num_cust |
+----------+
|        3 |
+----------+

mysql> SELECT MAX(prod_price) AS max_price FROM products;
+-----------+
| max_price |
+-----------+
|     55.00 |
+-----------+

mysql> SELECT MAX(prod_price) AS min_price FROM products;
+-----------+
| min_price |
+-----------+
|     55.00 |
+-----------+

mysql> SELECT SUM(quantity) AS items_ordered FROM orderitems WHERE order_num = 20005;
+---------------+
| items_ordered |
+---------------+
|            19 |
+---------------+

mysql> SELECT SUM(item_price*quantity) AS total_price FROM orderitems WHERE order_num = 20005;
+-------------+
| total_price |
+-------------+
|      149.87 |
+-------------+

聚集不同值

上面五个聚集函数都可以如下使用：

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

mysql> SELECT AVG(DISTINCT prod_price) AS avg_price FROM products WHERE vend_id = 1003;
+-----------+
| avg_price |
+-----------+
| 15.998000 |
+-----------+

DISTINCT 必须使用列名。

组合和聚集函数

SELECT 语句可以根据需要包含多个聚集函数：

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 |
+-----------+-----------+-----------+-----------+

分组数据

分组允许把数据分为多个逻辑组，以便能对每个组进行聚集计算。

创建分组

mysql> SELECT vend_id, COUNT(*) AS num_prods FROM products GROUP BY vend_id;
+---------+-----------+
| vend_id | num_prods |
+---------+-----------+
|    1001 |         3 |
|    1002 |         2 |
|    1003 |         7 |
|    1005 |         2 |
+---------+-----------+

GROUP BY 子句指示 MySQL 分组数据，然后对每个组而不是整个结果集进行聚集：

GROUP BY 子句可以包含任意数目的列，这使得能对分组进行嵌套，为数据分组提供更细致的控制。
如果分组列中具有 NULL 值，则 NULL 将作为一个分组返回，多行 NULL 值，它们将分为一组。
GROUP BY 子句必须出现在 WHERE 子句之后，ORDER BY 子句之前。

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

mysql> SELECT vend_id, COUNT(*) AS num_prods FROM products GROUP BY vend_id WITH ROLLUP;
+---------+-----------+
| vend_id | num_prods |
+---------+-----------+
|    1001 |         3 |
|    1002 |         2 |
|    1003 |         7 |
|    1005 |         2 |
|    NULL |        14 |
+---------+-----------+

过滤分组

WHERE 过滤行，HAVING 过滤分组，HAVING 支持所有 WHERE 操作符。

mysql> SELECT cust_id, COUNT(*) AS orders FROM orders GROUP BY cust_id HAVING COUNT(*) >= 2;
+---------+--------+
| cust_id | orders |
+---------+--------+
|   10001 |      2 |

mysql> SELECT cust_id, COUNT(*) AS orders FROM orders GROUP BY cust_id;
+---------+--------+
| cust_id | orders |
+---------+--------+
|   10001 |      2 |
|   10003 |      1 |
|   10004 |      1 |
|   10005 |      1 |
+---------+--------+

HAVING 分组后，对分组进行过滤。

HAVING 和 WHERE 的差别：

WHERE 在数据分组前进行过滤，HAVING 在数据分组后进行过滤。

mysql> SELECT vend_id, COUNT(*) AS num_prods FROM products WHERE prod_price >= 10 GROUP BY vend_id HAVING COUNT(*) >= 2;
+---------+-----------+
| vend_id | num_prods |
+---------+-----------+
|    1003 |         4 |
|    1005 |         2 |
+---------+-----------+

ORDER BY 和 GROUP BY 同时使用：

mysql> SELECT order_num, SUM(quantity*item_price) AS ordertotal FROM orderitems GROUP BY order_num HAVING SUM(quantity*item_price) >= 50 ORDER BY ordertotal;
+-----------+------------+
| order_num | ordertotal |
+-----------+------------+
|     20006 |      55.00 |
|     20008 |     125.00 |
|     20005 |     149.87 |
|     20007 |    1000.00 |
+-----------+------------+