MySQL基础——汇总数据汇总及分组数据

汇总数据

我们经常需要汇总数据而不用把它们实际检索出来，为此MySQL提供了专门的函数。

聚集函数

聚集函数 运行在行组上，计算和返回单个值的函数。
AVG()函数忽略列值为NULL的行，AVG()函数较好理解；
MAX()、MIN()、SUM()函数忽略值为NULL的行；
COUNT()函数确定表中行的数目或符合特定条件的行的数目。使用COUNT(*)对表中行的数目进行计数，不管表列中包含的是空值还是非空值；COUNT(列名)对特定列中具有值的行进行计数，忽略NULL值。

SELECT cust_email FROM customers;
+---------------------+
| cust_email          |
+---------------------+
| ylee@coyote.com     |
| NULL                |
| rabbit@wascally.com |
| sam@yosemite.com    |
| NULL                |
+---------------------+
mysql> SELECT COUNT(*) AS num_cust
    -> FROM customers;
+----------+
| num_cust |
+----------+
|        5 |
+----------+
mysql> SELECT COUNT(cust_email) AS num_cust
    -> FROM customers;
+----------+
| num_cust |
+----------+
|        3 |
+----------+

聚集不同的值

聚集函数对所有的行执行计算，指定ALL参数或不给参数（因为ALL是默认的）；只包含不同的值，指定DISTINCT参数，也必须使用列名，即不能COUNT(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 |
+-----------+-----------+-----------+-----------+

分组数据

创建分组

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

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

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

# 输出供应商1002-1005间、同一id、价格在不同区间（以10为单位）的产品数量，并汇总每个分组。
mysql> SELECT vend_id,
    -> prod_price-Mod(prod_price,10) AS price,
    -> COUNT(*) AS num_prods
    -> FROM products
    -> WHERE vend_id BETWEEN 1002 AND 1005
    -> GROUP BY vend_id,prod_price-MOD(prod_price,10) WITH ROLLUP
    -> ORDER BY vend_id;
+---------+-------+-----------+
| vend_id | price | num_prods |
+---------+-------+-----------+
|    NULL |  NULL |        11 |
|    1002 |  0.00 |         2 |
|    1002 |  NULL |         2 |
|    1003 |  0.00 |         3 |
|    1003 | 10.00 |         3 |
|    1003 | 50.00 |         1 |
|    1003 |  NULL |         7 |
|    1005 | 30.00 |         1 |
|    1005 | 50.00 |         1 |
|    1005 |  NULL |         2 |
+---------+-------+-----------+

过滤分组

HAVING 支持所有WHERE操作符，句法和WHERE相同，只是关键字有差别，WHERE过滤行，HAVING过滤分组。WHERE在数据分组前过滤，HAVING在数据分组后过滤。

# 输出具有2个以上、价格为10以上的产品供应商id
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 |
+---------+-----------+

分组和排序