MySQL汇总数据和分组数据
2018-07-03 本文已影响86人
禅与发现的乐趣
汇总数据
聚集函数
我们经常需要汇总数据而不用把它们实际检索出来，比如：
-
确定表中行数
-
获得表中行组的和
-
找出表列的最大值、最小值和平均值
返回实际表数据是对时间和处理资源的一种浪费（带宽啊），我们只要汇总信息，为此，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 |
+-----------+------------+
